Cloning a Microsoft SQL Server database on Compute Engine

Setting up the environment

To complete this tutorial, you need to set up your computing environment with the following:

• A SQL Server instance on Compute Engine (named sql-server-prod) to represent your production database server.
• An additional disk (named sql-server-prod-data) that's attached to your production server for storing your production database.
• A copy of the Wide World Importers SQL Server sample database to simulate the production database that you want to clone.
• A SQL Server instance on Compute Engine named sql-server-test to represent your testing database server. You clone your database onto this server.

The following diagram illustrates this architecture.

Create the production VM instance

To simulate a production environment, you set up a Compute Engine VM instance running SQL Server on Windows Server.

The VM instance for this tutorial uses two disks: a 50 GB disk for the OS and user accounts, and a 100 GB disk for database storage.

In Compute Engine, using separate disks offers no performance benefits. Disk performance is determined by the total storage capacity of all disks attached to an instance and the total number of vCPUs on your VM instance. Therefore, the database and log file can reside on the same disk.

Connect to the VM instance

1. In the Google Cloud console, go to the VM instances page.

   Go to the VM instances page

2. Wait about 5 minutes for the VM instance to be ready.

   To monitor the initialization process of the VM, view its serial port output in Cloud Shell:

   gcloud compute instances tail-serial-port-output sql-server-prod
   

   When you see the following message, the initialization is complete.

   Instance setup finished. sql-server-prod is ready to use.
   

   Press Control+C to stop monitoring the serial port.

3. Click the instance name sql-server-prod to open the VM instance details page.

4. Under Remote access, click Set Windows password, and then click Set to create your account on the remote machine.

   This step generates a password for you. Make a note of the password or copy it to a secure, temporary file.

5. In the Compute Engine section of the Google Cloud console, click the RDP dropdown and select the Download the RDP file option to download the RDP file for your instance.

   Use this file to connect to the instance using an RDP client. For more information, see Microsoft Remote Desktop clients.

6. When you're prompted, enter the password you just generated, and then click OK.

7. To accept the server certificate and log into your remote Windows instance, click Continue.

8. When you're prompted whether you want your computer discoverable by other PCs and devices on the network, click No.

Set up the additional disk

The second disk attached to the production instance is for storing your production database. This disk is blank, so you need to partition, format, and mount it.

1. In an RDP session connected to your sql-server-prod instance, click the Start button on the Windows taskbar, type diskpart, and then click diskpart to open DiskPart.
2. When you're prompted to let the app make changes, click Yes.

3. Display a lists of disks attached to your instance:

   list disk
   

   The output is the following:

   Disk ###  Status         Size     Free     Dyn  Gpt
   --------  -------------  -------  -------  ---  ---
   Disk 0    Online           50 GB      0 B
   Disk 1    Online          100 GB   100 GB
   

   Disk 1 (100 GB) is your data disk.

4. Select the data disk:

   select disk 1
   

5. Initialize the disk:

   clean
   

6. Create a GUID partition table:

   convert gpt
   

7. Create the data partition by using the entire disk:

   create partition primary
   

8. List the available volumes:

   list volume
   

   The output is the following:

   Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
   ----------  ---  -----------  -----  ----------  -------  ---------  --------
   Volume 0     C                NTFS   Partition     49 GB  Healthy    Boot
   Volume 1                      FAT32  Partition    100 MB  Healthy    System
   Volume 2                      RAW    Partition     99 GB  Healthy
   

   Volume 2 (99 GB) is your data disk.

9. Select the volume:

   select volume 2
   

10. Format the partition with the NTFS file system and label it data:

    format quick fs=ntfs label=data
    

11. Mount the disk as drive D:

    assign letter=d
    

12. Exit DiskPart:

    exit
    

Download the sample database

To set up your environment for this cloning exercise, you need to do the following:

• Create a directory structure on drive D (data) to store your database.
• Download the Wide World Importers SQL Server sample database full backup file. This database simulates the production database you want to clone.

To create the directory and download the backup file, follow these steps:

1. In your RDP session, click the Start button on the Windows taskbar, type PowerShell, and then select the Windows PowerShell app.

2. At the PowerShell prompt, create a directory structure for the database storage:

   mkdir D:\sql-server-data\wideworldimporters
   

3. Download the backup file to drive D:

   bitsadmin /transfer sampledb /dynamic /download /priority FOREGROUND `
     https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak `
     D:\sql-server-data\WideWorldImporters-Full.bak
   

Restore the sample database

You need to restore the sample database to drive D (data), either interactively by using the Microsoft SQL Server Management Studio (SSMS) wizards, or directly by running a Transact-SQL command.

To verify the sample database is functional, you can run a query.

1. In Microsoft SQL Server Management Studio, select File > New > Query with Current Connection to open a new query window, and then copy the following code:

   SELECT top(100)
     i.InvoiceDate, i.InvoiceID, i.CustomerID, c.CustomerName,
     i.ConfirmedDeliveryTime, i.ConfirmedReceivedBy
   FROM
     WideWorldImporters.Sales.Invoices i
     JOIN WideWorldImporters.Sales.Customers c
     ON i.CustomerID=c.CustomerID
   WHERE i.ConfirmedDeliveryTime IS NOT NULL
   ORDER BY i.InvoiceDate desc;
   

   This query retrieves summary information from the 100 most recently delivered invoices.

2. Right-click the query window and click Execute.

   The Results pane displays the summary information.

   

Create the test VM instance

In this section you create a SQL Server instance named sql-server-test as the destination for the cloned database. The configuration of this instance is identical to the production instance. However, you don't create a second data disk; instead, you attach the data disk later in this tutorial.

Connect to the VM instance

1. In the Google Cloud console, go to the VM instances page.

   Go to the VM instances page

2. Wait about 5 minutes for the VM instance to be ready.

   To monitor the initialization process of the VM, view its serial port output in Cloud Shell:

   gcloud compute instances tail-serial-port-output sql-server-prod
   

   When you see the following message, the initialization is complete.

   Instance setup finished. sql-server-test is ready to use.
   

   Press Control+C to stop monitoring the serial port.

3. Click the instance name sql-server-test to display the VM instance details page.

4. Under Remote access, click Set Windows password, and then click Set to create your account on the remote machine.

   This step generates a password for you. Make a note of the password or copy it to a secure, temporary file.

5. In the Compute Engine section of the Google Cloud console, click the RDP dropdown and select the Download the RDP file option to download the RDP file for your instance.

   Use this file to connect to the instance using an RDP client. For more information, see Microsoft Remote Desktop clients.

6. When you're prompted, enter the password you just generated, and then click OK.

7. To accept the server certificate and log into your remote Windows instance, click Continue.

8. When you're prompted whether you want your PC discoverable, click No.

Cloning the database using Compute Engine disk snapshots

One way to clone a SQL Server database running on Compute Engine is to store the database on a separate data disk and use persistent disk snapshots to create a clone of that disk.

Persistent disk snapshots let you get a point-in-time copy of on-disk data. Scheduling disk snapshots is one way to automatically back up your data.

In this section of the tutorial, you do the following:

• Take a snapshot of the production server's data disk.
• Create a new disk from the snapshot.
• Mount the new disk onto the test server.
• Attach the database on this disk to SQL Server on the test instance.

The following diagram shows how a database is cloned by using disk snapshots.

Create the disk snapshot

Attach the disk snapshot to the test instance

You need to create a new data disk from the snapshot you created and then attach it to the sql-server-test instance.

Mount the new data disk in Windows

The disk you created is attached to the VM instance but is offline, and the volume is set to read-only. To configure the volume as read-write and mountable, perform the following steps:

1. In the RDP client window that's connected to your sql-server-test instance, click the Start button on the Windows taskbar, type diskpart, and then click diskpart to open DiskPart.
2. When you're prompted to let the app make changes, click Yes.

3. Display a list of the disks attached to your instance:

   list disk
   

   The output is the following:

   
   Disk ###  Status         Size     Free     Dyn  Gpt
   --------  -------------  -------  -------  ---  ---
   Disk 0    Online           50 GB      0 B
   Disk 1    Offline         100 GB      0 B       *
    

   Your data disk (Disk 1, 100 GB) is offline.

4. Select the data disk:

   select disk 1
   

5. Bring the disk online:

   online disk
   

6. List the available volumes:

   list volume
   

   The output is the following:

   Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
   ----------  ---  -----------  -----  ----------  -------  ---------  --------
   Volume 0     C                NTFS   Partition     49 GB  Healthy    Boot
   Volume 1                      FAT32  Partition    100 MB  Healthy    System
   Volume 2                      RAW    Partition     99 GB  Healthy
   

   Volume 2 (99 GB) is your data volume. It's listed as Hidden with no drive letter assigned.

7. Select the volume:

   select volume 2
   

8. Clear the attributes that were set when you created the volume from the snapshot:

   attr volume clear readonly hidden nodefaultdriveletter shadowcopy
   

   This command makes the volume mountable.

9. Mount the volume as drive D:

   assign letter=d
   

10. Exit DiskPart:

    exit
    

Reset file ownership and permissions

Because you created the data disk by taking a snapshot on the sql-server-prod instance, the user IDs for file ownership and permissions are different than those on the sql-server-test instance. You need to change the ownership of the files to a user on the sql-server-test instance and update the permissions to make the files readable for your local user and the MSSQLSERVER user.

1. In the RDP client window connected to your sql-server-test instance, click the Start button on the Windows taskbar, and then type cmd.
2. Open the Command Prompt app as an administrator.

3. Set the owner of the files in the sql-server-data folder to the MSSQLSERVER service user:

   icacls d:\sql-server-data /setowner "nt service\mssqlserver" /t
   

4. Reset all permissions on all files in the top-level sql-server-data folder:

   icacls d:\sql-server-data /reset /t
   

5. Update the access control lists (ACLs) for the sql-server-data folder:

   icacls d:\sql-server-data /grant Administrators:(oi)(ci)f "nt service\mssqlserver":(oi)(ci)f "owner rights":(oi)(ci)f  %USERNAME%:(oi)(ci)f
   

6. Disable the inheritance from the root folder of the drive, so that only the preceding permissions are applied:

   icacls d:\sql-server-data /inheritancelevel:r
   

7. Exit the Command Prompt app:

   exit
   

The SQL Server instance and the local user on the sql-server-test instance can now access the database files.

Attach the cloned database

You now attach the cloned database on drive D (data) to the test SQL Server instance. You can attach the database either interactively by using the Microsoft SQL Server Management Studio wizards, or directly by running a Transact-SQL command.

To verify that the sample database is functional, you can run a query.

1. In SQL Server Management Studio, select File > New > Query with Current Connection to open a new query window, and then copy the following code:

   SELECT top(100)
     i.InvoiceDate, i.InvoiceID, i.CustomerID, c.CustomerName,
     i.ConfirmedDeliveryTime, i.ConfirmedReceivedBy
   FROM
     WideWorldImporters.Sales.Invoices i
     JOIN WideWorldImporters.Sales.Customers c
     ON i.CustomerID=c.CustomerID
   WHERE i.ConfirmedDeliveryTime IS NOT NULL
   ORDER BY i.InvoiceDate desc;
   

   This query retrieves summary information from the 100 most recently delivered invoices.

2. To run the query, right-click the query window, and then select Execute.

   The Results pane displays the summary information.

   

Now that you have seen how to clone a database using persistent disk snapshots, you might want to try cloning a database by using backup and restore. To complete the tutorial for this second approach, you must delete the database that you cloned from the sql-server-test instance.

Delete the cloned database

To delete the cloned database you created by using disk snapshots, perform the following steps.

Cloning using backup and restore

A second method of cloning a SQL Server database running on Compute Engine is to use native SQL Server backup and restore. With this approach, you transfer the backup by using Cloud Storage.

This section of the tutorial uses resources that you created in the Cloning the database using Compute Engine disk snapshots section of this tutorial. If you didn't complete that section, you must do so before continuing.

In this section of the tutorial, you do the following:

• Create a Cloud Storage bucket.
• Back up the database on the production server.
• Copy the backup file from the production server to Cloud Storage.
• Copy the backup file from Cloud Storage to the test server.
• Restore the backup on the test instance.

The following diagram shows how a database is cloned by transferring a backup using Cloud Storage.

Because systems outside of Google Cloud can be given access to Cloud Storage, you can use this approach to clone databases from external SQL Server instances.

Create a Cloud Storage bucket

You need to create a Cloud Storage bucket that stores the backup files while you transfer them from the sql-server-prod instance to the sql-server-test instance.

Make a full point-in-time backup of the database

In your production environment, you might already make backups. You can use these backups as a base for cloning your database. In this tutorial, you make a copy-only backup so that it doesn't impact any existing full or incremental backup schedules.

Copy the backup file to Cloud Storage

1. In the RDP session connected to your sql-server-prod instance, open a Windows PowerShell window.

2. Copy the backup file into the Cloud Storage bucket you created earlier:

   gcloud storage cp d:\sql-server-data\WideWorldImporters-copy.bak "gs://$(gcloud config get-value project)-bucket/" --no-clobber
   

3. Exit PowerShell.

   exit
   

Copy the backup file from Cloud Storage to sql-server-test

1. In the RDP session connected to your sql-server-test instance, open a Windows PowerShell window.

2. Copy the backup file into the Cloud Storage bucket you created earlier:

   gcloud storage cp "gs://$(gcloud config get-value project)-bucket/WideWorldImporters-copy.bak" d:\sql-server-data\
   

3. Exit PowerShell.

   exit
   

Your test instance now has the full-copy backup of your database on its local disk.

Restore the backup

You can now restore the full-copy backup onto drive D (data) of the sql-server-test instance.

To demonstrate that the database is functional, you can run a query.

1. In Microsoft SQL Management Studio, select File > New > Query with Current Connection to open a new query window, and then copy the following code:

   SELECT top(100)
     i.InvoiceDate, i.InvoiceID, i.CustomerID, c.CustomerName,
     i.ConfirmedDeliveryTime, i.ConfirmedReceivedBy
   FROM
     WideWorldImporters.Sales.Invoices i
     JOIN WideWorldImporters.Sales.Customers c
     ON i.CustomerID=c.CustomerID
   WHERE i.ConfirmedDeliveryTime IS NOT NULL
   ORDER BY i.InvoiceDate desc;
   

   This query retrieves summary information from the 100 most recently delivered invoices.

2. Right-click on the query window and click Execute.

   The Results pane displays the summary information.

   

Using Cloud SQL as the cloning destination

If your destination database is hosted on Cloud SQL, and the origin database is on Compute Engine, then the only supported mechanism for cloning is by backing up the database to Cloud Storage, and then restoring the database into Cloud SQL.

For this tutorial, you reuse the backup that you created during the previous section.

Create a Cloud SQL for SQL Server instance

1. Open Cloud Shell.

   Open Cloud Shell

2. Create a Cloud SQL for SQL Server instance running the same database version as your sql-server-prod instance:

   gcloud sql instances create sqlserver-cloudsql  \
     --database-version=SQLSERVER_2022_STANDARD \
     --cpu=2 \
     --memory=5GB \
     --root-password=sqlserver12@ \
     --region=${REGION}
   

   This creates an instance with root user of sqlserver with a password of sqlserver12@.

Update object permissions

The correct permissions need to be set on both the Cloud Storage bucket and the backup object so that the Cloud SQL service account is able to read them. These permissions are set automatically when you use the Google Cloud console to import the object, or you can set them using gcloud commands.

1. Open Cloud Shell.

   Open Cloud Shell

2. Set an environment variable containing the address of the service account of your Cloud SQL instance:

   CLOUDSQL_SA="$(gcloud sql instances describe sqlserver-cloudsql --format='get(serviceAccountEmailAddress)')"
   

3. Add the service account to the bucket IAM policy as a reader and writer:

   gcloud storage buckets add-iam-policy-binding "gs://$(gcloud config get-value project)-bucket/" \
       --member=user:"${CLOUDSQL_SA}" --role=roles/storage.objectUser
   

Import the exported database

1. Open Cloud Shell.

   Open Cloud Shell

2. Import the exported file into your Cloud SQL instance:

   gcloud sql import bak sqlserver-cloudsql \
       "gs://$(gcloud config get-value project)-bucket/WideWorldImporters-copy.bak" \
       --database WideWorldImporters
   

   When prompted, enter y.

3. Install the SQL Server tools package:

   sudo apt install -y mssql-tools
   

   If you accept the licence terms, enter yes when prompted.

   You use these tools to connect to Cloud SQL from Cloud Shell so that you can run queries on the Cloud SQL instance.

4. Connect the Cloud SQL proxy to your SQL Server instance:

   CONNECTION_NAME=$(gcloud sql instances describe sqlserver-cloudsql --format='value(connectionName)')
   cloud_sql_proxy -instances=${CONNECTION_NAME}=tcp:1433 &
   

5. To verify that the cloned database is functional, run a query:

   /opt/mssql-tools/bin/sqlcmd -U sqlserver -S 127.0.0.1 -Q \
     'SELECT top(100)
       i.InvoiceDate, i.InvoiceID, i.CustomerID, LEFT(c.CustomerName,20) CustomerName,
       i.ConfirmedDeliveryTime, LEFT(i.ConfirmedReceivedBy,20) ConfirmedReceivedBy
     FROM
       WideWorldImporters.Sales.Invoices i
       JOIN WideWorldImporters.Sales.Customers c
       ON i.CustomerID=c.CustomerID
     WHERE i.ConfirmedDeliveryTime IS NOT NULL
     ORDER BY i.InvoiceDate desc;'
   

   When prompted, enter the sqlserver user's password of the sqlserver-cloudsql database server (sqlserver12@).

   This query retrieves summary information from the 100 most recently delivered invoices.

   The output is the following:

   InvoiceDate      InvoiceID   CustomerID  CustomerName         ConfirmedDeliveryTime                  ConfirmedReceivedBy
   ---------------- ----------- ----------- -------------------- -------------------------------------- --------------------
         2016-05-30       70349         581 Wingtip Toys (Munich            2016-05-31 07:05:00.0000000 Youssef Eriksson
         2016-05-30       70350         123 Tailspin Toys (Roe P            2016-05-31 07:10:00.0000000 Ella Zvirbule
         2016-05-30       70351         175 Tailspin Toys (San A            2016-05-31 07:15:00.0000000 Julio Correa
         2016-05-30       70352        1029 Veronika Necesana               2016-05-31 07:20:00.0000000 Veronika Necesana
         2016-05-30       70353        1014 Narendra Tickoo                 2016-05-31 07:25:00.0000000 Narendra Tickoo
         2016-05-30       70354         930 Shantanu Huq                    2016-05-31 07:30:00.0000000 Shantanu Huq
         2016-05-30       70355         963 Be Trang                        2016-05-31 07:35:00.0000000 Be Trang
         2016-05-30       70356         567 Wingtip Toys (Jerome            2016-05-31 07:40:00.0000000 Severins Polis
         2016-05-30       70357         510 Wingtip Toys (Grabil            2016-05-31 07:45:00.0000000 Manish Ghosh
   ...