None of the IP addresses configured for the availability group listener can be hosted by the server ”

-> I was configuring Alwayson availability group on a database server JBSERVER1 with replicas to JBSERVER2 and JBSERVER3.

-> The setup can be identified with below design,

Blog43_1.PNG

-> I was able to create the availability group and add the required database onto it. I tried adding the listener and got the below error,

TITLE: Microsoft SQL Server Management Studio
——————————
Create failed for Availability Group Listener ‘JBS_APP’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17199.0+((SSMS_Rel).171004-0254)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroupListener&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
None of the IP addresses configured for the availability group listener can be hosted by the server ‘JBSERVER1’. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted on a public cluster network for this server. (Microsoft SQL Server, Error: 19456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3022&EvtSrc=MSSQLServer&EvtID=19456&LinkId=20476
——————————
BUTTONS:
OK
——————————

-> JBSERVER1 and JBSERVER2 IPAddress belonged to a subnet and JBSERVER3 IPAddress belonged to a different subnet.

-> I noticed that the IPAddress, I have provided for the Listener was from the subnet same as JBSERVER1 and JBSERVER2 and I did not added any IPAddress for this listener for Subnet with respect to JBSERVER3.

-> I then added 1 IPAddress from each subnet for the Listener and that resolved the issue.

-> Another workaround will be to remove JBSERVER3 from Availability group and just add the listener with IPAddress that belongs to JBSERVER1 and JBSERVER2 subnet. Once we have the other IPAddress, we can add JBSERVER3 to the availability group and then include the second IPAddress from different subnet to the Listener.

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

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.

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.