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.

Advertisements

Connecting to Azure Managed instance from a virtual machine

1) Create  a new subnet in the VNet JB_VN for a virtual machine

-> Click on Dashboard. Select resource group JB_RG and then Virtual network JB_VN. Click on Subnets under Settings and click Subnet.

Blog39_1.PNG

-> Complete the details as below and click OK.

Blog39_2.PNG

2) Create a virtual machine in the new subnet in the VNet

-> Click “Create a resource” and select “Windows Server 2016 VM”

Blog39_3.PNG

-> Complete the required details and proceed further,

Blog39_4.PNG

Blog39_5.PNG

-> On the Settings, select Subnet and select JB_VM_Subnet and click OK.

Blog39_6.PNG

-> On the Summary page, check the details and click Create.

3) Connect to virtual machine

-> Once the VM is created, Connect to it.

Blog39_7.PNG

-> Download the RDP file and connect to the RDP session.

-> In Server Manager, Click Local Server and select on in “IE Enhanced Security Configuration”. Select Off and click OK.

Blog39_8.PNG

-> Open Internet explorer. Download and Install SSMS from https://docs.microsoft.com/sql/ssms/download-sql-server-management-studio-ssms.

-> Once SSMS installation completes. Click on Dashboard and select Resource group JB_RG. Select Managed Instance jb and copy the host as shown below,

Blog39_9.PNG

-> Open SSMS and connect to the Managed Instance,

Blog39_10

Blog39_11.PNG

I have used the details present in this article to blog here.
Other articles related to Managed Instance can be found here.

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.

My first Azure Managed instance

1) Login to Azure portal.
2) Configure a virtual network.

-> Click “Create a resource”. Search for “Virtual network”. Select it and click on “create”.
Blog38_1.PNG

-> Provide the required details as below and click “Create”.

Blog38_2.PNG

3) Create a route table and a route.

-> Click “Create a resource”. Search for “Route Table”. Select it and click on “create”.

Blog38_3

-> Provide the required details as below and click “Create”.

Blog38_4.PNG

-> Open the route table once it is created, click on “Routes” and then click “Add”.

Blog38_5

-> Provide the required details as below and click “OK”.

Blog38_6.PNG

4) Apply the created route table to Managed Instance Subnet.

-> Click “Dashboard”, open Resource group “JB_RG” and select “JB_VN”. Click “Subnets” and then select “JB_Subnet”.

Blog38_7.PNG

-> Select “Route Table” and then “JB_RT”. Click “Save”.

Blog38_8.PNG

5) Create our Managed Instance.

-> Click “Create a resource”. Search for “Managed Instance”. Select it and click on “create”.

Blog38_9.PNG

-> Provide the required details and click on create.

Blog38_10.PNG

-> Your first instance will take close to 24 hours to complete as advised in the message.

Blog38_11.PNG

-> Click on resource group “JB_RG” and then click on Deployments as shown below,

Blog38_12_0.PNG

-> Select the “Managed Instance” that is getting deployed and you will see the below screen.

Blog38_12.PNG

-> Still getting deployed..

Blog38_13.PNG

-> It finished finally after 33 hours.

Blog38_14.PNG

-> This is the portal view after Managed instance is built,

Blog38_15.PNG

I created my first managed instance using this article and blogged here.
Other articles related to Managed Instance can be found here.

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.

Azure Managed Instance

Azure Managed Instance as per my understanding is a new essence of Azure SQL database. It is more compatible to On-Premise SQL Server than Azure SQL database. It provides network isolation addressing some common security concerns with Azure SQL database. Managed Instance allows minimal database and application changes while moving from On-Premise to Azure. It provides automatic patching, backups, high availability same as PaaS offerings.

I have started testing the managed Instance and will list down the capabilities below as I go,

1) My first Azure Managed instance.
2) Connecting to Azure Managed instance from a virtual machine.
3) Checking SQL Server errorlog in Azure managed Instance. 
4) Trace Flags enabled in Azure Managed Instance
5) Is Trace Flag 1117 enabled in Azure Managed Instance?
6) Physical_database_name column in sys.databases
7) Restore a database backup to an Azure Managed Instance

Limitations

1) Getdate() function
2) Sp_configure on Managed instance

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.

Error 1311.Source file not found: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Update Cache\KB4018073\ServicePack\redist\VisualStudioShell\VC10SP1\vc_red.msi. Verify that the file exists and that you can access it.

-> I was applying SQL Server 2012 Service Pack 4 on a database server. It failed with below error,

Instance MSSQLSERVER overall summary:
Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.
Exit code (Decimal): -2068709375
Exit facility code: 1202
Exit error code: 1
Exit message: The required MSI package ‘C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Update Cache\KB4018073\ServicePack\redist\VisualStudioShell\VC10SP1\vc_red.msi’ doesn’t exist.
Start time: 2018-02-20 09:54:47
End time: 2018-02-20 09:57:19
Requested action: Patch
Exception help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.7001.0&
EvtType=0xB34122F7%400xF762992B%401202%401&EvtType=0xB34122F7%400xF762992B%401202%401
Feature: Database Engine Services
Status: Failed: see logs for details
Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail.
Next Step: Use the following information to resolve the error, and then try the setup process again.
Component name: Microsoft Visual Studio 2010 Redistributables
Component error code: 1311
Component log file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20180220_103719\MSSQLSERVER\VC10Redist_Cpu64_1.log
Error description: Error 1311.Source file not found: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Update Cache\KB4018073\ServicePack\redist\VisualStudioShell\VC10SP1\vc_red.msi. Verify that the file exists and that you can access it.
Error help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.7001.0&
EvtType=vc_red.msi%40InstallFiles%401311

-> There were no missing MSI/MSP file. I extracted SQL Server 2014 Service Pack 4 KB4018073 to C:\temp\SP4 and searched for vc_red.msi file.

-> I placed the files vc_red.msi and vc_red.cab from C:\temp\SP4\redist\visualstudioshell\vc10sp1\x64 to C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Update Cache\KB4018073\ServicePack\redist\VisualStudioShell\VC10SP1\.

-> Started the patch and it failed with below error,

Feature: Database Engine Services
Status: Failed: see logs for details
Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail.
Next Step: Use the following information to resolve the error, and then try the setup process again.
Component name: Microsoft Visual Studio 2010 Redistributables
Component error code: 1642
Component log file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20180220_104714\MSSQLSERVER\VC10Redist_Cpu32_1.log
Error help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&
ProdVer=11.0.7001.0&EvtType=vc_red.msi%400x162A16FE%400×1642

-> Replaced the files vc_red.msi and vc_red.cab from C:\temp\SP4\redist\visualstudioshell\vc10sp1\x86 to C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Update Cache\KB4018073\ServicePack\redist\VisualStudioShell\VC10SP1\.

-> Started the patching again and it completed fine without any issues.

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.

Backup On-Premise SQL Server Database to Microsoft Azure

Blog36_1

-> Creating a Storage Account,

Blog36_2.PNG

Blog36_3.PNG

-> Once the Storage account is deployed. Click on Blog36_4 Resources and then click on your Storage that we deployed just now. It is jbsqlbackups in my case,

Blog36_5.PNG

-> Click on Containers and add a new container.

Blog36_6.PNG

-> Click on Access Keys and track down your Storage Account Name and the Keys,

Blog36_7.PNG

-> Open SQL Server Management Studio and try a backup,

Blog36_8.PNG

Blog36_9.PNG

Blog36_10.PNG

Blog36_11.PNG

Blog36_12.PNG

-> Lets connect to the Azure storage and check the backup file,

Blog36_13.PNG

-> Copy the Access key that was noted earlier and paste it connect to the Azure Storage,

 

Blog36_14

Blog36_15.PNG

-> Lets try restoring the database,

Blog36_16

Blog36_17.PNG

-> Click on OK and you will get the below screen. Select the backup that you ant to restore,

Blog36_18.PNG

Blog36_19.PNG

Blog36_20.PNG

-> The database is restored and ready for use.

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.

AlwaysON – Unable to access the ‘JB1’ database because no online secondary replicas are enabled for read-only access. (Microsoft SQL Server, Error: 982)

Environment

Blog29_1

-> The Application makes a connection to the Database JB1 with ApplicationIntent=ReadOnly and receives below message,

Unable to access the ‘JB1’ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.
Changed database context to ‘JB1’. (Microsoft SQL Server, Error: 982)

-> The message indicates that there are no active Read-Only partners. When checked further it is clear that Server JBSERVER2 and JBSERVER3 were down.

-> We were advised that the Servers JBSERVER2 and JBSERVER3 will not be up for next 6 Hours. Now the Application team doesn’t want to change their connection string by dropping ApplicationIntent=ReadOnly and wanted us to make sure that the primary accepts Read connections.

-> The Alwayson Availability group setting is as below,

Blog35_1.PNG

-> We changed the setting “Connections in Primary Role” from “Allow read/write connecions” to “Allow all connections” as below,

Blog35_2.PNG

-> The Application started connecting to the Primary Replica even with ApplicationIntent=ReadOnly.

-> This now brings up the question, why can’t we have the setting “Connections in Primary Role” set to “Allow all connections” instead of “Allow read/write connecions”. “Allow all connections”  can be an issue as Connections where the Application Intent is set to ReadOnly are not disallowed on the Primary Replica anymore. With this setting in place the read workloads may execute on both Primary and the secondary, If server JBSERVER2 which is on the same data centre as the PRIMARY goes down and we have intermittent network issues between DataCenters which makes JBSERVER3 to be offline Intermittently for JBSERVER1. So the Read workload might run on both JBSERVER1 and JBSERVER3.

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.