All You should not miss about Backups in Azure managed instance

Hello Readers!

This  is an informational blog on summarizing the differences observed in  backups of  Azure Managed instances with that of SQL Servers in On-premise and other Azure SQL offerings.It also covers the necessary information one must have about managed instance backups if you are an Azure DB Admin.

As many of you might know already ,backups and patching  are azure managed and automated in Managed Instances or any other PaaS  for database  (or DBaaS) offering in Azue. For Backups, We only got control only over the retention period configuration . Though we know that Azure takes managed instance backup to an azure storage which is replicated with RA-GRS to another region, we do not really get to access the Blob container or a storage account that is holding these backups.


When we are creating a managed instance, the backup will be turned on by default and with the default schedule as the below.

Full backups - every 7 days,
Incremental  - every 12 hours,
log backups  - every 5-10 min.

It is worth to notice that , the incremental backup here is not the differential backup we have got in the on-premise. This only keeps the the data from the previous Full or Incremental backup (whichever is new) unlike the differential backup which keeps the changed extents from the previous full backup.

Changing Retention Plan for the backup


Azure Managed instances  only have automated scheduled backups with Short Term retention plan that can be set as either 7,14,21,28 or 35.The default short term retention period is only 7 days.Its wise to change it after you provision the managed instance. 
 The other Azure DBaaS , Singleton and Elastic pool are having Long term retention plan too, apart from the short term retention plan.

Though Azure managed instances support Copy_only backups taken to a azure storage account which is a perfect workaround for that limitation.

You can change the backup retention period of a database from the Azure portal or by other means like Powershell, REST API. This can be only done for the database and not for the entire managed instance for now. 

Azure portal method:

Navigate to your database of Azure managed instance in azure portal and click on 'Configure backup retention'.



Change the value in the popup that appears and click on Apply.


Caution: When you reduce the retention period from a higher value, all the old backups beyond the new retention period will be ignored and you would not be able to restore them again. 


As I told above, we can also take adhoc backup with copy_only clause to an azure storage account/blob container. I have written about how to create credentials for it in my previous blogs.

That brings us to the end of this blog. I will write soon on Point in time  restoration and Geo-restore in my future blogs. Thanks for visiting !

Read only replicas of Azure managed instances and Linked Servers

Hello Readers!

Welcome to my blog once again!
The below blog is to summarize the experience I had with the read only replicas and using linked servers to connect to it for read only workload .
Before we begin, if you have no clue about the read only replicas, I recommend you read my blog post on Azure managed instances high Availability options here for the detailed description.

A few months back, I was involved in finalizing the migration plan for a reporting system which basically process the OLTP or Live transactional data with a set of T-SQL procedures called from SSIS packages.In the On-premise setup , they used to have a separate SQL server to which the transactional replication is enabled from the Actual Prod OLTP server. So the system works with the replicated subset of databases and process it locally.We had plans to use the business critical instances for the Prod OLTP instances and a general purpose instance fir the report server. We wanted to explore the feasibility of using the Read only replicas of the Prod instances without having any impact on performance,  for building a cost effective plan for the migration . There is another reason why wanted to explore this. The data in the OLTP was actually very near the retention period planned (which is 7 years) and future growth was kind of capped and predictable. If the  read only replica plan  could match the performance of the On-premise setup, we could save some cost in hosting replicated copies of the databases in the reporting DB instance. 

The plan was to create a linked server in the reporting instance and make it connect to the Read only replica , so procedures in the reporting database could access the data from the Read only replica through the linked server.

So I have created a  linked server for my POC with the below query.

EXEC sp_addlinkedserver @server=N'Prodcopy', --Change the name as you need
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr = N'ApplicationIntent=ReadOnly',
@datasrc= '<ProdInstance PrivateEndpoint address>';

The next step was to update the procedures kept in the report database , so it would not access the OLTP databases' data locally but use the remote database through the linked server. This part was quite simple.I could convert the existing three parts query to a 4 parts one (Linked server.Database.schema.object)

1.On top of the procedure(which is written on dynamic SQL), i have introduced a variable named  @link

Declare @link nvarchar(15)
Set @link='Prodcopy'
2. Added this part   '+@link+'.'+ infront of every object references in the procedures.
3. Removed (nolock) query hint as 4 parts query dont work with it. 

I have edited this in all 12 procedures which were working with retrieving data from prod tables containing data like orders, quotes, invoices etc, and join them with local tables in the reporting databases.


I could test one or two procedures and could see that its working fine. 
But I could also observe  that the procedures' performance got degraded drastically. I tried applying workarounds like the below.

1. Recording the table names and the columns being referred from these table names in an excel sheet

2.Create Views in the Prod databases for these tables (with all column names used in the procedures).

3.Replacing the four parts query with Openquery.


4 parts query:

Select column1,column2 from Prodcopy.DB.dbo.quotes where orderid=23432

OpenQuery :

