Category Archives: SQLTips

SQL Server Managed Backup to Microsoft (Windows) Azure | Part-2

In the last post we discussed about the features of SQL Server Managed Backup to Windows Azure, how it is different from conventional backup schedules, how the backup and container gets named and based on what conditions SQL Server will initiate a Full\Transactional Log backup or skip it.

 

What are the benefits of using this technology:

1. If you want to automate backups without using this feature you need to develop a backup strategy, you may also have to write your own code and schedule the jobs etc. But by using this feature all you need to do is provide the retention period & storage location that’s it and SQL Server Managed backup to windows azure will schedule, perform and maintain the backups for you.

2. This feature can be configured at DB level or at the instance level.

a. If you enable this at the instance level this feature will be automatically applied to all DBs created thereafter that means you don’t have to worry about the newly created DBs.

b. If you enable this at the DB level and specify the retention period this will override the default settings set at the instance level; means more granular control.

3. You don’t even have to worry about the type of backup and frequency, all of these things gets determined automatically by SQL Server Managed Backup to Azure based on the retention period you specify and the workload on the database.

4. If you configure to use Encryption it gives you additional Security. Backup Encryption is new feature added in SQL 2014 and you can also use it in Native backups on disk\tape and you can take backup with Encryption using T-SQL or GUI both.

 

Things you must know about this feature:

1. A full backup and a transaction log backup can run concurrently, however if there are more than 1 Full DB backup jobs running for the same database it will wait for the current job to complete before initiating another backup; the same applies for transaction log backup also.

2. If more than 10 concurrent full DB backups are scheduled, it will issue a warning through debug channel of Extended Events; then maintains a priority queue for the remaining DBS that require a backup until all the backups are scheduled and completed.

3. Failures are logged as Extended Events.

 

Limitations of Managed Backup in SQL 2014

Don’t you think features discussed till now are too good to be true, yes just like any other technology there are some limitations for SQL Server Managed Backup to Windows Azure, these are:

1. Only Full and Transaction Log backup is possible through Managed Backup.

2. System Databases are not supported.

3. Only full & Bulk-Logged recovery models are supported so the recovery model of the DB should be one of them.

4. File backup automation is not supported as of now.

5. SQL Server Managed backup to Azure is currently supported only by T-SQL; you can monitor and troubleshoot by using Extended Events though. If you are a PowerShell & SMO freak then watch out, because PS & SMO support is limited to Configuring storage and retention period default settings for an instance of SQL Server, and monitoring the backup status and overall health based on SQL Server Policy Based Management policies.

6. Windows Azure Blog Storage service is the only supported backup storage option. Backup to disk and tape are not supported.

7. Currently the maximum file size allowed for a page blog in windows azure storage is 1 TB that means you can’t backup the DB if the backup size goes beyond 1 TB & it will fail. To avoid such scenarios you can use Compression and test the size before configuring the backup & keep a check on the backup size.

8. It also may have some limitations when it is configured with other technologies supporting backups, HA & DR.

 

Thanks,
Sarabpreet Singh

SQL Server Managed Backup to Microsoft (Windows) Azure | Part-1

SQL Server Managed backup is a new feature introduced in SQL Server 2014, this feature is simply outstanding – you don’t have to worry about the backup strategy anymore, this feature manages and automates the backup process.

This feature uses sort of artificial intelligence – if you see at current scheduling features they just keep on taking backups at a specified time interval whereas, in this feature it checks couple of things and based on the situation it decides whether a backup needs to be initiated for a Database or not. This Feature is based on Retention period selected for backups and transaction workload on the database and it also provides Point-in-time recovery. You want to know how? Keep reading… you’ll get your answers soon.

You can enable this feature at two levels Instance as well as Database level. It doesn’t really matter if your SQL Server is running On-Premise or on Cloud. SQL Server Managed Backup to Microsoft (Windows) Azure is in-fact recommended for SQL Server running on a VM in Cloud.

The Retention period is a value which determines what backup files to keep and what needs to be deleted in the Azure Storage so that the underlying DB can be recovered to a point in time within the time frame specified. The valid retention period values are from 1 till 30 days.  The backup_finish_date of the backup is used to determine and match the time specified by the retention period settings.

Naming convention it follows for the backup and container

The backup files are named using a combination of first 40 characters of the database name, the GUID of DB without “-“& the timestamp; all these values are separated using Underscore to make it readable. The .bak and .log extensions are used for Full and Log Backups respectively. In case of Availability Group databases the Availability group database GUID is added after the first 40 characters of the DB name, you can get this GUID value from group_database_id column of Sys.databases.

The same way Backup container of (Windows) Microsoft Azure also get named using the SQL Instance name and in the case of Availability databases it gets named using GUID of Availability group.

 

Conditions that trigger a backup

As we discussed earlier also SQL Server managed backup doesn’t initiate a backup merely on the basis of schedule which has been fixed but takes a calculative decision whether a backup is really required for the given DB or not. It decides based on the below mentioned conditions:

A. Full backup gets triggered:

