Finding Undistributed commands in Transactional replication

-> Below query can be used to find undistributed command count in transactional replication.

set nocount on
declare @publisher varchar(255)
declare @publisher_db varchar(255)
declare @Subscriber varchar(255)
declare @Subscriber_db varchar(255)
declare @subscription_type int
declare @subscriber_id int

set @publisher = ‘<Publisher SQL Instance Name>’
set @publisher_db = ‘<Publisher Database>’
set @Subscriber = ‘<Subscriber SQL Instance Name>’
set @subscriber_db = ‘<Subscriber Database>’

set @subscriber_id = (select distinct b.subscriber_id from master.dbo.sysservers a INNER JOIN MSsubscriptions b on a.srvid = b.subscriber_id where a.srvname=@Subscriber)

Declare @PublicationName VARCHAR(1000)
DECLARE CheckUndistributedcmdinbadway_cursor CURSOR FOR

select distinct b.publication,a.subscription_type from MSsubscriptions a inner join MSpublications b on a.publication_id = b.publication_id where a.subscriber_id = @subscriber_id

OPEN CheckUndistributedcmdinbadway_cursor
FETCH NEXT FROM CheckUndistributedcmdinbadway_cursor INTO @PublicationName,@subscription_type
WHILE @@FETCH_STATUS = 0
BEGIN
print ‘Publication: ‘+@PublicationName
EXECUTE sp_replmonitorsubscriptionpendingcmds
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication =@PublicationName,
@subscriber =@Subscriber,
@subscriber_db =@subscriber_db,
@subscription_type =@subscription_type
print ‘————————————————————————‘
print ‘ ‘
FETCH NEXT FROM CheckUndistributedcmdinbadway_cursor INTO @PublicationName,@subscription_type
END
CLOSE CheckUndistributedcmdinbadway_cursor
DEALLOCATE CheckUndistributedcmdinbadway_cursor

-> Before running this query, select “Results to Text (CTRL + T)” from SQL Server management studio and execute it.

-> The query is not the best way to get the undistributed command count in transactional replication. But helped me solve my purpose. Can be useful for someone else.

-> The query will be blocked if distribution cleanup is running.

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

Logshipping metadata table log_shipping_monitor_secondary is not getting updated

-> I worked on a datacentre migration recently. One of the server involved in the migration was a Logshipping Primary and Monitor server. Logshipping primary and Monitor server were in new datacentre, but the logshipping secondary was at old datacentre.

-> After the migration, all of the logshipping jobs like Backup, copy and restore were working as expected. But the LS_Alert job was failing with below error,

Executed as user: <LOGIN>. The log shipping primary database <ServerName>.<DatabaseName> has backup threshold of 45 minutes and has not performed a backup log operation for 320 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14420). The step failed.

-> I checked the metadata tables msdb..log_shipping_monitor_primary and msdb..log_shipping_monitor_secondary to get more details.

-> Metadata table msdb..log_shipping_monitor_primary was updated recently. But msdb..log_shipping_monitor_secondary table was not updated recently. The time it was last updated was the time when we moved the Logshipping Primary and monitor server to new datacentre.

-> I started a profiler trace on the primary and monitor server without much luck.

-> I then started a profiler trace on the secondary server and found below messages,

OLE DB provider “SQLNCLI10” for linked server “LOGSHIPLINK_<Monitorserver>_-1140148506” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI10” for linked server “LOGSHIPLINK_<Monitorserver>_-1140148506” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.

-> I tried connecting the monitor server using SQL Server management studio from secondary server without much luck. I tried pinging the monitor server from secondary server and got the below,

C:\Users>ping <MonitorServer>
Pinging <MonitorServer> [192.152.0.3] with 32 bytes of data:
Reply from 10.0.0.9: TTL expired in transit.
Reply from 10.0.0.9: TTL expired in transit.
Reply from 10.0.0.9: TTL expired in transit.
Reply from 10.0.0.9: TTL expired in transit.

Ping statistics for 192.152.0.3:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

-> Updated my network team and they corrected it. Tried connecting the monitor server using SQL Server management studio from secondary server and this time it worked.

-> Metadata table msdb..log_shipping_monitor_secondary started getting updated normally.

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.

 

 

Restore a database backup to an Azure Managed Instance

1) Creating Azure storage account and upload backup file

-> Click “Create a resource”. Select “Storage” and click “Storage Account”.

Blog45_1.PNG

-> Complete “Create storage account” and click “Create”.

Blog45_2.PNG

-> Once the Storage Account deployment completes. Click on Resource group JB_RG, select jbmistorage. Under settings, click “Shared Access Signature”. Check the details and modify accordingly to your needs. I am leaving the default and clicking on”Generate SAS and connection string”.

Blog45_3.PNG

-> Copy the SAS Token and BLOB Service SAS URL and place it in carefully,

