Last week a good friend of mine, who’s a Veeam customer, was telling me over coffee about the problems he faces when they have to backup a physical Microsoft cluster for SQL.
The issues he’s up against? The high cost, the time it takes and most importantly, keeping his sanity intact. Unfortunately, at present, he can’t use Veeam to backup the physical server as it a physical server and also a cluster.
Apparently, his management team is struggling to reduce their budget costs as well as increase productivity and availability of their SQL applications. All to meet the Data/application compliance they just introduced to their company.
These managers also told him they needed to backup the SQL databases from their physical server to our new acquired deduplication appliance using one backup software.
That’s quite a challenge. So after two days looking into this, I came back to him with a great workaround:
Workaround requirements:
- a virtual machine.
- Installing a new SQL instance on the new created VM.
- Copy the Production SQL DB’s to the new created VM SQL.
- Mirror the SQL DB to the new created VM SQL instance.
- Use Veeam backup to backup the VM.
Workaround deployment Steps:
After provisioning a new VM and installing the SQL server and before setting up the SQL mirror, the production SQL DB’s must be copied to the new created VM SQL server.
The easy way is to use SQL built-in DB backup feature, from the production SQL (Cluster), run the below commands to backup the desired database to a share location where the VM SQL will read and restore the database from:
BACKUP DATABASE VeeamDB TO DISK = ‘Z:\Backup.bak’;
BACKUP LOG VeeamDB TO DISK = ‘Z:\Backup.trn’;
Go
Restore the DB on the new VM SQL Server using the below commands:
RESTORE DATABASE VeeamTestDB FROM DISK = ‘Z:\Backup.bak’ WITH MOVE ‘VeeamTestDB’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\VeeamTestDB.mdf’, MOVE ‘VeeamTestDB_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\VeeamTestDB.ldf’, NORECOVERY, NOUNLOAD, STATS = 5; GO
Create an SQL mirror to go between the cluster and the new VM SQL:
At the production DB server, right click on the DB and select Tasks – Mirror:
- Select Mirror – Configure Security:
- No Witness server needed
- Chose the source and target SQL Servers.
- Enter the username and password of the service account
- Finish
Backup the new SQL VM server with Veeam and modify the job config as below:
- Create Veeam backup Job and select the new SQL VM server.
- Guest Processing -> Applications ->Edit -> Transaction Logs -> select: Perform copy only.
For Restore:
- Browse the desired backup point and open with Veeam File Explorer.
- Restore the Database to the desired SQL server.
- Use the attach DB option to attach the restored DB.
Things to remember:
- It’s a workaround solution.
- If you have many DB’s the process probably will be a time consuming (initial setup).
- Additional SQL license is required.
- Restore process requires to “Attach the DB” manually to the SQL server.
The result:
With this workaround, and after the initial setup, my friend was able to address fully the backup requirements introduced by his company. And with Veeam controlling the backup, they also get the benefit from one product to backup the entire infrastructure. Not to mention reduce their management and licensing costs.
Leaving a good backup, a happy management team, and one relieved friend.
If you’re facing this type of backup issue, a workaround solution like the one above is your best bet.
See you in the next post.
