0

We are migrating our SQL Server 2008R2 instance from one Windows Server platform to another. SQL Server will remain as 2008R2. The new plaform will have more disk space, memory and CPU.

I am doing up a tasklist of what is required to migrate SQL Server. Below is an outline of what I think are the steps involved. Are these "best practice" or am I missing some requirements?

Thanks!

1) Backup and Restore Databases - Is this the best option? Use Ola Hallengren Script

2) Migrate Logins - use Microsoft KB? kb/918992

3) Migrate Credentials/Certificates - what is required for this?

4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)

5) Migrate SSIS Packages - (How to do this?)

6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)

7) Recreate assemblies

8) Recreate Linked Servers

NOTE: System Databases - do these need to be migrated?

Improve this question
2
  • 1
    Restoring system database with users databases would be best and will save lot of time, save you from migration jobs and logins. You can very well restore system databases please refer to Micrsooft link msdn.microsoft.com/en-gb/library/ms190190.aspx
    – Shanky
    Jul 11, 2014 at 10:16
  • 2
    Not really an answer in any detail, hence using a comment, but if your backups and disaster recovery plan are well designed then you should just be able to use that. Stop access to the apps, run a final refresh of your backups, restore to the new hardware as per the DR plan, test, then reactivate the services once all seems well. You presumably don't have this setup already, so I recommend using this migration as a good time to get that arranged and tested. Your users/clients will be glad you made such preparations if a disaster does strike at a later date.
    – David Spillett
    Jul 11, 2014 at 10:25

1 Answer 1

Reset to default
3

Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time:

1) Backup and Restore Databases - Is this the best option?

It's an option. If you need the downtime to be less, you can use mirroring or log shipping (or do it by hand) to keep the databases in sync and then migrate during a fast downtime or cutover.

Please note that system databases cannot be log shipped or mirrored and would need to either be frozen or copied right before the cutover.

2) Migrate Logins - use Microsoft KB? kb/918992

If you restore the master database to the new instance, no migration should be needed. All server level logins are stored in the master database.

3) Migrate Credentials/Certificates - what is required for this?

If this is inside of SQL Server, these would be held in their respective databases (and possibly master as well). The one difference would be the service master key (SMK) that would change. You make want to back that up and restore it on the new server if you're relying on automatic key decryption.

4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)

If you restore the msdb system database, all agent jobs will be held in it. There would be no need to script->restore.

5) Migrate SSIS Packages - (How to do this?)

If the SSIS packages are on the filesystem (not default) it would be trivial to create the same location on the new server and copy. By default the SSIS packages are held in the msdb system database and restoring it would get you to the same place, just like #4 and previous.

6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)

This is also stored in msdb. See #4, #5.

7) Recreate assemblies

These live in their respective databases. If backup and restore is used, this should not be a problem. Any assemblies outside of SQL Server would need copied to the new server.

8) Recreate Linked Servers

These also live in the master database, see #2.

Improve this answer
9
  • Thanks! Does Backup and Restore work for Database certificates/credentials? So backup/restore of the Database should automatically handle certs/credentials?
    – K09
    Jul 11, 2014 at 17:09
  • 1
    @k09 If you look at #3 in the list again, if the certificate is inside of SQL Server, it will be stored in whatever database it was created in. Yes, backup and restore will take care of that. This goes for credentials too, which are at a server level (which is in master, not a user database as #2 and #3 also suggest).
    – Sean Gallardy
    Jul 11, 2014 at 17:15
  • The Active Directory will be changed. Is there a script I can use to update existing SQL Server users/logins to point to new AD?
    – K09
    Jul 17, 2014 at 8:14
  • If you're changing active directory domains, this would need more than just an "update" of the users. It would require creating them from the new AD source.
    – Sean Gallardy
    Jul 17, 2014 at 12:19
  • So I would have to recreate each user/login?
    – K09
    Jul 18, 2014 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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