Restore a database backup to an Azure Managed Instance

1) Creating Azure storage account and upload backup file

-> Click “Create a resource”. Select “Storage” and click “Storage Account”.

Blog45_1.PNG

-> Complete “Create storage account” and click “Create”.

Blog45_2.PNG

-> Once the Storage Account deployment completes. Click on Resource group JB_RG, select jbmistorage. Under settings, click “Shared Access Signature”. Check the details and modify accordingly to your needs. I am leaving the default and clicking on”Generate SAS and connection string”.

Blog45_3.PNG

-> Copy the SAS Token and BLOB Service SAS URL and place it in carefully,

Blog45_4.PNG

-> Under “BLOB SERVICE”, click containers and Click “+ Container”.

Blog45_5.PNG

-> Provide the required details and click ok.

Blog45_6.PNG

-> Click on Resource group JB_RG, select jbmistorage. Click Conatiners under “BLOB SERVICE”. Click on “jbmibackupcontainer”. Click on properties.

Blog45_7.PNG

-> Copy the URL.

Blog45_8.PNG

-> Once the URL is copied, go back to the container page by clicking  on Resource group JB_RG, select jbmistorage. Click Conatiners under “BLOB SERVICE”. Click on “jbmibackupcontainer”. Click on “Upload”. Select the backup file you want to upload and click “Upload”. Wait for the upload to complete.

Blog45_9.PNG

-> Upload of backup file in progress.

Blog45_10.PNG

-> Upload completed.

Blog45_11.PNG

2) Restore the database JB_AQ on the Managed instance.

-> Create a SAS Credential using below query,

CREATE CREDENTIAL [https://<storage_account_name>.blob.core.windows.net/<container>]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘<shared_access_signature_key_with_removed_first_?_symbol>’

https://<storage_account_name&gt;.blob.core.windows.net/ – Click on “Resource group” JB_RG and select “jbmistorage”. Copy the “Blob Service Endpoint” as indicated below and replace “https://<storage_account_name&gt;.blob.core.windows.net/” with the copied value.

Blog45_12.PNG

<container>- Click on “Resource group” JB_RG and select “jbmistorage”. Select “Container” under “BLOB SERVICE”. Copy the container name on the Right side. The container name in my case is “jbmibackupcontainer”.

Blog45_13.PNG

-> When you click on the container “jbmibackupcontainer”. You will be able to find the backup file uploaded.

WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’ – Will remain as it is.

<shared_access_signature_key_with_removed_first_?_symbol> – Replace this with the SAS Token that was stored earlier. Please note that you should remove the leading ? from the SAS Token.

Blog45_18.PNG

-> My command is as below,

CREATE CREDENTIAL [https://jbmistorage.blob.core.windows.net/jbmibackupcontainer]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘sv=2017-11-09&ss=b&srt=sco&sp=rwdlac&se=2018-06-13T09:33:37Z&st=2018-06-13T01:33:37Z&spr=https&sig=AWlMTj6MZ0M4ictY2nBJ4%2BfVr0kx0RWfpFU1xlJ76FU%3D’

-> Execute the query,

Blog45_16.PNG

-> Below query checks the SAS Credential and backup validity,

RESTORE FILELISTONLY FROM URL =
https://<storage_account_name&gt;.blob.core.windows.net/<container>/<Backup_File>.bak’

https://<storage_account_name&gt;.blob.core.windows.net/ – Click on “Resource group” JB_RG and select “jbmistorage”. Copy the “Blob Service Endpoint” as indicated below and replace “https://<storage_account_name&gt;.blob.core.windows.net/” with the copied value.

Blog45_12.PNG

<container>- Click on “Resource group” JB_RG and select “jbmistorage”. Select “Container” under “BLOB SERVICE”. Copy the container name on the Right side. The container name in my case is “jbmibackupcontainer”.

Blog45_13.PNG

<Backup_File>.bak – Replace it with the backup file we uploaded. When you click on the container “jbmibackupcontainer” . You will be able to find the backup file uploaded.

Blog45_15.PNG

-> My command is as below,

RESTORE FILELISTONLY FROM URL =
https://jbmistorage.blob.core.windows.net/jbmibackupcontainer/JB_AQP_MI.bak&#8217;

-> Execute the query,

Blog45_17.PNG

-> Use the below query to restore the database JB_AQ in Managed Instance.

RESTORE DATABASE [JB_AQ] FROM URL =
https://jbmistorage.blob.core.windows.net/jbmibackupcontainer/JB_AQP_MI.bak&#8217;

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Physical_database_name column in sys.databases

-> I was trying to rename a database on the Azure managed Instance and got the below expected error,

TITLE: Microsoft SQL Server Management Studio
——————————
Unable to rename JB_MI_1. (ObjectExplorer)
——————————
ADDITIONAL INFORMATION:
Rename failed for Database ‘JB_MI’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Database&LinkId=20476
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Database ‘8b680fc7-b6ab-4d70-be7a-dff62547bf51‘ is enabled for database mirroring or has joined an availability group. The name of the database cannot be changed. (Microsoft SQL Server, Error: 957)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=957&LinkId=20476
——————————
BUTTONS:
OK
——————————

Blog44_1.PNG

-> It was unusual to see a GUID as a database name in the message.

-> I checked this further to see if I can understand what this GUID is and tried querying the sys.databases to see if I have some details.

-> The sys.databases has a column called physical_database_name that holds this value.

Blog44_2.PNG

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Sp_configure on Managed instance

-> We use Sp_configure almost everyday on our Database Server in On-Premise.

-> We can access Sp_configure in Managed Instance as well. But not all setting can be tweaked.

-> We will check most of the used configuration settings and see if it can be changed or not,

Name Can be changed?
Show Advanced Options Yes
Ad Hoc Distributed Queries Yes
allow updates No
automatic soft-NUMA disabled  No
backup checksum default  Yes
backup compression default Yes
blocked process threshold (s)  Yes
clr enabled  Yes
clr strict security  Yes
contained database authentication  Yes
cost threshold for parallelism  Yes
cross db ownership chaining  Yes
Database Mail XPs  Yes
filestream access level  No
fill factor (%) No
index create memory (KB)  Yes
lightweight pooling  No
locks  No
max degree of parallelism  Yes
max server memory (MB)  No
max text repl size (B)  No
max worker threads  Yes
min memory per query (KB)  Yes
min server memory (MB)  No
nested triggers  Yes
network packet size (B)  Yes
Ole Automation Procedures  Yes
optimize for ad hoc workloads Yes
priority boost  No
recovery interval (min)  Yes
remote admin connections  Yes
Replication XPs  Yes
scan for startup procs  No
xp_cmdshell  Yes, But when you execute xp_cmdshell in query window. You will get an error “‘xp_cmdshell’ is not supported in this version of SQL Server.”

 

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

GETDATE() on Managed Instance

-> I was testing different features of Managed Instance. I moved an On-premise database to Managed Instance along with few jobs.

-> Everything was fine. But the jobs were not working fine. Later I found that the job uses getdate() function.

-> It seems like Getdate() function returns time in UTC time zone only. Any objects within the database and Jobs using Getdate() function will return time in UTC time zone than the timezone where the Instance resides. This might break the business logic of some objects and jobs.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Is Trace Flag 1117 enabled in Azure Managed Instance?

-> The list of trace flags enabled by default in an Azure Managed Instance doesn’t contain trace flag 1117.

-> The official documentation states that “A Managed Instance runs with all of the features of the most recent version of SQL Server, including online operations, automatic plan corrections, and other enterprise performance enhancements.”. This advises us that features related to trace flag 1117 should be enabled by default even though the trace flag is not explicitly enabled.

-> Lets check if this is actually true!

-> Managed instance that I am testing has below Tempdb files,

Blog42_1.PNG

-> I am inserting some data into a temporary table and check if all the tempdb files grow,

Blog42_2.PNG

-> It is pretty clear that all the tempdb files have grown. Lets check the SQL Server error log to see if there are any messages related to Tempdb autogrow,

Blog42_3.PNG

-> The messages recorded above shows us that autogrow has happened.

-> This proves that the features related to trace flag 1117 are enabled in the managed instance and it is not required for us to enable it explicitly.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Trace Flags enabled in Azure Managed Instance

-> There are many trace flags enabled by default in Azure Managed Instance.

Blog41_1.PNG

-> At this point, I am not sure if these trace flags will be enabled after the preview. But I can see trace flags that are used to write finer details to errorlog. So I think these may be removed later.

-> Lets see what these trace flags do. Some details can be incorrect, I will change it going forward as required.

Trace Flag Description
1800 1)  Details related to SQL Server startup.
2) This article mentions this trace flag which is related to “Slow synchronization when disks have different sector sizes for primary and secondary replica log files in SQL Server AG and Logshipping environments”
2551
2591
3004 Lists the database Backup / restore details step by step.
3447
3605 Redirects output to SQL Server errorlog
3701 Script level upgrade scripts will be written to SQL Server errorlog during failures. This gets added automatically to On-Premise SQL Server during patching.
3978
4141
5521
7838
8015  Ignores physical NUMA detection. This article has more details.
8017  Avoid creating a lot of offline schedulers.
8037
8054
8057
8063
8065
8086
9041
9537
9570
9837 Details related to checkpoint
9850 Details related to checkpoint
9883
9905
9934
9940
9941

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Checking SQL Server errorlog in Azure Managed Instance

