May 10, 2008 at 11:37 am
Comments posted to this topic are about the item Moving Databases
May 12, 2008 at 6:49 am
Another factor in a move is the use of SQL 2005 encryption in the database. Does the database have a database master key secured and automatically being opened for use via the Service Master Key? If so, you need to be sure you have the password so you can re-sync the DBMK with the new server's SMK using the Alter Master Key command. Hopefully if the dba has also been following best practices you also have a backup of all SMK's and DBMK's and have the stored in a secure location.
May 12, 2008 at 6:50 am
Item 3 mentions replication and the fact you have to remove replication first. If you're moving a file with merge replication on the same server (to one with my free space) and you're under SQL Server 2005 SP2, I know you don't need to remove replication.
alter database [123456] set offline;
--move the database to the new loacation......say J:\Data
alter database [123456] modify file (Name='123456', filename='J:\DATA\123456.mdf');
alter database [123456] set online;
I've seen several articles stating replication must be removed, but I had this situation recently, consulted with Microsoft and was given this method and it has worked very well. I haven't tried this with transactional or snapshot replication, but wouldn't know why those also wouldn't work with this method.
Doug
May 12, 2008 at 8:20 am
#4. Linked Servers and cross-server (or cross-database) queries has been a recent pain point for me. I've been wondering why the applications weren't just built to connect to both places and retrieve the data from each and then deal with the result sets at the application level. I think it applies moreso to DTS (SSIS) packages and dynamically managing your connections.
It really becomes an issue when you've got a full test environment and are constantly moving stored procs between environments that have hard-coded server/db names.
May 12, 2008 at 8:44 am
Andy,
Good Job with the article.
- DNS Aliases (your #8). They do help when moving 1 application database and not the whole server. When you have application aliases or host headers changing just the IP on the DNS server will help.
By application alias I mean, a normal DNS alias that is used only by 1 application. Another application or users what use another application use another DNS alias reflecting the name of this second application.
- Also if leaving a copy of the database on the old server, put it in the restricted mode so users who don't know about the change would not enter production data in the old database (if they use server name and not the alias)
Another issue that we try to avoid is testers forgetting this is a test copy start to enter production data into the test database that will be overwritten during the production move.
Regards,Yelena Varsha
May 12, 2008 at 9:50 am
Andy a very good article. What are major precautions a user should take before performing these steps?
May 12, 2008 at 9:58 am
Nice check list.
May 12, 2008 at 12:27 pm
Doug Hora (5/12/2008)
alter database [123456] set offline;--move the database to the new location......say J:\Dataalter database [123456] modify file (Name='123456', filename='J:\DATA\123456.mdf');
alter database [123456] set online;
Cool. I must be missing something here. I have two drives for my database. S: for the data and T: for the log. Some new guy here installed both on S:. How do I move just the log part?
ATBCharles Kincaid
May 12, 2008 at 1:06 pm
Charles,
Let's say [123456] is the name of your database.
Determine the logical filename and physical filename of your log file (via Mangement Studio or sp_helpdb '123456') . From that, let's say the logical name is 123456_log and the physical file is S:\123456.ldf
You should be able to do the following.
alter database [123456] set offline;
--move the log file to the new location......say T:
alter database [123456] modify file (Name='123456_log', filename='T:\123456.ldf');
alter database [123456] set online;
Let me know if this doesn't work for you.
Doug
May 12, 2008 at 1:30 pm
Thanks Doug. Books On Line (great in 2000, sucks rocks in 2005) was not clear on that. 2005 BOL is lacking in examples. Now that I see that Name= means the LOGICAL file name it's very clear. Ive been:
A. Detach the database (GUI).
B. Move the files around.
C. Using the attach SP to conect the files back.
ATBCharles Kincaid
May 12, 2008 at 1:56 pm
Standard drive letters are nice. But, one place that you are forced use different letter is on Microsoft Cluster Services clusters. Each instance on a cluster must have its own set of drive letters. This is done because the possibility exists that all virtuals could be running on the same node.
Clustering is great for some things. It certainly minimizes down time for OS patches and other reboots since the passive nodes can be patched and the virtuals failed over to them. Otherwise, a reboot could take 15-20 minutes or more on large machines. A failover for SQL Server is generally 30 seconds to 2 minutes.
I just did a cluster hardware upgrade last week and had to move some databases to new drive letters on the new hardware. User databases are easy to move. The system databases were a minor PITA.
- Jay
May 12, 2008 at 4:39 pm
sorry for the dumb question but we have two server support people here and no dba any more.
we needed to move a database to a new server the other day so we just created a plain vanilla database on the new server and then backed up/restored the database from the old server to the new server over the top of the plain one created.
however nobody could log in succesfully after the move. we thought that this might be bacuse we had not moved the master database over also but when we tried to do that it crashed in the middle of restoring and so we had to back out of the whole thing and bring the old one back up again.
could someone explain in *really* simple terms what we need to do to move the database and the master database from one server to a new one?
tia
May 12, 2008 at 4:53 pm
SQL 2005? 2000?
Are master and the other system databases going to have the same drive letter/path on the new box?
If so, it should be fairly straightforward. Start SQL Server in single-user mode from the command line (sqlservr -c -m). Use sqlcmd, osql or isql to restore master. If you are using a bacjkup tool like SQL LiteSpeed, you would need to use one of their utilities instead.
Once that is done, issue a shutdown and restart SQL Server as you normal would. You should not need to do anything further to your user database. All the logins should map correctly to the database users.
- Jay
May 12, 2008 at 7:48 pm
A couple of things left out here are CLR assemblies, User Defined Datatypes, and the Service Broker. It's a good practice to ensure the proper dll versions for the CLR's are in place on the new server and registered in the GAC. For the service broker, learn your order of install as the scripts will have to be run in a certain order.
May 13, 2008 at 9:33 am
Jay B (5/12/2008)
... Otherwise, a reboot could take 15-20 minutes or more on large machines. ...
That's just about the normal boot time for a good size AS-400. I know one place that switched the mains feed to a locking connector at both ends. They felt it would be cheaper to pay worker claims for tripping over the cord than to have someone yank it loose.
ATBCharles Kincaid
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply