5

We have a production SQL Server 2005 server on which we are hosting 200 databases.

We want to host these databases on our new server. On new server SQL Server 2005 is installed and is ready.

I want to know the best and the most convenient way to migrate the whole data includes:

  • Security and permission
  • Users and memberships
  • All of the databases and ..

In one word I want to have exactly the same copy of the current SQL Server 2005 on our new instance on the new server.

I will be thankful if you guide me on this case.

Best regards

6

Disclaimer : SQL Server 2005 is out of mainstream support. - extended support ends for sp4 on 04/12/2016

Migration always have a downtime. So depending on how much your downtime is, you can go with setting up either :

  1. Logshipping ( I have used this method and it reduces the downtime considerably)

    a. Set up logshipping from the source to destination server as usual. Keep the sync time 1 min (which is minimum).

    b. On the day of migration, make sure the logbackups are in sync.

    c. Stop all the logshipping jobs and then take the final tail log-backup and put the primary databases in no-recovery mode (this is just in case you need to go back to primary).

    d. restore the tail log backups on the secondary with recovery bringing all the secondary databases online.

  2. Database Mirroring : I do not recommend mirroring for migrating 200 databases as you will easily exhaust the worker threads as described in this KB article.. If there were less databases, then this would be a viable option.

If you have enough downtime then I would recommend

backup / restore method (as opposed to detach / attach) as if something goes wrong, you atleast have a good backup to restore back. this script will get you started with moving all the logins and databases. Just modify it to use backup / restore (or take full backups before running the script - as it uses detach/attach method).

Things you can do prior to migration :

  • Refer to this answer for migration steps. Some may apply to your scenario as well.

  • script out all the jobs / ssis packages, etc --OR -- As suggested by @shanky, you can restore the system databases from the old server to new server before hand - that will give you all the logins, jobs, etc on the new server.

  • turn on Instant File Initialization on the new server.

  • Have multiple tempdb data files with equal size.
  • Enable Trace Flag 1118 - to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points.
  • Configure max and min memory correctly. Especially Max memory away from default.
  • Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
  • Best is to install sp_Blitz from Brent Ozar. Run it and address the critical and high priority issues reported by it.
  • You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.

Edit:

You can use powershell to migrate your all databases from one server to another as described at Use PowerShell to Migrate Entire SQL Server Instance (db, logins, jobs, etc)

Migrates databases with simple or complex file structures via backup & restore or detach & attach. Also migrates users with passwords, SIDs, server & db permission sets, server & db roles, server configuration settings, SQL Agent objects, user objects in systems databases.

  • OP never said he is not ok with little downtime as a fact with mirroring and logshipping during 'cut over' there would be downtime and your answer does not include basic thing about restoring system database backup. I dont know why you put different links – Shanky Sep 23 '14 at 15:11
  • @Shanky if you read my answer, I have mentioned that restore the tail log backups on the secondary with recovery bringing all the secondary databases online. Also, the different links are for optimization that are very relevant for sql server 2005 and up. That makes the answer more relevant. I would rather leave with the OP to decide which answer will help. At the end, we all are here to help and get help. – Kin Shah Sep 23 '14 at 15:27
  • I was thinking more in terms of will it be eaiser to backup and restore of 200 database with system databases or creating logshipping or mirroring for 200 databases and then manually moving all jobs and logins and other packages if required. – Shanky Sep 23 '14 at 15:30
  • 1
    there are many steps that can be done pre migration. What I normally prefer is to have as minimum downtime as possible during the actual day of migration, so that there is ample time for testing and addressing any issues that might come up. The steps mentioned are the ones that I have actually implemented. – Kin Shah Sep 23 '14 at 15:33
  • 1
    Thank you sir. This is a really definitive answer and has described all aspects of the issue perfectly. In fact we can have downtime for about 2-3 hours and this is enough for backup and restore. This answer is great. Thank you guys – Armin Sep 24 '14 at 7:14
4

I want to know the best and the most convenient way to migrate the whole data includes: - Security and permission - Users and memberships - All of the databases and ..

Best method to migrate data and all objects related to particular database is by using backup and restore method.Perhaps since you want to migrate whole database to new instance you can backup all user and system databases and restore it on new SQL Server instance

Backup database on current server and copy it to new server and restore it there.

You have to backup system database and restore it on new instance.How to backup and restore system databases. First do backup and restore of system databases.

Backing up and restoring system databases would provide you all logins, jobs and packages stored in MSDB to new instance. New server would also include complete securables and permissions

SQL server 2005 is out of mainstream support its highly advisable to upgrade to latest SQL server version or if you have to stay on 2005 make sure all SQL Server instances are patched to SP4.

  • Aaron, Thank you for your care on my case. Although that above answer is a definitive guide but your answer is great too for cases in which we can have some hours of downtime. Thank you for your care – Armin Sep 24 '14 at 7:15
  • The answer is by Shanky. Aaron was kind enough to edit it 😊 – Kin Shah Sep 24 '14 at 10:31
  • I'm trying to install sql server 2005 on a 2008R2 OS. I receive a warning that this sql server version is not compatible with this OS unless i install Server Pack4 of SQL Server 2005 My source server is using Service pack 2. 1. If i install the service pack4 on the new server i will not be able to restore the MASTER DB for transferring logins and credentials If i install service pack2 instead of the advised one (SP4) will i experience issues with SQL Server 2005 on the 2008R2 OS?? Thank you guys for your helps – Armin Oct 1 '14 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.