-> I executed xp_readerrorlog in SSMS and could see the command working fine in Managed Instance same as the On-Premise SQL server.

-> Azure Managed Instance version details from error log,

Capture.PNG

-> Azure Managed Instance version details using @@version,

Blog40_2

-> The version numbers are different just as the type. I think this will reflect same details in future. The official documentation states that “A Managed Instance runs with all of the features of the most recent version of SQL Server, including online operations, automatic plan corrections, and other enterprise performance enhancements.”.

Update from Microsoft Engineer :  Azure Managed Instance is still considering as a PAAS offering which is similar to SQL Azure database, so the “select @@version” will return a similar thing as SQL Azure database.
The Errorlog is something that can be considered as a backed service data for SQL Azure, so it is reflecting the real version of the database engine.

-> There are many trace flags enabled by default. We will discuss what these trace flags do in a separate post.

Blog40_3.PNG

-> SQL Server errorlog shows that 24 logical processors are detected.

Blog40_6

-> Checking the instance properties, the processor count is 24.

Blog40_8

-> But, I have selected 8 cores while building the managed instance. Checking sys.dm_os_schedulers shows that we have 8 online schedulers based on the below screenshot,

Blog40_7

-> Checking the processors tab in server properties shows that we only have 8 cores assigned to the Managed Instance,

Blog40_9.PNG

-> The extra cores may be used in case the managed instance is scaled up.

-> Memory manager uses conventional memory and not locked pages in memory. Instance collation is SQL_Latin1_general_CP1_CI_AS.

Blog40_4.PNG

-> Instant file initialization is disabled.

Blog40_5.PNG

-> The errorlog log contains detailed messages than the On-Premise SQL Server. There are many debug-level messages in the errorlog. It seems like these messages will be used by Microsoft Engineers to troubleshoot issues related to Managed Instance.  The traceflags enabled by default on a managed Instance could be the reason for these debug-level messages. I am not sure if these trace flags and messages will be removed after the preview.

-> This article explains about the procedure sp_readmierrorlog that removes most of the debug-messages that are not really required for a normal user.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.