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.

Advertisements

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.

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.

 

 

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.