January 18, 2012 at 10:46 am
I know how to create an SQL Server Alias but what steps would I go about to implement?
I have an old Server Named (fictitious) Server NY_BI which is in Production.
I'm moving to a new Server IN_BI which has not gone live.
What steps do I need to go through to switch servers? :unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 18, 2012 at 10:56 am
Are they the same version of Windows, same version of SQL Server with the same drive letters/sizes and installed to the exact same paths?
January 18, 2012 at 11:11 am
Markus (1/18/2012)
Are they the same version of Windows, same version of SQL Server with the same drive letters/sizes and installed to the exact same paths?
Yes and thank you.
I thought that you could use SQL Server Aliases so that you did not have issues with the move?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 18, 2012 at 11:35 am
I have used dns aliases for ours... not SQL Server aliases. It should work the same but you'd have to test it out.
If SQL Server is all installed EXACTLY in the same path you can backup and restore master (you have to start SQL Server in single user mode to do this... check books online for exact steps as it changes a little for each version of SQL Server. Then, restore MSDB, then restore each application user database.
You will then have to change the servername from within SQL Server as SQL Server will THINK it is still installed on the old servername.
sp_dropservername......
sp_addservername
again, check books online for the exact syntax for this.
Also of note... depending on which version of SQL Server you are running there are a few gotchas with respect to msdb and maintenance plans. Some version store the servername in one of the tables and that will have to be updated. Otherwise when you attempt to open up the maint plan it will fail to open up.
The best thing to do is to test out all of the above steps and make sure it all works AND more importantly how long it will take (except the alias part)! Practice it a few times until you are comfortable with doing it and have all of the steps documented.
January 18, 2012 at 12:07 pm
Years ago I goofed on switching Master DB.
The last time, I just scripted out the Jobs & it worked, bad idea?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 18, 2012 at 12:12 pm
The jobs and such are stored in MSDB.
The way I have done it... because I have simple installations....
Script out the jobs and then import them to MSDB. Done!
as far as the logins to get master updated....
on the new server, create empty databases for ALL of the application databases you have.
then, script out all of the userids in the production system. Then use THAT script to create them all in the new master db.
then all you have to do is backup/restore the application dbs.
Keep in mind though if you script out the userids and someone adds a new user before the cutover OR changes their password they won't be in the new server. If the userbase is small and you know if you are the only one to create userids or change passwords you can keep control of that.
Again, do all of the steps a few times so you are comfortable with the steps and how long it will take!... If you can.. also attempt to log into the new server with the userids to make sure one or two of them work without any issues.
January 18, 2012 at 4:59 pm
I know that if you stop the SQL Server Service you can replace a User Database.
My guess is that you could do the same to replace the Master & Model Databases?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 19, 2012 at 5:34 am
If you have the master,model,msdb and user databases in the DATA folder and SQL Server is installed in the same drive/path.. .then yes. You stop the service and copy the contents from one to another. However, you will have to stop your production server long enough to do the file copy though.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply