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.