Solved

SQL 2005 to 2012 Migration Question

Posted on 2014-01-17
15
34 Views
Last Modified: 2015-11-01
All,

I have done a number of migrations in the past but mostly same versions, I am looking into a project that will require I migrate 16 database servers from single instance (Physical Server) 2005 into a single virtual server running named instances and using SQL 2012.

I need to migrate the db's including the system db's which hold the log in's and permissions.  Is there a best practice or step by step procedure I can follow to accomplish this?

And before anyone is concerned about iOPS this is a SSD Array all DB certified SSDs :)

Thanks!
0
Comment
Question by:smyers051972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 3
  • 2
15 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39789362
You can't migrate the system databases and you should never think of that. If you have to migrate the jobs and logins by scripting them out on source and execute the scripts on destination.

Migrate jobs with transfer job tasks. In MS under Management node right click on Maintenance Plans > New Maintenance Plan > in the Tools panel on the left grab the Transfer Jobs task and drag it to the designer panel and then go into its properties to setup the job migrations:
http://technet.microsoft.com/en-us/library/ms137568.aspx

for logins scripting:

http://support.microsoft.com/kb/918992
0
 
LVL 1

Author Comment

by:smyers051972
ID: 39789373
How about all the DB Permissions?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39789444
You do this:

1. Script logins on 2005 server and then run the script on SQL 2012
2. Backup databases on 2005 and restore on 2012
3. Synchronize the logins with the databse users with:
USE database
GO

-- lists the orphaned users in the database
EXEC sp_change_users_login 'REPORT'
GO

Open in new window

all the users reported above you will fix with:
-- fixes one user - login orphan probloem: 
-- EXEC sp_change_users_login 'UPDATE_ONE', 'db_user', 'login'
EXEC sp_change_users_login 'UPDATE_ONE', 'user', 'login'

Open in new window

only SQL users will be reported and need this fix; details about user-login sync here: http://technet.microsoft.com/en-us/library/ms174378.aspx

3. Migrate the jobs.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:smyers051972
ID: 39789541
perfect ill try this Sunday and report back! Thanks! I assume this will be the same after adding the named instances as well?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39789807
Correct.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39789876
Here is one that you may want to see about doing is building you own maintenance plans that runs from a query window. That way when you have to move or add another SQL Server, you can just use the script to create the new jobs.

The sp_helprevlogin as ZB noted above is the best way to move logins. I also add a job
to any SQL Server and automatically run it every 8 hours and dump it to disk to be backed up.  That way if the SQL Server instance crashes, I can just re-install the SQL Server from scratch. Restore the databases and then use the scripts to recreate the jobs and logins.

Another suggestion that will make DB moves easier in the future. If you have access to the DNS system create a CName record that is like DBName that points to the ServerName or IP. Even if the DB is a named instance such as ServerName1\InstanceNm have it point at the DBName\InstanceNm. The reason for this is that if the clients  are using DBName\InstanceNm and the original server should crash for some reason, all you have to do is restore the DB to ServerName2\InstanceNm and then change the DNS record and not touch the clients. It will also allow you to move a single DB at a time instead of having to do all five at one time and touching every client.

Just some tips that I have picked up over the years.
0
 
LVL 1

Accepted Solution

by:
smyers051972 earned 0 total points
ID: 39800483
@Zberteoc:

I Ran the first part of the script on the 2005 server and get an error that it could not locate the stored procedure.

Here is what you said to run:
USE database (Assumed MASTER DB)
GO

-- lists the orphaned users in the database
EXEC sp_change_users_login 'REPORT'
GO
0
 
LVL 1

Author Comment

by:smyers051972
ID: 39800505
I also found this article but it mentions moving sql 2005 -> 2005.

http://www.techrepublic.com/blog/how-do-i/how-do-i-transfer-logins-from-one-sql-server-2005-instance-to-another/#.

I also learned that we are using 2008 as the destination not 2012 as originally thought out.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 39804130
ok so I am importing the db everything there going well when I export the users on the old server and import the domain users i.e. domain\user seem to not be found?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39804231
Is the new SQL Server in the domain?

What userid are you using to run the SQL Server services? If it isn't a domain user id it can't really query the AD easily.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 39804256
New server, its in the domain but you know what I am running them all as local service accounts... maybe this needs to change lol
0
 
LVL 1

Author Comment

by:smyers051972
ID: 39833437
This is still open as a project sorry for it not being closed yet.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 39976945
This is still an open project
0
 
LVL 1

Author Comment

by:smyers051972
ID: 40009441
Project was put on hold 2 weeks ago but will be resuming shortly.
0
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 41165424
Good answer
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question