1

Am having trouble with what seemingly should be an easy process. I'm a SQL Server n00b, so please be gentle and very explanatory with your answers.

Currently, I have a SQL Server 2012 cluster (running on top of Windows Server 2008R2), and have created a new SQL Server 2012 Enterprise server in the same AD domain that I want to move all my databases to so I can start using this new instance (its running all SSD drives for better performance with databases, logs, temp logs, etc, all on different 512GB SSD drives). The new instance uses the same AD group as SQL administrators, so logins are the same.

I want to copy all the databases from the "old" SQLCluster to the new SQL instance (the new instance is standalone). I've tried the Copy Database Wizard, making sure firewalls are stopped on both instances (seems easiest to avoid conflicts), telling it to make a text log file at the root of C: drive in case anything goes wrong. However, the wizard gets to the last step (running the job) and bombs out, but doesn't create a log file ... it just says the job failed, giving me little to go on. Both instances have the SQL Agent running.

Can someone give me some guidance on the easiest way to do this while leaving the databases on the source cluster online? Note that I also have hourly backups that run via R1Soft that make copies of the database and log files; I thought perhaps I could just dump the MDF and LDF files from this backup in a directory and re-attach them, but the file structure is different on the new SQL Server 2012 machine with log files and DB files on different drives, and I get an error there with that, and don't know how to change those paths to reattach the databases from the backup (they are straight images of the MDF/LDF files, NOT SQL Server backups).

Thanks for any help you can provide.

  • Set up log shipping and copy the logins using a script which maintains the sid (there are a few here but I'm on a phone, so...). When you're ready to cut over it's simple. Do not use the Wizards and definitely do not use detach/attach. – Aaron Bertrand Jan 5 '15 at 13:08
1

Run this on your current instance:

select
    'CREATE LOGIN ['+p.name+'] FROM WINDOWS;'
from sys.server_principals as p
where type in ('U','G')

Take it's output and run it on the new instance. Those AD accounts will now have logins on both instances. (This is a good opportunity to audit your access and ensure all those users really are needed.)

Backup your databases (a proper SQL Server backup, not a copy of mdf and ldf files) and restore these to the new instance. You may have to get creative with the restores if the drive names have changed. If there are a lot of DBs some RESTORE FILELISTONLY and a script will save much time.

Finally, on each DB, script an ALTER USER to reconcile LOGINS to USERS. This can be meta-scripted as above.

Test connections to the new instance. Update the applications' connection strings. Take the old DBs offline and, after a few days, tidy away the old server.

  • When I run the script and then run the output on the new machine, I get that all the logins already exist except for the SQLCluster users on the cluster not existing on the second box, which is not a cluster. For users that access both SQL boxes they are in a Active Directory group (called SQL_Admins), not listed individually (so that I can keep an easy list of who has access and remove and replace them as needed). As for the Copy Database Wizard, I tried again tonight and ask it to add the errors to the event log, and this time I got an error: Login failed for user 'CORP\SQL1$' – CrainBramp Jan 6 '15 at 3:31
  • Your recommendation to do database backups instead of moving the MDF/LDF files may have done it for me. I was able to backup 5 databases from the cluster, move those files to the new SQL server, and restore them, changing the drives/directories where they live on the new SQL Server (I'm running all SSDs on the new server and have everything (DBs, Logs, TempDBS, etc) on different SSDs), and it seems to have worked great. – CrainBramp Jan 6 '15 at 3:58

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.