Blog45_4.PNG

-> Under “BLOB SERVICE”, click containers and Click “+ Container”.

Blog45_5.PNG

-> Provide the required details and click ok.

Blog45_6.PNG

-> Click on Resource group JB_RG, select jbmistorage. Click Conatiners under “BLOB SERVICE”. Click on “jbmibackupcontainer”. Click on properties.

Blog45_7.PNG

-> Copy the URL.

Blog45_8.PNG

-> Once the URL is copied, go back to the container page by clicking  on Resource group JB_RG, select jbmistorage. Click Conatiners under “BLOB SERVICE”. Click on “jbmibackupcontainer”. Click on “Upload”. Select the backup file you want to upload and click “Upload”. Wait for the upload to complete.

Blog45_9.PNG

-> Upload of backup file in progress.

Blog45_10.PNG

-> Upload completed.

Blog45_11.PNG

2) Restore the database JB_AQ on the Managed instance.

-> Create a SAS Credential using below query,

CREATE CREDENTIAL [https://<storage_account_name>.blob.core.windows.net/<container>]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘<shared_access_signature_key_with_removed_first_?_symbol>’

https://<storage_account_name&gt;.blob.core.windows.net/ – Click on “Resource group” JB_RG and select “jbmistorage”. Copy the “Blob Service Endpoint” as indicated below and replace “https://<storage_account_name&gt;.blob.core.windows.net/” with the copied value.

Blog45_12.PNG

<container>- Click on “Resource group” JB_RG and select “jbmistorage”. Select “Container” under “BLOB SERVICE”. Copy the container name on the Right side. The container name in my case is “jbmibackupcontainer”.

Blog45_13.PNG

-> When you click on the container “jbmibackupcontainer”. You will be able to find the backup file uploaded.

WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’ – Will remain as it is.

<shared_access_signature_key_with_removed_first_?_symbol> – Replace this with the SAS Token that was stored earlier. Please note that you should remove the leading ? from the SAS Token.

Blog45_18.PNG

-> My command is as below,

CREATE CREDENTIAL [https://jbmistorage.blob.core.windows.net/jbmibackupcontainer]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘sv=2017-11-09&ss=b&srt=sco&sp=rwdlac&se=2018-06-13T09:33:37Z&st=2018-06-13T01:33:37Z&spr=https&sig=AWlMTj6MZ0M4ictY2nBJ4%2BfVr0kx0RWfpFU1xlJ76FU%3D’

-> Execute the query,

Blog45_16.PNG

-> Below query checks the SAS Credential and backup validity,

RESTORE FILELISTONLY FROM URL =
https://<storage_account_name&gt;.blob.core.windows.net/<container>/<Backup_File>.bak’

https://<storage_account_name&gt;.blob.core.windows.net/ – Click on “Resource group” JB_RG and select “jbmistorage”. Copy the “Blob Service Endpoint” as indicated below and replace “https://<storage_account_name&gt;.blob.core.windows.net/” with the copied value.

Blog45_12.PNG

<container>- Click on “Resource group” JB_RG and select “jbmistorage”. Select “Container” under “BLOB SERVICE”. Copy the container name on the Right side. The container name in my case is “jbmibackupcontainer”.

Blog45_13.PNG

<Backup_File>.bak – Replace it with the backup file we uploaded. When you click on the container “jbmibackupcontainer” . You will be able to find the backup file uploaded.

Blog45_15.PNG

-> My command is as below,

RESTORE FILELISTONLY FROM URL =
https://jbmistorage.blob.core.windows.net/jbmibackupcontainer/JB_AQP_MI.bak&#8217;

-> Execute the query,

Blog45_17.PNG

-> Use the below query to restore the database JB_AQ in Managed Instance.

RESTORE DATABASE [JB_AQ] FROM URL =
https://jbmistorage.blob.core.windows.net/jbmibackupcontainer/JB_AQP_MI.bak&#8217;

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.

Physical_database_name column in sys.databases

-> I was trying to rename a database on the Azure managed Instance and got the below expected error,

TITLE: Microsoft SQL Server Management Studio
——————————
Unable to rename JB_MI_1. (ObjectExplorer)
——————————
ADDITIONAL INFORMATION:
Rename failed for Database ‘JB_MI’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Database&LinkId=20476
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Database ‘8b680fc7-b6ab-4d70-be7a-dff62547bf51‘ is enabled for database mirroring or has joined an availability group. The name of the database cannot be changed. (Microsoft SQL Server, Error: 957)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=957&LinkId=20476
——————————
BUTTONS:
OK
——————————

Blog44_1.PNG

-> It was unusual to see a GUID as a database name in the message.

-> I checked this further to see if I can understand what this GUID is and tried querying the sys.databases to see if I have some details.

-> The sys.databases has a column called physical_database_name that holds this value.

Blog44_2.PNG

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.

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.

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.