Published
Feb 16 2019 04:19 AM
591
Views
First published on TECHNET on Mar 21, 2017
There may be circumstances where creating SQL replica automatically over the network may not be possible due to network bandwidth constraint between DPM server and SQL server(situations where DPM and protected server are in different sites). In such situations we can rely on manual replica creation process. This lets us create a replica on the DPM server without transferring the data from the protected server. Data can be copied to a USB drive and then exported over to DPM server and then copied over to the replica path.
Here is a step by step procedure for creating manual replica for SQL protection in DPM.
Note: This involves stopping SQL server Service to copy database and log file from the SQL server(These files are in use and cannot be copied unless SQL Server service is stopped).
1. On the DPM console click on New to create new protection group.
2. Select Servers and click Next.
3. Expand the SQL server listed under the domain and select the database to be backed up. In my lab it's named TestDB.
4. Name the protection group and hit Next.
5. Specify Retention range, Synchronization frequency, and backup schedule and click Next.
6. Leave the values to default and hit Next.
7. On the next page select "Manually" from under "Replica in DPM Server" and click Next.
8. Leave the options to default selection and click Next.
9. Next page will show summary of the settings and data selected. Click on "Create Group".
The next page will show you the Allocate replica and protection group creation status. Close the page.
1o. Note that SQL protection shows Manual Replica Creation Pending state. Click on "Click to view details" next to Replica path.
11. You will see a pop window showing Replica path. Copy this path. This is path which you will use later to copy the database files to.
In my lab I have copied the following path.
C:\ on rsql2012.Contoso.com C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Volumes\Replica\SqlServerWriter\Vol_ba644616-05c4-4f06-bc8e-05ed4bd8ef46\86740339-0e6f-4c7b-8bf3-28682d532fc9\Full\C-Vol\
12. Now we need to copy the database files from the SQL server to any USB disk. Log on to your SQL server and open SQL Server Management Studio. Right click on the database which needs to be protected and click on Properties. On the Database properties page click on "Files". This will show you the path for your database files. Note down the path to files(from the screenshot we have Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA). Later we will need to add this directory structure to replica path and then copy files.
Stop the SQL Server (MSSSQLSERVER) service and navigate to the path to copy the database and log file. In my lab its named TestDB.mdf and TestDB_log.ldf.
13. On the DPM server download PSExec ( http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx ) and extract the files. Open command prompt and navigate to PSTools folder and run the following command.
psexec -s -i cmd.exe
Above command will open a new command prompt in System context. This will help in creating directory structure and navigating to replica path to copy files.
Now we need to add Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA directory structure to replica path. So replica path will look like C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Volumes\Replica\SqlServerWriter\Vol_ba644616-05c4-4f06-bc8e-05ed4bd8ef46\86740339-0e6f-4c7b-8bf3-28682d532fc9\Full\C-Vol\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA.
Use Mkdir - p command to create desired directory structure.
Use Robocopy to copy the database file to replica path. I placed the copied over database and log file to C:\SQLfiles on DPM server and ran following command to copy the files to replica path.
Robocopy "C:\SQLfiles" "C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Volumes\Replica\SqlServerWriter\Vol_ba644616-05c4-4f06-bc8e-05ed4bd8ef46\86740339-0e6f-4c7b-8bf3-28682d532fc9\Full\C-Vol\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA" /Copyall
14. Once the copy is complete you can run consistency check on the datasource.
Once Consistency check completes it should show the Protection Status as ok.
I hope the above steps will help you create a manual replica in situations where you cannot transfer the data over the network due to a slow network.
Jitendra Mishra
Microsoft Support Escalation Engineer
There may be circumstances where creating SQL replica automatically over the network may not be possible due to network bandwidth constraint between DPM server and SQL server(situations where DPM and protected server are in different sites). In such situations we can rely on manual replica creation process. This lets us create a replica on the DPM server without transferring the data from the protected server. Data can be copied to a USB drive and then exported over to DPM server and then copied over to the replica path.
Here is a step by step procedure for creating manual replica for SQL protection in DPM.
Note: This involves stopping SQL server Service to copy database and log file from the SQL server(These files are in use and cannot be copied unless SQL Server service is stopped).
1. On the DPM console click on New to create new protection group.
2. Select Servers and click Next.
3. Expand the SQL server listed under the domain and select the database to be backed up. In my lab it's named TestDB.
4. Name the protection group and hit Next.
5. Specify Retention range, Synchronization frequency, and backup schedule and click Next.
6. Leave the values to default and hit Next.
7. On the next page select "Manually" from under "Replica in DPM Server" and click Next.
8. Leave the options to default selection and click Next.
9. Next page will show summary of the settings and data selected. Click on "Create Group".
The next page will show you the Allocate replica and protection group creation status. Close the page.
1o. Note that SQL protection shows Manual Replica Creation Pending state. Click on "Click to view details" next to Replica path.
11. You will see a pop window showing Replica path. Copy this path. This is path which you will use later to copy the database files to.
In my lab I have copied the following path.
C:\ on rsql2012.Contoso.com C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Volumes\Replica\SqlServerWriter\Vol_ba644616-05c4-4f06-bc8e-05ed4bd8ef46\86740339-0e6f-4c7b-8bf3-28682d532fc9\Full\C-Vol\
12. Now we need to copy the database files from the SQL server to any USB disk. Log on to your SQL server and open SQL Server Management Studio. Right click on the database which needs to be protected and click on Properties. On the Database properties page click on "Files". This will show you the path for your database files. Note down the path to files(from the screenshot we have Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA). Later we will need to add this directory structure to replica path and then copy files.
Stop the SQL Server (MSSSQLSERVER) service and navigate to the path to copy the database and log file. In my lab its named TestDB.mdf and TestDB_log.ldf.
13. On the DPM server download PSExec ( http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx ) and extract the files. Open command prompt and navigate to PSTools folder and run the following command.
psexec -s -i cmd.exe
Above command will open a new command prompt in System context. This will help in creating directory structure and navigating to replica path to copy files.
Now we need to add Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA directory structure to replica path. So replica path will look like C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Volumes\Replica\SqlServerWriter\Vol_ba644616-05c4-4f06-bc8e-05ed4bd8ef46\86740339-0e6f-4c7b-8bf3-28682d532fc9\Full\C-Vol\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA.
Use Mkdir - p command to create desired directory structure.
Use Robocopy to copy the database file to replica path. I placed the copied over database and log file to C:\SQLfiles on DPM server and ran following command to copy the files to replica path.
Robocopy "C:\SQLfiles" "C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\Volumes\Replica\SqlServerWriter\Vol_ba644616-05c4-4f06-bc8e-05ed4bd8ef46\86740339-0e6f-4c7b-8bf3-28682d532fc9\Full\C-Vol\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA" /Copyall
14. Once the copy is complete you can run consistency check on the datasource.
Once Consistency check completes it should show the Protection Status as ok.
I hope the above steps will help you create a manual replica in situations where you cannot transfer the data over the network due to a slow network.
Jitendra Mishra
Microsoft Support Escalation Engineer