January 25, 2016 at 5:50 am
We have recently acquired a new SAN Storage system, and all databases and the tempdb need to be transferred to the new storage. I've created new LUNs and made them available on the SQL Server. Now, the databases are in Availability groups, so this complicates the move of the databases a bit. So far, i've come up with the following steps:
1] Disable any applicable logons
2] Kill any remaining connections
3] Remove databases from availability groups
4] Take databases offline
5] Copy database files to the new disks
6] Move tempdb to system disk
7] Restart SQL-Server service
8] Reassign driveletters on new and old disks
9] Move tempdb to new disks
10] Restart SQL-Server service
11] Bring the databases back on-line
12] onlineabases back in the availability groups
13] Enable any logon disabled in step 1
The one thing that keeps bugging me is that i have one database that has replication through a publication/subscription active.
I am at a loss of how to move the associated files to the new disks short of recreating it all.
Has anyone any ideas as to how to move the publication and where it goes in the steps? And how i can get the publication in the availability group, as currently it isn't.
Thanks in advance....
January 25, 2016 at 1:48 pm
How large is the publication in question?
How long is your outage window?
Is this server virtual?
In the past I have done something in the following
create a new server named the same as the old server with a _NEW appended to the name
Installed everything on the 'new' server to mirror the current server (this would include setting up the server for replication), drives, drive names, drive sizes, folder structure, service accounts, etc.
Once happy with the recreation, I would stop the mssql services, make a copy of the system databases for safe keeping.
At the time of the outage, lock down the current source system, disable the services, manually copy the db files to their respective folder locations (including the system databases) on the new server.
At this time, I ask that the current server be renamed to xxx_OLD, rebooted and verify a short time later that the change replicates through DNS.
Then, I request that the xxx_new server be renamed to simply xxx.
Once that change replicates, and DNS updates...
You should be able to move forward with starting the mssql services, and changing the mssql server name from xxx_new to xxx. Restart and test.
I have no idea how well that would work with an availability group. That kind of monkeying around was done with some vanilla databases that didn't have a lot of moving parts.
January 26, 2016 at 1:12 am
Hi, thanks for the reply. In answer to your questions:
The replicated data isn't much, some 700MB. The size of the databases is approximately 100GB. The outage window i have is 3 hours max, and yes, the server is virtual. It is a shared server, servicing 4 applications.
I would have gone for something like you propose but for the fact that i do not have a large outage window, and the server is part of a failover cluster. So, recreating the server would leave me with having to reconfigure or recreate the failover cluster as well. I am pressed for time already because of the need to remove/add the databases to the availability groups.
I have asked around som more and i am resigned to the fact that i may need to recreate the publication/subscription. The thing i haven't figured out just yet is how to get the publication in the availability group. The way it stands now, when i have a failover the subscription will stay on the old server whilst i need it to be available on the otheserver.
Any ideas on how tho achieve this are much appreciated.
January 27, 2016 at 10:48 am
Taking a step back...just so I understand you...
you have the new storage provisioned and already attached and available to MSSQL server(s)?
i.e. new drives that are the same size as the old drives?
If that is the case, why wouldn't you simply be able to disable the mssql services, and do a straight file copy from old drive to new drive?
I have done that in the past as well when I needed to migrate to a different drive on an existing virtual mssql server.
I disabled the services, then changed the drive letter of the drive I was going to me moving the files from (say it is E:\ I changed it to Z:\) and rebooted the server.
Anything that would be looking for the E:\ drive would now fail upon restart and the expectation is no files would be in use.
Then I did a complete copy (not a cut and paste) of the folders / files to the new drive
once complete I flipped the drive letter on the new drive to what it should be, enabled the services and rebooted
worked like a charm.
IDK if that fits you needs or not.
January 29, 2016 at 3:12 am
That's basically what i do i the steps i described. I have the new storage available to the sql server.
However, simply disabling the SQL-Services left me with the database in the availability group corrupted. Luckily i tried this in our test environment first. That is the reason i kill all connections to the database AND remove it from the availability group before i do the copy action. Basically, putting the databases offline is sufficient. After the copy action and the reassigning of the driveletters simply bring them online again. Worked for me before.
As before, i'm not sure what putting the database off-line does to the publication i have set up on that database (and yes, the publication is only available in production, not in test). And how that will react to getting the drive letter changed from under him, albeit for a short time.
I have stumbled upon the following msdn-article, which explains how to get the replication/subscription in the availability group.
https://msdn.microsoft.com/en-us/library/hh882436(v=sql.110).aspx
So, to implement this i will need to recreate the subscription anyway.
I want to thank you for thinking with me!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply