Make IT Complete

Technical Ramblings From an IT Professional

Rename SQL Server

During the migration of SQL databases to a new SQL Server we find that we must rename the SQL Server to the original server name.  Renaming a machine on the network is easy eough but we must also run a T-SQL script to rename the server in MS-SQL

To correct @@SERVERNAME for a default instance use the following commands:

exec sp_dropserver old_name
GO
exec sp_addserver new_name, ‘local’
GO

To correct @@SERVERNAME for a named instance:

exec sp_dropserver old_nameinstancename
GO
exec sp_addserver new_nameinstancename, ‘local’
GO

Makeitcomplete.com has completed the development on misscyfairhouston.com

In a little more than a couple days we have helped the Cy-Fair Chamber of Commerce launch a web site for the Miss Cy-Fair Houston Pageant.  We developed this site using Drupal.  With some graphics work form Logo Origins to help match the printed materials and ads already submitted to press we teamed for excellent results.

Visit the site: www.misscyfairhouston.com

Check out Logo Origins at Logo Origins

Windows Uptime

Ever wonder how long your windows system has been online?  Found that in Windows you can get to this information by running

net stats srv

Applied an update tonight for mod_security

We have updated the mod_security on our site this weekend. Please let us know if you experience any difficulties with your sites.

Small Office Virtualization

This blog entry is starting the journey of converting a small business office in to a virtualized office that will provide the same functionality virtaully as the office has today in the brick and mortar.

Background

A client with a small office has decided to move from their current office that is much too large and with an adversarial land lord.  They have 4 employees and the owner is remote and located in God’s Country without high speed Internet.

The office generates a huge amound of paper but most of the work is completed on computers and they leverage 3rd party databases over the internet.

They have an Windows Domain running active directory on a single (very old) server.  All of their databases are in Access.  They do own a copy of SQL server but it has never been installed.

They currently get access to the Internet and dialtone from CBEYOND.  CBEYOND has been a very good provider for all of these services.

The office will physically no longer exist.

The Plan

We will remotely locate all of the users to their own home offices.  We will use a VoIP phone service from a company called Ring Central.  When we get into to the detail on this there will be another blog post.  Ring Central will provide phone service to the homes and their virtual PBX will have autoattendant, call routing, voice mail and the like.

We will move the server to a small data center that is willing to take the server in and provide some bandwidth.

Problems

We are planning on connecting the remote PCs to the server using a VPN connection.  During initial testing the VPN connection provided suitable access to the file system and to other network services but when the application are access the performance is unaccepatable.  This is due to the nature of the application of couse.  Remote access to M$ Access over a VPN is never going to be a good thing.

During the first test I was able to connect to the VPN server with no problem.  When I started the application it took over a minute for the first window of the applciation to open.  This is clearly because of the database access that was happening.  When the app was then available it could take as long as a minute to move from record to record.

Clearly this is not going to work.  There is not enough time in the project to attempt to rewrite the software or to even dive into how much data is actually being moved.

[Read the rest of this entry...]

Slow Large File Copy Issues

From time to time, customers will call in to report “performance problems” that they are having when copying large files from one location to another.  By “performance problems”, they mean that the file isn’t copying as fast as they expect.  The most common scenario is copying large SQL databases from server to server, but this could just as easily occur with other file types.  More often than not, the customer has tried different methods of copying the file including Windows Explorer, Copy, XCopy & Robocopy - with the same results.  So … what’s going on here?

Assuming that you aren’t experiencing network issues (and for the purposes of this article, we’ll assume a healthy network), the problem lies in the way in which the copy is performed - specifically Buffered v Unbuffered Input/Output (I/O).  So let’s quickly define these terms.  Buffered I/O describes the process by which the file system will buffer reads and writes to and from the disk in the file system cache.  Buffered I/O is intended to speed up future reads and writes to the same file but it has an associated overhead cost.  It is effective for speeding up access to files that may change periodically or get accessed frequently.  There are two buffered I/O functions commonly used in Windows Applications such as Explorer, Copy, Robocopy or XCopy:

  • CopyFile() - Copies an existing file to a new file
  • CopyFileEx() - This also copies an existing file to a new file, but it can also call a specified callback function each time a portion of the copy operation is completed, thus notifying the application of its progress via the callback function.  Additionally, CopyFileEx can be canceled during the copy operation.

