February 25, 2011 at 1:15 am
We are planning to migrate from SQL 2k5 to SQL 2k8. We have three databases in each of our three sql 2k5 environments (Prod/Pre-Prod/Dev). One online application with same three environments (pointing to respective database environments). What should be the generalized approach to migrate as App DBA ? I am doing it for the first time and want to do it step by step.
February 25, 2011 at 2:29 am
you can take backup on all your currently running user database and can restore them on 2008 so that you no need to take long downtime for moving your database from 2005 to 2008.
If you have too many logins in your 2005 environment then for logins and role you will find a script on msdn which creates the sp_helprevlogin SP to transfer login and password(for sql account).
Script out all the jobs and run it on your 2008. Let them disable till you not moved completely on 2008.
After restoration of latest backup(full/diff/log) just update the stats and change the compatibility level to 10.
----------
Ashish
February 25, 2011 at 8:10 am
Thanks Ashish.
February 25, 2011 at 6:39 pm
Are you migrating to new hardware or upgrading in line?
No matter what, be sure you have good backups of all databases ( including master/msdb) on another machine before you start.
February 27, 2011 at 9:06 pm
Yes, we are migratng to new hardware.
March 3, 2011 at 9:23 am
If you have new hardware, you will install the new software on it, then move logins
Login migration: http://www.sqlservercentral.com/articles/934/
Move over databases from either a backup/restore or detach/attach, then script out alerts/operators/jobs and run the scripts on the new server.
March 3, 2011 at 12:07 pm
Steve Jones - SSC Editor (3/3/2011)
If you have new hardware, you will install the new software on it, then move loginsLogin migration: http://www.sqlservercentral.com/articles/934/
Move over databases from either a backup/restore or detach/attach, then script out alerts/operators/jobs and run the scripts on the new server.
A restore of the master and msdb databases from fresh backups of "old" prod, prior to restoring the rest of the user databases, should take care of jobs, logins etc.
I must confess though that I haven't tried that before, but rather have also followed Steve's approach above when doing migrations.
You may also need to migrate:
- trace flags:
- server-level permissions of logins (VIEW SERVER STATE, VIEW ANY DEFINITION permissions)
- Windows-level permissions of NT accounts that are logins on the SQL instance: eg. do any logins need to be part of the local-admins group?
- Linked servers
- server-logins' mappings to database roles on master and msdb database (if you don't restore those dbs on the new server)
- server-login permissions on master and msdb database objects: eg. SELECT permissions on msdb.dbo.sysjobs
- sp_configure settings that are different from the defaults: eg. MAXDOP <> 0
- any custom objects that may exist in the system databases.
- application files and SSIS packages that may be referenced in SQL jobs or elsewhere by the application.
- any other custom settings, such as job-history pruning (normally recorded in msdb)
- permissions of the new SQL-Server startup-service and SQL-agent accounts on network shares
- tempdb specialized settings: number of files, sizes, autogrowth settings
- database-mail config settings, if different from the defaults: eg. maximum attachment file size
I have revently submitted an article on this site that discusses some of these complexities, which I hope will be published soon. 😉
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply