swapping server
-
Hi Folks
If I need to move a database to another server what would be the process for that?
The situation seems complicated by the fact that there are 400+ clients that connect to the databases in different ways (ODBC, ADO.net etc)
-
There are several ways to move a database. You can put the database in single user move and detach the db, copy the mdf to the other server, and attach the database. You can backup the database and restore it on the new server.
As it appears you have determined really the hardest part is how to get the 400+ clients to connect to the new database and that really depends on the types of apps. Obviously web applications will be the easiest as all you need to do is change the connection string(s) for the web apps. If you are using a file DSN on a file share for the ODBC connections you can just change the file DSN. If you have client based apps you will need to re-install or re-configure the clients.
Are you adding and additional server or replacing a server? If it is a replacement server you can just give it the same name as the replaced server or alias it in DNS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Well, don't forget also that your user mappings are going to be out of whack on the new server. They're mapped from each DB to the sid in master and while you can create the logins on the new server they won't have the same sids and the mappings to the DB users won't happen.
You can either create the logins with the original sids, or you can run sp_change_users_login. That will map the usernames back to the logins on the new server. There are a couple caveats in using this, but it's documented in bol pretty well.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
There are quite a few articles on this site regarding moving databases. Search for that and you'll get some answers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply