In this post I would like to publish how I did an MSSQL migration.
In my project I had to migrate an MSSQL 2008 what run on a Windows Server 2008 SP2 (server 1) to another MSSQL 2008 what run on a Windows Server 2008(x46) R2 (server 2).
Details of server 1:
Operating system: Windows Server 2008(x64) SP2 Standard [Version 6.0.6002]
System memory: 1024MB
Disk space: 36000MB (Only C:\ drive)
MS SQL version: SQL Server 10.0.5500
Name of SQL instance: MSSQL
IP address: 192.168.10.10 (before migration it has to be changed)
Hostname: MsSqlServer (before migration it has to be changed)
Details of server 2:
Operating system: Windows Server 2008(x64) R2 SP1 Enterprise [Version 6.1.7601]
System memory: 2048MB
Disk space: 40960MB (C:\ drive) and 40960MB (D:\ drive)
Required disk space:
– System drive (C:\): 815MB required
– Shared Install Directory: 411MB
– Instance Directory: 528MB
MS SQL version: SQL Server 10.0.1600
Name of SQL instance: MSSQL
IP address: 192.168.10.10 (before assigning check if available)
Hostname: MsSqlServer (before assigning check if available)
0.) Step Zero. This is necessary because you need to prepare the system to the migration. Start with the running system.
Start SQL Server Management Studio and go to the Security leaf.
Firstly the services of the instances have to be stopped. For this you should open the server manager and navigate to the services. Here you can find the running SQL services. Stop them.
After this modify the IP address of the machine something different but be sure that do not be occupied. For example: 192.168.10.9. Put the computer out from the domain and restart it. After restart modify the name of the host to something different. For example: OldMsSqlServer and restart it again. If this is possible delete the computer object from the Active Directory.
Continue the work with the receiver computer. Modify its name to MsSqlServer and restart it. After restart log the computer in the domain and restart it. Log in with a domain user (who has admin privilege) and assign new IP address to the machine. This is a specific address since it had the IP address of the another computer. This is: 192.168.10.10
At this point we exchanged the computers. The new computer has all of the settings what the old computer had. However the old computer has a completly new setting.
1.) Copy or insert the install media to the server 2. MS SQL 2008 will be installed to the computer. The following components will be installed:
– database engine services
– full-text search
– client tools connectivity
– mngmt tools basic – complete
2.) Configure the instance
– If you need different instance as the default enter here as you can see on the attached picture.
3.) Configure the server: Service Accounts
SQL Server Agent – SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server.
SQL Server Database Engine – The Database Engine is the core service for storing, processing, and securing data.
I have a user account in the domain and its name: contoso\_svc_sql (be careful: use the domainname\username form)
4.) Configure the Database Engine
This is the “last” step. After this you should press next, next, finish. You will see the installation progress bar and if everything is all right (green checks everywhere) the MS SQL 2008 will be installed on to your computer.
Press “Close” button to finish the installation.
5.) In the start menu, find the SQL SMS and start it.
Server type: Database Engine
Server name: <hostname>\<instancename>
Authentication: Windows Authentication
User name: <it is filled out with the person who started the Studio> (it is inactive)
6.) In the start menu, find the SQL Server Configuration Manager (SQL SMS) and start it.
Click on the SQL Server Network Configuration\Protocols for MSSQL in the left pane. In the right pane you will find the related settings. Right click on the TCP/IP and choose the IP Addresses tab on the conversation panel.
Check here please that your settings are the same with the old MSSQL settings. It means if you used the 12222 TCP port to the communication with the database you should set this specific port here. Supposing you want to reach your databases on the same port.
Relevant Firewall settings for MSSQL:
TCP protocol on 1433 (default MSSQL listening port, change this if you are using a different)
UDP protocol on 1434
These settings are necessary if you want to connect to the Database or the Configuration Manager remotly.
At here Enable the Named Pipes. (Both on Windows and POSIX systems, named-pipes provide a way for inter-process communication to occur among processes running on the same machine. What named pipes give you is a way to send your data without having the performance penalty of involving the network stack.)
7.) Copy the databases and the logfiles from the OldMsSqlServer (old machine) to the MsSqlServer (new machine). As you can see among the details of server2 there is D:\ drive. This is because the databases and logfiles will be copied to here instead of location C:\ . Practically it is better solution to keep your databases (and database related) files separately from your system drive from instance on an attached drive. Create a folder on your D:\ drive. The name of this folder could be: “DB”. Copy the mdf and ldf folders into this one. This is important because this will be your further SQL Database location and of course you have to set up your SQL Application to know about it.
8.) Open the SQL SMS again. Log in. Find your current instance (currently MSSQL) // you should see something like this: <hostname>\<instancename>(SQL Server 10.0.1600 – <domainname>\<username>)
On this item click with right button for the properties option and choose the Database Settings. On this panel at bottom-right you can find the option for the location. Browse the D:\DB folder out. It will be your further database source. You should not enter the mdf and ldf folders because the engine will look the files after there.
9.) Lower in this structure open the Security and open the Logins leaf too. At this point you should log in to the old machine. Open the SQL SMS, log in and check the same leaf in that environment. Add that elements to your current instance.
10.) At last attach your migrated databases. Right click on the Databases leaf and choose the Attach… option.
After you click on the Add… button, browse your .mdf file in. Under the ‘Databases to attach’ section you can find the ‘Database details’ and here you will face that your log file is not found. Click on it and browse your proper .ldf file in. Your files will be found on drive D:\DB\mdf and D:\DB\ldf (of course if you copied them to here as I suggested above)