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,

NameCan be changed?
Show Advanced OptionsYes
Ad Hoc Distributed QueriesYes
allow updatesNo
automatic soft-NUMA disabled No
backup checksum default Yes
backup compression defaultYes
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 workloadsYes
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.

-> This is not a problem anymore in 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.

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.