This page provides the steps to set up and configure backup images of Microsoft SQL Server databases. This includes discovery of a SQL Server, protection of the databases in the instance, and mounting a backup to a target server.
Before you begin
The sections in this page must be completed in sequential order. This page assumes the following details about your project:
There will be either one or two VMs.
Source—production—VM hosting source databases.
Target—recovery, test, DR, or non-production—VM to mount virtual copies of the databases.
Backup and DR has already been deployed with the backup/dr appliance installed and the network firewall is configured as directed to allow the required communications between appliance and the SQL Server VMs.
Regardless of whether you deploy new VMs or use existing ones, the following tasks must be performed in sequential order:
The source and target VMs require the Backup and DR agent setup.
The Windows server requires the iSCSI service to be set to started and set to automatic start.
Microsoft SQL Server should be installed.
You can add additional drives to your SQL servers if this is a normal operating procedure, and if you run databases and logs on alternate disk drives.
Create a backup plan
Go to the management console to create a policy template that determines when backup images run, how long they are retained, and where they are stored. Several details related to how the backup images are performed are also specified.
In the management console, go to Backup Plans drop-down menu and click Templates.
Click Create Template.
Enter a name and description for your new template, then click the plus sign (+) below the icon that represents production data.
Enter the desired policy name, daily window, and retention for the snapshots of your databases. This retention is typically short and it is stored in the snapshot pool on Persistent Disk storage to ensure recovery performance.
Click Advanced Policy Settings.
There are several options in the advanced policy settings, most of which should be left at the default values. For a SQL server, the most commonly changed settings, set the following values for best practices:
Truncate/purge log after backup. Set this to Truncate.
Enable database log backup. Set this to Yes.
Log backup retention. Set this to 7 days.
Replicate logs. Set this to No.
Send logs to OnVault pool. Set this to Yes.
Click Save changes. After making the desired selections, click Update policy.
The snapshot icon appears colored.
Click the plus sign (+) between the snapshot and OnVault icons to add an OnVault policy.
Enter the desired policy name, daily window, and retention for the OnVault copies of your database backup images. The start time of the window should match the time specified in the snapshot policy. This retention is typically longer, ranging from 30 to 90 days, and the copies are stored in Cloud Storage to ensure the right balance of performance and cost. Click Update policy when done.
Click Save template to save your policies and settings.
When you deploy the management console and appliance, an OnVault pool and a matching resource profile are created automatically. This resource profile is used when protecting the databases.
Add the databases to the management console and protect them as needed. Continue to Add a Microsoft SQL server host and its databases.
Set up the source Windows Microsoft SQL server VM
Log into the source VM.
If this is a Compute Engine instance, you need to ensure the iSCSI initiator is started and also auto-start is placed on reboot. To do this, launch the iSCSI initiator application using the steps below.
Click the Start menu.
Type in iSCSI initiator to find the application.
Launch the process.
If you are prompted, click Yes to enable iSCSI and have it set to auto-start upon boot. Once this is complete, the iSCSI Initiator page appears. Click Ok to close.
Open a browser and download the Windows Backup and DR agent from the management console. To download the Backup and DR agent, complete the following steps:
From the management console click the Manage drop-down menu and click Applications.
Right-click and select the appliance to perform the backup images and select Configure appliance.
From the Appliance configuration page, go to Agent management and download the relevant agent. You may need to do this on a different host and then transfer the EXE to the relevant host.
Click on the Windows Backup and DR agent EXE, and select Run.
Select Yes to begin the installation.
Select the option to install the Backup and DR agent with change tracking driver. This installs an additional Windows service that tracks changes for databases larger than 1GB in size.
Click Next and agree to install the Backup and DR agent in the default location.
Click Install to let the installation begin. When done, click Finish.
Open the Services control panel and confirm there are two Backup and DR services running. If not, then reinstall the Backup and DR agent and enable Change block tracking.
Open a Windows command prompt and change the directory to
C:\Program Files\Google Cloud Backup and DR>.
Run the following command:
udsagent.exe secret --reset
Optional: If you want transaction log backup, then you may need to change the Recovery model of your databases to Full. The default is Simple, which means that Backup and DR Log backup images never find any logs to backup. This may be the desired setting for your configuration, in which case do not change it. Log into the Microsoft SQL Server Management Studio, then right-click the database and select Properties. Go to Options and change the Recovery model from Simple to Full.
If you intend on backing up an SQL databases with transaction logs—Full recovery model—or if you wish to avoid the need to enter credentials for an SQL server into the management console interface when performing recoveries, there are two options available to achieve this.
Use one of these two approaches for the most seamless experience:
Option one: Use the
Go to the SQL Instance Security Login menu. Right-click on the
NT AUTHORITY\SYSTEMaccount and select properties. Then in Server roles, select the sysadmin role, and click OK. This allows the Backup and DR agent the elevated rights to run log backup images and log recoveries without requiring additional user authentication.
Option two: Create a new Windows Administrator account—domain or local OS:
Verify or grant the account permissions as follows:
Allow the following rights in SQL:
db_backup operator databaserole,
Assign the following securables in SQL: View any database, Create any database, Alter any database, Connect SQL
Next, the account should be in the local Windows administrator group, to ensure access to scripts in
C:\Program Files\Google Cloud Backup and DR>and to make OS calls and handle the staging disks where backup images are stored.
Finally, update the Google Cloud Backup and DR agent service to run as this account and restart the service.
Add a Microsoft SQL server host and its databases to management console
To protect an SQL server database, you must first add its host to the management console and then discover the database.
From the Backup & restore menu, select an SQL server.
At Manage hosts, you're either shown a list of existing hosts or a message that no hosts were found. Click + Add host to open the add host dialog.
Enter the name, IP address and click the plus sign (+) of your SQL Server VM.
Select your backup/recovery appliance and click Add.
In Backup and DR agent settings complete the following:
Backup and DR agent port is normally port 5106. If it is anything else, enter that port here.
Add credentials for the host only if you are backing up SMB shares.
You need to supply a secret key generated on Windows and Linux host servers by the Backup and DR agent, to initialize trusted communications between the host and Backup and DR agent. If you don't have a secret key that was generated in the last two hours, reset the key as per the Set up the source Windows Microsoft SQL Server VM and paste the resulting key into this field. This only needs to be done once. You can use the secret key that is generated while Set up the source Windows Microsoft SQL Server VM.
In the management console, go to Backup & Recover, then Backup and choose Microsoft SQL Server. Select your newly added server and click Next to discover the SQL Instances on the server. This process can take a minute to complete. If it fails, then you may need to verify the correct firewall policy is in place, to allow the backup/recovery appliance access to the SQL server via TCP port 5106. Use the instructions to Validate backup/recovery appliance to Backup and DR agent connectivity.
The SQL server instances on the VM is displayed.
Backup your application
In this step, you apply the backup plan policy that you created in Configure a backup policy in the management console to a database discovered in Add a Microsoft SQL server host and its databases to the management console.
According to the schedule in the policy, a backup job runs. It takes a snapshot of the database according to the snapshot policy and then copies it to the OnVault pool for longer storage according to the OnVault policy.
Select the SQL server instance that has the databases you wish to backup and click Next.
To apply a backup plan, select the instance, then choose Apply backup plan from the dropdown menu above the instance list. As you select options, more drop-down menus appears. Select the Template and Profile that were previously created, then click OK.
If you wish to select individual databases to include or exclude from backup images, you can click the All databases link to bring up the manage membership dialog.
You may choose an inclusion rule to suit your needs for inclusion or exclusion of databases for backup images. Auto-inclusion of new databases is controlled by this selection.
Include all, Include user, and Exclude selected result in new databases added to this instance to be backed up automatically.
Include user and Include selected result in a static list of databases to backup, and new databases are not backed up without manual intervention.
The recommended selection is Include all.
After optionally changing the database inclusion rule and saving your changes, click Next. You now have the option to modify application settings that determine an SQL server specific behaviors during backup images. Keep the defaults and select Next.
From the summary display, click Finish to initiate the discovery and protection of your SQL server databases. You are presented with a confirmation page reminding you to keep your browser window open, without refreshing, until the discovery is complete. Click Finish again.
Proceed after you see the green checkbox.
After the backup plan is applied to the SQL instance, backup images begin automatically, based on the schedule within the Template policy.
The database should now be backed up successfully. After a few minutes of initiating the backup snapshot, return to the App manager, Applications menu.
Recover the Microsoft SQL instance or database images
In the management console, navigate to the App Manager drop-down menu and click Applications.
Find your backed up SQL instance or databases, right-click and select Access.
Depending on the backup plan configured, You should be able to see captured images in the timeline ramp or table view. You may have images in the snapshot lane and additionally in the OnVault lane. Next, select one of the images and click Mount.
The Mount screen is where you select the target host you want to mount the databases to. This can be back to the source host (choose a new name for the DB), or a different target host. You can also optionally enter a label for the mount, which may be helpful when referring to the mount job later. You also must select the databases to be mounted in the Select items area.
Next, provide a name for the database you are mounting. The User Name and Password fields can be left blank if the Backup and DR UDS Host Agent service has the permissions specified in the section Set up the source Windows Microsoft SQL server VM above. Otherwise, you need to enter credentials with those permissions on this screen. Lastly, click Submit to run the job. The mount job may take 1-5 minutes, depending on the environment. To monitor the job, go to the Monitor drop-down menu in the management console and click Jobs.
If all has succeeded, you can see your mount task using the management console using the App Manager drop-down menu and clicking Active Mounts.
Go back to the SQL Instance, and refresh the instance to see your newly mounted database.
Verify the database is available. Select New query and paste the following information. Use the name of the mounted database where DevCRM is below:
USE [DevCRM] SELECT * from [Customers];
Mount and migrate your database
Mount and migrate allows you to get access to a database quickly via the mount operation as above, and then restore the database while it is running, with a small interruption at a later time to switch from the mounted disks to the server's attached disks.
In the management console, navigate to App Manager drop-down menu and click Active Mounts.
Right-click on the database and select Migrate.
Select the Frequency which is the duration in hours between incremental refreshes of the mounted database to the server's attached disks. This process repeats until the final switchover is done. Additionally you can configure the copy thread count—between 1-20— and optionally select the new file and folder location for the restored copy. Click Submit to start the process.
During the migrate job, the database still runs as a mounted database hosted from the snapshot pool in the Data mover backup/recovery appliance. When the first migrate job is complete, a locally restored copy of the SQL database and log files are present on the target SQL server.
Finalize the migration. This takes the database offline, performs a final sync of the current data to the server's attached disks, switches SQL server to reference the restored database files, and then brings the database back online. For many large databases, this process can run less than five minutes depending on the rate of change. From the App Manager drop-down menu in the management console, click Active Mounts and select the database you want to finalize the migration for. Then, select Finalize migration.
Confirm the finalize task, and click the Proceed option.
Finally, Backup and DR also cleans up the previous mounted disks as part of the finalize task. You do not need to perform clean-up tasks manually.
Unmount and delete a mounted database image
Use these instructions to unmount a mounted database:
In the management console, go to the App Manager drop-down menu and click Active Mounts. Select the mount you want to remove from the SQL instance, right-click on the application, and select Unmount & delete.
From this menu below, click Submit to remove the database from the running SQL instance.
If your account does not have enough privileges with SQL, then this job may fail. If that occurs, you need to use SQL Studio to manually delete the database from SQL server. Once that has been successfully completed, you can re-try the Unmount-Delete and select the Force unmount option. This results in the Backup and DR presented disks being removed from the server without warning SQL server first.
After a short period of time, you can check the SQL instance, and the database should be removed.
Go to the Backup plans overview page to learn about how to set up a backup plan.