(SELECT column1,column2 FROM openquery (Prodcopy, 'SELECT column1,column2 FROM DB.dbo.quotes WHERE orderid =23432');

By doing this I could see that 11 out of the 12 procedures performance got improved a lot, even better than the on-premise response time. One procedures which rather had a big list of tables and joins, couldnt improve much. The procedure was as big as to be written in five or six  A4 size papers.

Eventually I had to drop the plan (because of this one procedure which is huge), and go for transactional replicated databases for processing the data locally. But I learned a thing or two.


I was even aware earlier that Linked server often get excluded from new gen code practices because of performance issues. Yet we use it for reporting purposes. I have seen many SQL reporting setups which uses linked servers 


Conclusion: 

1. Linked server performance when we are joining local tables and remote ones are a bit bad
2.You can workaround by converting the tables to views and using openquery to access them in your t-sql/dynamic SQL scripts.
3.Openquery and 4 parts query execution plans are entirely different . One can confirm this from their queries'  execution plan in SSMS.


You should independently do a performance comparison to decide if you can adopt it or not based on your own experience.I hope to have a setup planned like this in one of my future assignments.

Thanks for reading !

Migrating your SQL server Databases to Azure Managed instances in offline mode using native backup restore method

Hello Readers !

The below is a small blog on how do we migrate SQL Server Databases to Azure Managed instance using SQL native backups we take from our On-premise SQL servers. This is an offline method where you would require downtime throughout the activity till the connection is re-established to your new database in the cloud and hence not suitable for giant databases with businesses not willing to put up with a bigger downtime window. This can still be adopted for small databases based on your convenience.

Managed instances databases do not support "norecovery" mode in restoring databases which means that we cannot use a restore plan involving restoration of latest full, differential, subsequent log backups.

Lets get to it.

Step 1: Creating a Blob container in Azure storage account

Go to your storage account in azure portal and click on Containers (basically blob folders),

Here we would get to create a container.



You can refer my previous detailed blog series on storage account to learn more. Part 1 2 3

Step 2: Creating SQL credentials with the SAS key of the storage account for storage account.

The concept of  "Credentials" has been there in MS SQL server space for long. It is used by SQL server to connect to resources outside SQL server, in our case the azure storage account where we have created the blob container.

The name of the credential we create should be the complete URL of the blob container which is going to be used for holding the backup files.
You can easily find it out by navigating to your storage account and by clicking the properties settings in the left pane.

Please check the top portion of my blog here to learn how to generate SAS token which contain the authentication information to connect to the storage account.

Below is a sample t-SQL script to create SQL credentials using the SAS token


Use master
go
CREATE CREDENTIAL [https://<Yourstorageaccount>.blob.core.windows.net/<Containername>] WITH IDENTITY = 'SHARED ACCESS SIGNATURE' , SECRET = 'sv=2017-11-09&ss=bfqt&srt=sco&sp=rwdlacup&se=2028-09-06T02:52:55Z&st=2018-09-04T18:52:55Z&spr=https&sig=WOTifdfdfdfdfdf%2BwndxW2CQ7%2B5fHd7Is%3D'


Please make sure to remove the '?' sign in front of the SAS token . That is a common mistake many do when they are creating the credentials. We need to have the SQL credential created in both the source SQL server instance (If you are directly taking backup to azure storage account/blob container) and in the managed instance.




Step3: Backing up your databases to azure blob container from the Source DB instance

Use the regular backup T-SQL command only with a difference of "To URL" to write your backup set to azure blob container.Authentication for this backup command to write the backup onto azure storage, will be taken care by the Credential we created above with the container URL and the SAS token.

T-SQL command


Use master
go
BACKUP DATABASE <DBName> TO

URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname.bak' with compression


You may also keep Copy_only if you are doing your test migration and dont want to affect the backup schedule in your DB instance.It also supports stripping of big DB backups to multiple files for ease of management. All you need to do is adding as many "URL ='Backup foldername+filename' in the backup command like below.


Use master
go
BACKUP DATABASE <DBName> TO
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname1.bak' ,
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname2.bak' ,
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname3.bak'
with compression

Step4: Restoring the Backup from the Blob container into the managed instance
T-SQL commanduse master
go
RESTORE DATABASE <DBname> FROM URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/WideWorldImporters-Standard.bak'

If you have created stripped backup from step3, make sure to give the URLs of all your backup in the restore command.

use master
go
RESTORE DATABASE <DBname> FROM
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname1.bak',
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname2.bak' ,
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname3.bak'



You can view your restored database in SSMS or in the portal in your managed instance page.

You can also see the progress of your restoration using DMVs like below.


use master
go
Select percent_complete from sys.dm_exec_requests where session_id=<SPID of your restore session>


I hope you are able to find your restore session's SPID using Sp_who/Sp_who2 active.

For online restoration to minimize downtime, we have methods like SQL replication and Azure Database migration service (DMS), which is a premium service listed in Azure market place.

That brings us to the end of this blog. Hope you find it useful and simple. I will write on Online DB migration in future with all pre-requisites.