So looking at the definition of buffered I/O above, we can see where the perceived performance problems lie - in the file system cache overhead.  Unbuffered I/O (or a raw file copy) is preferred when attempting to copy a large file from one location to another when we do not intend to access the source file after the copy is complete.  This will avoid the file system cache overhead and prevent the file system cache from being effectively flushed by the large file data.  Many applications accomplish this by calling CreateFile() to create an empty destination file, then using the ReadFile() and WriteFile() functions to transfer the data.

  • CreateFile() - The CreateFile function creates or opens a file, file stream, directory, physical disk, volume, console buffer, tape drive, communications resource, mailslot, or named pipe. The function returns a handle that can be used to access an object.
  • ReadFile() - The ReadFile function reads data from a file, and starts at the position that the file pointer indicates. You can use this function for both synchronous and asynchronous operations.
  • WriteFile() - The WriteFile function writes data to a file at the position specified by the file pointer. This function is designed for both synchronous and asynchronous operation.

For copying files around the network that are very large, my copy utility of choice is ESEUTIL which is one of the database utilities provided with Exchange.  To get ESEUTIL working on a non-Exchange server, you just need to copy the ESEUTIL.EXE and ESE.DLL from your Exchange server to a folder on your client machine.  It’s that easy.  There are x86 & x64 versions of ESEUTIL, so make sure you use the right version for your operating system.  The syntax for ESEUTIL is very simple: eseutil /y <srcfile> /d <destfile>.  Of course, since we’re using command line syntax - we can use ESEUTIL in batch files or scripts.  ESEUTIL is dependent on the Visual C++ Runtime Library which is available as a redistributable package.

Advanced User

The advanced user module allows the filtering of users based on the user.module fields and optionally the profile.module fields. The fields available for filtering can be configured using the module settings. Eg. Site admin may search through 1000s of users to display all users who have not accessed their account.

Once the group of users with selected common denominators are filtered, the module can be used to perform functions that email, block, unblock, add or remove roles, or delete. Another nice feature is the ability to notify administrators or other roles, by email, of user account creation or profile changes.

http://drupal.org/project/advuser

Backing up Plesk Servers

I found this article to be helpful.

http://kau-boys.ramarka.de/blog/webserver/plesk-backup-manager-auf-1und1-linux-root-server-einrichten?lang=en

Really Helpful Post about Connection Strings

http://www.connectionstrings.com/sql-server-2005

Auto Delete Content In Drupal

I have a client that wants to have content posted on his site automatically deleted when it reaches 30 days.  His site is built using Drupal and using the hook_cron() will let the scheduled cron job process the node deletions.

I created a Drupal module and in the .module file added function profeng_cron() as the hook and it calls deleteagednodes with the type of content to be removed.  The function deleteagednodes does all of the work.  I created a query to return all of the nodes that have the desired content types and then using a where clause to filter on the created date.  In this example I am setting $expired_time to be now minus 30 days and in the query I am getting records that were created before the $expired_time.

I then take the results from the query and loop through all results.  Because the cron job runs as the anonymous user I select the user that owns the node and set the current user to the owner of the node.  I then call the Drupal node_delete.  When calling node_delete all of the heavy lifting of the delete like writing to the logs and sending notifications are executed.

//cron hook

function profeng_cron()

{

deleteagednodes(’inspectionreport’);

}

//function to delete content by tyoe that is over 30 days old

function deleteagednodes($contenttype)

{

$expired_time = strtotime(’-30 days’);

$query = db_query(”SELECT nid, uid FROM {node} AS n WHERE type = ‘%s’ and created <= ‘%s’”, $contenttype, $expired_time);

while($del = db_fetch_object($query))

{

global $user;

$owner_account = user_load(array(’uid’ => $del->uid));

$temp_user = $user;

$user = $owner_account;

$this_edit['nid'] = $del->nid;

node_delete($this_edit);

$user = $temp_user;

}

}