1. At the time when Managed Backup is configured on DB or when it is configured on the complete instance with default settings.

2. If the log generation since last full backup is greater than or equal to 1 GB in size.

3. If maximum time interval of one week has passed since the last full backup. (it tries to take at least one full back up every week)

4. When the log chain is broken; it checks periodically whether the log chain is intact or not by comparing first and last LSN of backup files, if it finds there is a gap it initiates a full DB Backup. There could be n number of reasons for log chain breaks, most common would be:
a. Switching recovery model to simple.
b. Taking back up through any other job\manual process.
c. Deleting some backup files
d. Accidental overwrites of backup files.
e. Truncating the log.

B. Transaction Log backup gets triggered:

1. When the Transaction Log space used is equal to or greater than 5 MB.

2. The maximum time interval of 2 hours has passed since the last log backup.

3. If no log backup history is found; for example when you enable Managed backup for the first time for the respective DB.

4. Anytime the TLog backup is lagging behind the full backup, it always tries to keep the log chain ahead of the Full backup. In simple words after every full backup it will initiate one TLog backup.

 

Thanks,
Sarabpreet Singh Anand

SQL Tip-12 | Using SQL Server Backup to Windows Azure Tool – Part2

In last part we discussed the installation, configuration and usage of the SQL Server Backup to windows azure tool.

In this part we’ll discuss some internals of this tool and how to restore the backups which were moved to Azure storage.

As we discussed in the last part this tool creates a login ID with the name of SQLBackup2Azure , let’s diagnose it further. The tool creates this login with a low privilege account which is used to run this service. This tool also creates a group called “TempGroup” and the login SQLBackup2Azure gets the membership of this group. The name of the group and login are hardcoded and can’t be changed. However the password of the login gets generated automatically and randomly which is not known to anyone including Microsoft.

When you start adding different rules in this tool, this group get read\write access on the specified location – that is how this tool is able to take backup and restore.

The biggest disadvantage of this tool is it doesn’t provide a mechanism to track the uploaded files and any kind of error logging information, you don’t even have any control over the schedule of the uploading of backups since there is a service it keeps on checking the files.

 

Restore a Database from the Backup file which was uploaded using this tool

This tool creates a Stub file with some metadata to use during restore. You can use this tool just like a regular backup file when you want to restore a database. SQL Server automatically reads the metadata information in the file and restore the database by getting the required file from the Azure Account. Internet connectivity is a must since the stub file contains only the metadata information about the backup file rest everything is there in the Azure Storage account.

 

Stub File

This tool uploads the specified backups from the file path mentioned in the rule but once the backup file gets uploaded it creates a Stub file for the same and leaves it in the local storage folder with metadata information to be used during restore.

 

What if Stub file is inaccessible or Deleted by mistake?

A copy of the same Stub file gets uploaded along with the backup on your Azure Storage Account. If for any reason the stub file is inaccessible you can download the copy of the stub file and place it in the same location which is configured for this tool.

 

Some known issues:

1. In the event of a loss of network connectivity, SQL Server will surface the error as not being able to write to the device. You will need to clean up the files relating to this backup operation (if any) and retry the backup.

2. If you want to create a backup using the Tool, you must create a new backup file instead of overwriting/appending to an existing backup file.

 

Thanks,
Sarabpreet Singh

SQL Tip-11 | Using SQL Server Backup to Windows Azure Tool

As you know there are so many cool benefits of using Windows Azure Storage Account, everybody want to use it to get advantage. Last month i blogged about “SQL DB Backup to URL\Cloud” feature which was first introduced in SQL Server 2012 and later in SQL 2014 it was integrated completely in every aspect.

But what if you are still running old version of SQL Server and want to take advantage of Cloud?

Microsoft is being very kind and they are providing you a workaround. They released a small tool which can help you create a one cloud backup strategy that means you will be able to take backups on cloud for prior SQL versions like SQL Server 2005, 2008 and SQL 2008 R2.

By using this tool you get all the latest features like Backup on Cloud, using Encryption and Compression in all earlier versions.  Currently these features are available only in following versions\editions.

Feature Version Edition
Backup Encryption 2014 Enterprise, Standard and Business Intelligence Edition
Backup Compression 2008 Enterprise Edition
Backup Compression 2008 R2 Enterprise and Standard Edition


Isn’t it cool you get all latest features even if you’ve not upgraded to new version\edition.

The Utility can be downloaded from this link and the utility is less than 10 MB is size. Let’s see how to install, configure and use this tool.

Installation of this tool is quite simple just like any other regular software.

1  2

3   4

The tool installs a service which takes the responsibility of taking the backups from local system to Cloud. The tool also creates a separate login for this tool with the name of “SQLBackup2Azure.

service

 

To configure this tool you’ve to provide a local path of a folder where your backups will be placed – this location will act as a source for this tool. You need to also mention the file name\extension which will be picked to be backed-up on the cloud. The best part is you can use wildcards to select the type of files.

config1 config2

 

Next you need to mention the details about your Windows Azure Storage Account, this is similar to creating a credential which will provide necessary access to this tool to backup the files on Azure. You get an option to Verify the account details right from the wizard.

config3

In the next page you also get an option to use Encryption and Compression for all these backups.

config4 config5

Read the next part here…

Thanks,
Sarabpreet Singh

SQL Tip-10 | SQL Server Database Backup to Cloud (Microsoft Azure)

This feature can help you leverage the power of Cloud with your SQL instance and taking your backups directly on Cloud.

Backup to URL is not new to SQL 2014, it was first introduced in SQL Server 2012 SP1 CU2, but you were allowed to use the URL in query only. Starting with SQL Server 2014 this feature has been totally integrated, now you can specify URL while taking Backup or doing Restore no matter if you are performing the task via Query, Backup\Restore GUI, Powershell cmdlets or using SMO.

To be able to take backups or restore from Microsoft Azure (earlier named as Windows Azure) you must create a Credential. Before creating a credential you must already have a Storage account created on Azure. Once you create your storage account you’ll be able to get Storage Access Key from Azure portal. This Storage access key will provide full Admin control on your storage account.

Here is the script to create the Credential:

Create credential Storage_4Backup

with identity = ‘dbbackup12’,

SECRET = ‘H61bUxq5ld+AUFGa3Zc30CKYGSOyMDGEb/MKcCjvssdrgF34PZt+TV/42/HoJpOgnrM82KQpuCYYHEudjg==’

go

 

You need to replace the Secret key with your storage access key, the screenshot below will tell you from where you need to copy this.

clip_image002

Once the credential is created on your SQL instance you are now all set to be able to take backups or do restore from Windows Azure storage account.

Here is the T-SQL Script for the same:

Backup database azure_test

to URL = ‘http://dbbackup12.blob.core.windows.net/sqlprod/azure_test.bak’

with credential = ‘Storage_4Backup’, format, compression, stats=10, MediaName = ‘azure_test_22_02_2014’,

mediadescription=‘Backup_of_azure_test’

 

Note: To get the complete script bundled with step-by-step demo follow this link.

You can also do the backup to windows azure using Backup UI – but the credential is a must, here is the screenshot.

If the credential already exists you can select from the drop down menu otherwise the wizard itself gives you an option to create one.

clip_image004

Rest everything is self-explanatory. 😉

 

Thanks,
Sarabpreet Singh

SQL Tip-8 | How to script out multiple SQL Server jobs at once

Migrating SQL Server jobs from one server to another is a critical task and there are many alternatives available for the same, the easiest one available is to script out each and every job and re-create them on the destination server.

If there are a few jobs you can very well script out each and every job individually, but once the count of jobs or number of servers to be migrated increases this seems to be another challenge.

For this reason SQL Product team provided a very convenient (but not so popular) option to script out all the jobs.

All you need to do is select jobs in the object explorer and open the Object Explorer Details Tab (shortcut key is F7) – Once the object explorer details tab is in view, you can now select multiple jobs by holding Ctrl (control) key.

Once all the desired jobs are selected right click and select script job as from the context menu.  Please refer the attached Screenshot to get more idea.

image.png

Enjoy!

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

SQL Tip-7 | Different ways to enable a Trace Flag

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. There are many documented and undocumented trace flags. For example Trace Flag 1222 that can be used to get Deadlock Graph. In Trace Flag 1222 you get a detailed information about all the processes running and participating in the deadlock, which query they were running, what all resources were locked, what was the isolation level and what type of lock request was being blocked and why. You can use all this information to troubleshoot the problem.

You can switch-on Trace Flags either by using DBCC Commands or by Startup Parameters.

a. Enable Trace Flag using DBCC Commands

DBCC TraceON(1222)

Once the trace flag is enabled you can check the status by using DBCC TraceStatuscommand, it will provide an output similar to below screenshot.

clip_image001

In the above screenshot you can see that the trace flag 1222 has been enabled but on Session level. Means the trace flag will work for only the current session. If you want to enable this for the full instance you need to give one more parameter i.e., -1. This will enable the trace flag on global level. To enable the trace flag on Global level let’s first disable\switch off the trace flag on session level and then we’ll enable it on global level.

DBCC TraceOFF(1222)

DBCC TraceON(1222,-1)

Now let’s check the status one more time.

clip_image002

So the trace flag has been enabled on Global\Instance level.

b. Enable Trace Flag using –T Startup Option

SQLServer uses Startup Parameters during startup to find Master DB Data\Log & Error Log Files, along with this one can also specify some server wide Conditions. Most users do not need to use anything apart from Data\Log & Error log File paths, until and unless you want to change the default startup behavior of DB Engine.

Trace Flags are used to start the server with nonstandard behavior. When using this functionality use an Uppercase T to pass trace flags, a lower case t enables other internal flags that are required by only SQL Server support engineers. The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option.

You can do this in SQL Server Configuration Manager, Choose the right SQL Server Service – right click and open properties. Follow the screenshot below to specify the trace flag and press Add. (the below screenshot is from SQLServer 2012)

Note: Changing any Startup Parameter\option needs a SQL Service Restart.

clip_image003

clip_image004

Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox