Moving databases to new storage within an Availability Group

  • [アーティクル]

I had a customer come to me for help in adding storage for their SQL servers which were supporting their SharePoint farm.  Their SQL architecture consisted of three (3) SQL servers configured in an AlwaysOn Availability group.  One server was configured as the primary replica for Search, one as the primary replica for Content, and the third server as the primary replica for "Other."  Each of the servers then served as a secondary replica for its sister servers.  For the purposes of this article I will refer to these servers as Server1(Search), Server2(Content) and Server3(Other).

The customer had a constraint imposed by their virtualization layer that no disk could be larger than 2 TB, and the size of their content databases was getting near to the limit.  The solution we arrived upon was to simply add another disk and move some of the content databases to spread the workload out onto the new storage.  As there were almost 200 individual content databases (and because I am inherently lazy) I wasn't keen on doing an detach/move/attach for each one.  Starting with this article, I developed another procedure which was much faster and only involved a couple of Availability Group failovers in terms of downtime.

In short, the process is to run a series of "Alter Database...Modify File" statements which in effect moves the pointer to where SQL expects the file to be, stop SQL, move the .mdf database files, and then start SQL again.  In order to make things as easy as possible, I wrote a T-SQL script which creates the "Alter Database...Modify File" statements automatically based upon where things currently reside and where they should reside in the future, as well as the copy and delete (cleanup) scripts to run on the file system.  All you have to do is add the names of your databases and the new storage location.  Then it's simply a matter of shuffling things around between the servers.  That process is as such (and assumes you've taken a recent back up of everything):

  1. Disable read-only access on each secondary replica
  2. On each server, run the script (below) which will create the "Alter Database...Modify File" statements as T-SQL, as well as the copy and delete scripts, which can be run in a command or PowerShell prompt.
    1. Suggest that you save the copy and delete statements as command files (.cmd) on each of the servers.
  3. On each secondary (i.e. Server1(Search) and Server3(Other)), run the "Alter Database...Modify File" statements.  This will move the pointers for the data files.
  4. Server1(Search) and Server3(Other) are currently primary replicas for the Search and Other Availability Groups.  Fail these over to Server2(Content) so Server2(Content) is now the primary replica for all three AGs.
  5. Stop the SQL Services on Server1(Search) and Server3(Other).
  6. Run the Copy statements as an elevated user on Server1(Search) and Server3(Other).  This will copy the database files to their new location.
  7. Start the SQL Services on Server1(Search) and Server3(Other).
  8. In SSMS, verify that the moved databases are showing healthy and are participating in replication after they recover.
  9. Move the Search and Other Availability Groups back to Server1(Search) and Server3(Other), respectively, so they are again the primaries for their Availability Group.
  10. Move the Content Availability Group to make the Server1(Search) server the primary replica.
  11. Run the "Alter Database...Modify File" statements on Server2(Content).  This will move the pointers for the data files.
  12. Stop the SQL Services on Server2(Content)
  13. Run the Copy statements as an elevated user on Server2(Content).  This will copy the database files to their new location.
  14. Start the SQL Services on Server2(Content).
  15. In SSMS, verify that the moved databases are showing healthy and are participating in replication after they recover.
  16. Move the Content Availability Group back to Server2(Content).
  17. Final step (when all databases are healthy and online):  Run the "Delete" scripts on all three servers to clean up the old database files.

With that set of procedures and a couple of interruptions of the AG listeners, we are able to move the databases around with little to no apparent disruption to users.  This is important because, as the size of the database we are moving gets larger, the copy time increases as well.  This process avoids this problem as we're always working against a secondary replica, not the primary.

Here's the script:

/*Update the @newLocation variable for your actual new data file location*/ /*Update the values to load into the @sourceDBs table to reflect your actual database names*/ DECLARE @sourceDBs TABLE (name nvarchar(500)) DECLARE @newLocation nvarchar(500) = 's:\sqldata\' MERGE INTO @sourceDBs as target using (VALUES ('Content_DB1'), ('Content_DB2')) as source (name) on target.name = Source.name when not matched by target then insert (name) values (name); ;with CTE as ( select d.name as [DB_Name], f.name as [File], f.filename as [FilePath], reverse(left(reverse(f.filename),(charindex('\',reverse(f.filename),0))-1)) as [FileName] from sysaltfiles f inner join sysdatabases d on (f.dbid = d.dbid) inner join @sourceDBs sdb on sdb.name = f.name ) select 'Alter Database [' + [DB_Name] + '] modify file ( name = [' + [File] + '], filename = N''' + @newLocation + ''');' as alterDBScript, 'xcopy "' + [FilePath] + '" ' + @newLocation + ' /Y' as CopyCommand, 'powershell rm "' + [FilePath] + '"' as DeleteCommand from CTE

Comments

  • Anonymous
    March 06, 2017
    Nice post. Thanks!
  • Anonymous
    September 20, 2017
    I think there is a slight typo in scriptinner join @sourceDBs sdb on sdb.name = f.nameshould beinner join @sourceDBs sdb on sdb.name = d.nameOtherwise - very useful - thanks
    • Anonymous
      September 21, 2017
      Robin - in my case, I joined to sysaltfiles as this filters the logs (LDFs) out of the result set, as the logs were out of scope of what I was moving. Otherwise, yes, if you're looking to move the logs AND the database files, you are correct with your join statement.Thanks!
  • Anonymous
    February 22, 2018
    I'm a bit confused on steps 3 and 4. In step 4, you say server1 and server3 are secondaries, but in step 4 you say they are primaries.
    • Anonymous
      February 22, 2018
      Sorry..in step 3 you say they are secondaries...
      • Anonymous
        February 22, 2018
        No worries - it gets a little squirrely when you're switching back and forth. I'll look at clarifying the language - thanks!