April 4, 2012 at 4:33 pm
Hi all,
Was wondering if someone could provide me with a link or documentation or etc ... on the proper steps to handling the migration of an entire SQL Server instance to a new machine.
In my mind, there's four steps:
Step 1: Take each of the old databases offline (including system databases?)
Step 2: Take a backup of each database (including system databases?)
Step 3: Copy the database backups to the new machine
Step 4: Restore each of the database backups to the new machine (including the system databases?)
Step 5: Run a script to restore the User - Login connection for each user in each database (is this actually necessary?)
Step 6: Anything special regarding jobs or linked servers?
So basically needing a bit more info regarding the system databases. Do I take a backup of the system DBs and then restore those backups first on the new machine, and then restore the rest? If I do this, does this avoid the problem of having the connection between the individual users in each database and the login on the server being broken?
April 4, 2012 at 4:43 pm
unless you have got a LOT of jobs, SSIS, linked servers and other objects whose definitions are held in the system databases it is best not to backup and restore the system databases, especially with SQL2005. Script out the objects you want and run them into the new server.
User databases either backup\restore or detach\attach. Backup\restore usually best because the files to copy are smaller and backout is easier if problems with the migration.
If your server name is changing you will need to repoint your app connection strings to the new server.
---------------------------------------------------------------------
April 5, 2012 at 9:33 am
Sounds good - finished all of that, took about 30-60 minutes, not too bad.
One thing I'm not sure of though - since I didn't restore the system databases, does that mean that the sp_configure settings are not set on the new server? If so, is there an easy way to script that out of the old server to restore to the new one, or do I just go through each option manually? Same deal for any surface area configuration settings.
April 5, 2012 at 9:58 am
They won't be set.
I don't have a script to reverse engineer them as I don't change that many of them (you should not need to), I use a script if I amend them and they are easily listable using sp_configure or by selecting out of master.sys.sysconfigures.
It's probably just as quick as a one off change to do manually by comparing.
I'll bet there are scripts using dynamic SQL to script them out on this site.
---------------------------------------------------------------------
April 5, 2012 at 10:01 am
They won't be set.
I don't have a script to reverse engineer them as I don't change that many of them (you should not need to), I use a script if I amend them and they are easily listable using sp_configure or by selecting out of master.sys.sysconfigures.
It's probably just as quick as a one off change to do manually by comparing.
I'll bet there are scripts using dynamic SQL to script them out on this site.
---------------------------------------------------------------------
April 5, 2012 at 4:20 pm
BTW just check you don't have any user defined error messages. Check the sysmessages tables for error nos above 50000
set quoted_identifier off
select 'exec sp_addmessage ' +convert(varchar(10),error) +"," +convert(char(2),severity) +"," +"'"+description +"'" from master..sysmessages where error > 50000
---------------------------------------------------------------------
April 7, 2012 at 4:12 am
george sibbald (4/4/2012)
User databases either backup\restore or detach\attach. Backup\restore usually best because the files to copy are smaller and backout is easier if problems with the migration.
The backups generally won't be any smaller, so you have the time it takes to back up and restore plus the tim to copy the backup files across the network.
For larger databases I have found detach, copy, attach much faster as the detach\attach operations are quick. Of course all of these options rely on sufficient network bandwidth.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 6:28 am
Hey Hi,
explain the minimum required for the migration of Microsoft SQL Server 2000 to Microsoft SQL Server 2008. You can use the same principles to migrate a Microsoft SQL Server 2005 to Microsoft SQL Server 2008.
1. SQL server 2008 upgrade advisor
1.1. Before migrating
Microsoft provides a tool called "Microsoft SQL Server 2008 Upgrade Advisor" to alert you of any changes in design between Microsoft SQL Server 2000/2005 and SQL Server 2008. It is strongly recommended to run this software before migration.
1.2. Download
You can download this tool from the link below:
“Download the Microsoft SQL Server 2008 Upgrade Advisor. Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 to help you prepare for upgrades to SQL Server 2008.”
After installation, a new tab appears in: Start>> All Programs >> Microsoft SQL server 2008 >> SQL Server 2008 Upgrade Advisor
1.3. Report before migration
Run “SQL Server 2008 Upgrade Advisor”.
Then click on “Launch Upgrade Advisor Analysis Wizard”.
Then click on "Detect". The tool will automatically select the components installed on your platform.
It is also interesting to give a trace profiler tool containing a representative of your business so that it detects all the elements that would longer supported or recommended in Microsoft SQL Server 2008.
Then configure the connection to your SQL server 2000 instance. After a few minutes a report will be generated with warning or points on which you must bring your attention. These items may include Full Text Search, replication, objects that no longer exist or have been modified in the new version, plans to maintain ...
The tool will provide two other types of information:
1. Objects affected
2. Advice you can find a workaround or fix the problem.
Sample report provided by the tool:
3. Migration with the database restore method
3.1. Restoring a database SQL server 2000
On your new instance Microsoft SQL Server 2008, connect to Management Studio 2008. Then click on the "Restore Database". Then follow the instructions.
RESTORE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186858.aspx
How to: Restore a Database Backup (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms177429.aspx
3.2. Compatibility Level SQL 2000/ SQL 2008
If you restore your database in SQL Server 2000 SQL Server 2008, the level of compatibility will default mode "SQL Server 2000 (80).
To know the level of compatibility, Make a right click on the name of the database>> "Property"
Then in the dialog "Database Properties", click "Options"
To enjoy all the new features in the new engine SQL server 2008, you must change the compatibility level to 100.
To know the differences between compatibility 80, 90 or 100, I invite you to read the following article
http://msdn.microsoft.com/en-us/library/bb510680.aspx
sp_dbcmptlevel (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms178653.aspx
3.3. Transferring SQL Server logins and Windows
There are different ways to migrate your users
- SQL Server Intégration Services, with component « "transfer Login task ».
- SQL Server Management Studio, with “Copy Database Wizard”
- You can also draw on examples of script between SQL Server 2000 and 2005 KB Article http://support.microsoft.com/kb/246133
3.4. SQL Server Agent jobs
You can migrate your SQL Server Agent jobs using Enterprise Manager 2000. You can find more detail in the documentation below:
How to script jobs using Transact-SQL (Enterprise Manager)
http://msdn.microsoft.com/en-us/library/aa177024(SQL.80).aspx
3.5. Other components
You must also reconfigure the components such as SQL database Mail extended stored procedures, linked servers...
3.6. Update statistics
It is recommended that, after having committed or changed the compatibility mode to 100, execute the stored procedure: sp_updatestats
The procedure allows sp_updatestats system to recalculate the statistics and make an update for all the statistics on each table in your base data. To avoid errors related to the statistics of the previous version.
sp_updatestats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173804.aspx
SQL server DBA
April 7, 2012 at 6:55 am
kranthi.india123 (4/7/2012)
3.6. Update statisticsIt is recommended that, after having committed or changed the compatibility mode to 100, execute the stored procedure: sp_updatestats
You first execute DBCC UPDATEUSAGE(), this is required when moving from 2000. It should be followed by DBCC CHECKDB WITH DATA_PURITY. I would also perform a full rebuild of all indexes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 7:23 am
Perry Whittle (4/7/2012)
george sibbald (4/4/2012)
User databases either backup\restore or detach\attach. Backup\restore usually best because the files to copy are smaller and backout is easier if problems with the migration.The backups generally won't be any smaller, so you have the time it takes to back up and restore plus the tim to copy the backup files across the network.
For larger databases I have found detach, copy, attach much faster as the detach\attach operations are quick. Of course all of these options rely on sufficient network bandwidth.
How can a backup file be larger in size than a data AND log file?
Unless the database is very big and takes a long time to backup and restore I prefer the backup method and plan the time in. You can always do the full restore up front and then apply one last log backup.
Backing out is also easier if you haven't detached the database.
---------------------------------------------------------------------
April 7, 2012 at 7:57 am
Are referring to native or 3rd party backups, there is a difference
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply