SQL Server from Scratch

Suppose, that, you have a brand new server with higher specification from the old one. Your boss instruct you to, literally, MOVE the SQL Server from the old server to new one with the least possible time, i.e. the minimum downtime.

By the meaning of moving to the new server is that it includes all the latest patches, and configuration you have done, maybe in the span of many years, instantly into new server.

The conventional method is to run the MS SQL Server setup in the new server and perform the patch update and re-enter the configuration and other tedious jobs, that will lenghten the downtime of your server.

Now, imagine, that we can MOVE literally the SQL Server system from the old one to new one without any re-configuration and patch update. Would it more exciting than the boring job to re-configure and re-patch ?

There are at least two reasons to do this, that is to minimalize downtime, which is surely pleased your boss. The other more important, is that, in the process of re-configuration, we tend to miss/forget some small but yet crucial item, so that new MS SQL server do not behave as expected. Sometime, because of this, we have to ‘re-invent the wheel’, and start all over again all the years in perfecting the SQL Server in the old machine.

The challenge is, is this possible ? Well, you can find the answer in the end of this article 🙂

Now, it’s time to roll up your sleeves, and get to work.

I have a sample running MS SQL Server 2000 in one machine, using usual setup and configuration process, and I have other machine, for the target.

First, I copied all the executables and required DLLs and other files from the old machine to target machine in D:\SQL2000. After all is set, using the command line, I activated the sqlservr.exe :

After performing some examination, I found that, this is caused by sqlservr.exe do not find this key in the registry :

HKLM\SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer\CurrentVersion

Let’s add this key to the registry in the new machine and see what happen :

It is caused by the value of “CurrentVersion” parameter is not initialized.

Let’s initialized it using the registry value from old machine :

Re-run the program :

This is because the call below is failed :

004e6ad6 ff55f8 call dword ptr [ebp-8] ss:0023:000bfc88={SQLBOOT!QueryValue2 (422a2100)}

This routine, through QueryValueWorker checks for the value in :

HKLM\SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer
\CurrentVersion\checksum

Let’s copy this registry value to new machine, and re-run sqlservr.exe :

We have one more step closer to make it up and running. This is caused by registry information :

HKLM\SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer
\CurrentVersion\Parameters

is not set. I copied from the original registry and updates it to :

The file master.mdf and mastlog.ldf is copied from original server to the above destination. Then, re-run the sqlservr.exe :

2009-01-22 16:16:25.76 spid5 udopen: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf.

This is because in the old server, the setup data information in master.mdf is set to this location. So, in new server, I create the necessary directory (C:\Program Files\Microsoft SQL Server\MSSQL\data).

Later on, after the SQL server is up, I can change the location data in master.mdf to desired location (D:\SQL2000\Data).

So, currently copied all the required files to old directory in new machine, re-run again and voila ! The server is now active :

2009-01-22 16:56:59.30 server SQL server listening on Shared Memory.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: