Where do you store your Microsoft SQL data? traditionally SQL requires local, SAN, DAS storage to operate however extending SQL databases to low-cost object storage is easily achievable through LucidLink.
High transactional databases will most certainly remain where they are, or be best served through all-flash arrays, although capacity dependent databases and less frequently accessed databases consuming premium storage might be better served off a more price efficient storage technology - certainly backups, in any case, shouldn't consume valuable production resources.
Choose the storage technology that best suits and meets the IO expectations of any given workload.
In our case within this article, we will discuss implementing a disaster recovery and database backup scenario with an ability to simulate DR recovery periodically to ensure data integrity and recoverability.
In our environment we have 2x SQL servers (SQL + TEST-WIN1), geographically separated. SQL is our production server running in Azure and TEST-WIN1 is our bare metal disaster recovery simulation server.
Each server is connected to a LucidLink Filespace, if you have not created a Filespace yet, you can easily Sign-up and try our service, along with following our Getting Started Guide to initialize your Filespace within your storage account.
Both SQL and TEST-WIN1 servers are able to access data from their independent Filespace mount-points. Securely maintaining database backups in low-cost object storage, and access to data anywhere, anytime, recovering promptly from virtually any disaster. Let's get started:
1. On any required servers install LucidLink as a service
lucid service --install lucid service --start
2. Link and mount our Filespace (we've used a specific Filespace User ACL called "SQL" which has a data Share to only what data this user requires access). We will also use S: drive within both our servers' operating systems as our universal mount-point.
lucid link --fs <filespace.domain> --user <filespaceuser> --mount-point <driveletter>
3. As simple as that! Both our servers now have an S: backed by Azure Block Blob object storage.
4. Next we setup a SQL backup of our existing "Contacts" database to leverage this location for our backup data.
Microsoft SQL maintenance consists of regularly performing database consistency checks, compaction and secure backups to ensure your database is optimized and protected. Backing up the database and transaction log files is critical to make certain you can recover from unexpected or malicious data corruption or loss.
Through SQL's in-built Maintenance Plan Wizard you can create a history of full, or differential backups of your databases with retentions for a specified period.
a. Right-click Maintenance Plan in Management and select Maintenance Plan Wizard
b. Provide the desired plan name and select Change to specify a schedule
c. Define a suitable plan schedule. In our case, weekdays at midnight
d. We are going to perform a full database backup for our plan task
e. Selecting only our Contacts database
f. and define our destination, our Filespace mount-point S:\SQL
g. we will also write our backup report to our Filespace on S:\SQL
h. our maintenance plan to backup our Contacts database has been successfully created
Additionally, you can create a Maintenance Cleanup task that removes files leftover from executing a maintenance plan ie. maintain only 4 weeks of our daily Contacts database backups on S:\SQL
6. Lets execute the plan we just created to produce a current backup and test our plan is functioning as expected. Right-click Backup Contacts and select Execute
7. You will note our plan will execute successfully by creating a backup file of our database along with a report outputted to our Filespace mount-point within S:\SQL
Mount your backups remotely to periodically test your disaster recovery strategy with just a few clicks. Let's use our TEST-WIN1 server to restore our Contacts database from our Filespace mount-point.
1. Right-click databases and select Restore Database
2. Select device, and add
3. Browse through to our S:\SQL Filespace mount-point. Choose the backup to recover and press Ok
4. We are now ready to perform our recovery. before we do, lets change a couple more settings
5. Within the restore database wizard, select files. let's specify a new location for our database files6. We will restore our Contacts.mdf and Contacts.ldf to our Filespace mount-point S:\SQL
7. Select Ok to restore our database. remember of course that our TEST-WIN1 server is a geographically separate bare metal server. Our production server is running in the Azure cloud.
8. Our database Contacts is successfully restored on our TEST-WIN1 server to our Filespace mount-point S:\SQL.
Leveraging LucidLink for not only the database backup location, our recovery location and database operating environment.
9. Success! We can now run a query within our disaster recovery server to interrogate our data and guaranty the data integrity, using our Filespace as our data location for the recovery of our production backups.
Should the database performance behave as expected, why not consider leaving it there, or transitioning it and other resources to low-cost object storage for production workloads.
As you can see Filespaces integrate as an extension of the local Windows filesystem and become an obvious choice for a convenient, offsite, data backup location, secured in the storage provider of your choice.
Workloads can be accessed and distributed across virtually any operating environment.