Automate SFTP with SSIS – Part 4 – Creating SSIS Package

Automate SFTP with SSIS – Part 4 – Creating SSIS Package

  1. Create an Integration Service Package and name it winscpScreenShot001
  2. Create to Script Tasks
    ScreenShot002
  3. Add 4 package level variables and provide values
    ScreenShot003
  4. The value for sshHostKeyFingerPrint would the value copied when connecting to the SFTP server for the first time using winscp client as explained in part 3 point 2.
    ScreenShot008
  5. Double click the upload script task and make available the package level variables as read only variables
    ScreenShot004
  6. Double click the download task and make available the package level variables as read only variables.
  7. Double click the upload files script task, and edit the script
    ScreenShot005
  8. At the script editor add reference to the winscpnet.dll
    ScreenShot007
    ScreenShot006
  9. Similarly add reference to the download task as well.
  10. Double click on upload task, click on edit script and replace the script. Ensure to retain the original namespace line.  The script should be updated for the folder path.
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
    using System.AddIn;
    using WinSCP;
    
    
    namespace ST_c8db38a05a7d457bb96729ea03fbf461.csproj
    {
        [AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : VSTARTScriptObjectModelBase
        {
            public void Main()
            {
                // Setup session options
                SessionOptions sessionOptions = new SessionOptions
                {
                    Protocol = Protocol.Sftp,
                    // To setup these variables, go to SSIS > Variables.
                    // To make them accessible from the script task, in the context menu of the task,
                    // choose Edit. On the Script task editor on Script page, select ReadOnlyVariables,
                    // and tick the below properties.
                    HostName = (string)Dts.Variables["User::HostName"].Value,
                    UserName = (string)Dts.Variables["User::UserName"].Value,
                    Password = (string)Dts.Variables["User::Password"].Value,
                    SshHostKeyFingerprint = (string)Dts.Variables["User::SshHostKeyFingerprint"].Value
                };
    
                try
                {
                    using (Session session = new Session())
                    {
                        // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
                        // you need to set path to WinSCP.exe explicitly, if using non-default location.
                        session.ExecutablePath = @"c:\Program Files (x86)\WinSCP\WinSCP.exe";
    
                        // Connect
                        session.Open(sessionOptions);
    
                        // Upload files
                        TransferOptions transferOptions = new TransferOptions();
                        transferOptions.TransferMode = TransferMode.Binary;
    
                        TransferOperationResult transferResult;
                        transferResult = session.PutFiles(@"F:\SFTP\Outgoing\*", "/Incomming/", true, transferOptions);
    
                        // Throw on any error
                        transferResult.Check();
    
                        // Print results
                        bool fireAgain = false;
                        foreach (TransferEventArgs transfer in transferResult.Transfers)
                        {
                            Dts.Events.FireInformation(0, null,
                                string.Format("Upload of {0} succeeded", transfer.FileName),
                                null, 0, ref fireAgain);
                        }
    
                    }
    
                    Dts.TaskResult = (int)DTSExecResult.Success;
                }
                catch (Exception e)
                {
                    Dts.Events.FireError(0, null,
                        string.Format("Error when using WinSCP to upload files: {0}", e),
                        null, 0);
    
                    Dts.TaskResult = (int)DTSExecResult.Failure;
                }
            }
        }
    
    }
  11.  Similarly update the script for the download script task.  Ensure to retain the original namespace line.  Update scripts for folder paths.
    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
    using System.AddIn;
    using WinSCP;
    
    namespace ST_85cb36206d6549bc9cca1653725a7ea8.csproj
    {
        [AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : VSTARTScriptObjectModelBase
        {
            public void Main()
            {
                // Setup session options
                SessionOptions sessionOptions = new SessionOptions
                {
                    Protocol = Protocol.Sftp,
                    // To setup these variables, go to SSIS > Variables.
                    // To make them accessible from the script task, in the context menu of the task,
                    // choose Edit. On the Script task editor on Script page, select ReadOnlyVariables,
                    // and tick the below properties.
                    HostName = (string)Dts.Variables["User::HostName"].Value,
                    UserName = (string)Dts.Variables["User::UserName"].Value,
                    Password = (string)Dts.Variables["User::Password"].Value,
                    SshHostKeyFingerprint = (string)Dts.Variables["User::SshHostKeyFingerprint"].Value
                };
    
                try
                {
                    using (Session session = new Session())
                    {
                        // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
                        // you need to set path to WinSCP.exe explicitly, if using non-default location.
                        session.ExecutablePath = @"c:\Program Files (x86)\WinSCP\WinSCP.exe";
    
                        // Connect
                        session.Open(sessionOptions);
    
                        // Download files
                        TransferOptions transferOptions = new TransferOptions();
                        transferOptions.TransferMode = TransferMode.Binary;
    
                        TransferOperationResult transferResult;
                        transferResult = session.GetFiles("/Outgoing/*", @"F:\SFTP\Incomming\*", true, transferOptions);
    
                        // Throw on any error
                        transferResult.Check();
    
                        // Print results
                        bool fireAgain = false;
                        foreach (TransferEventArgs transfer in transferResult.Transfers)
                        {
                            Dts.Events.FireInformation(0, null,
                                string.Format("Upload of {0} succeeded", transfer.FileName),
                                null, 0, ref fireAgain);
                        }
    
                    }
    
                    Dts.TaskResult = (int)DTSExecResult.Success;
                }
                catch (Exception e)
                {
                    Dts.Events.FireError(0, null,
                        string.Format("Error when using WinSCP to upload files: {0}", e),
                        null, 0);
    
                    Dts.TaskResult = (int)DTSExecResult.Failure;
                }
            }
        }
    }
  12.  Link the packages to execute the upload first and download second.
  13. Save the package and test package execution.

See Also:
Part 1 : Automate SFTP file operation using SSIS
Part 2 : Setting up Free SFTP Server
Part 3 : Testing the SFTP Server from a client
Part 4 : Creating a SSIS Package to automate SFTP File operations
Part 5 : Scheduling the SSIS Package
Part 6 : Save SFTP incoming files info to SQL table
Part 7 : Logging SSIS Package a>

Cheers!
Sanjay

 

 

 

 

 

Collect MS SQL Server basic configuration info

select @@SERVERNAME
go
select @@VERSION
go
xp_msver
go
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 
go
SELECT * FROM sys.dm_os_cluster_nodes 
go
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName] 
Go
SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Cheers!
Sanjay

Regain Lost Access to SQL Server 2008 R2

Log on the server hosting the SQL Server as administrator

Stop all  SQL services if SQL services are running.

SQL Server 2008 R2 Configuration Manager
SQL Server 2008 R2 Configuration Manager
SQL Services Stopped

SQL Services Stopped

Start a command prompt as administrator.

Command prompt

Command prompt

Change the directory to SQL Server Bin folder:
In SQL server 2008 r2 the path would be “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

Start the SQL Server in single user mode:
Type SQLServr.Exe –m at command prompt

sql server single user mode

Starting SQL server in single user mode

Start another command prompt as administrator

Change directory to SQL server bin folder.

Connect to SQL server as admin
SQLCMD –S <Server_Name\Instance_Name>

SQLCMD

SQLCMD

Create a new login:
CREATE LOGIN <Login_Name> with PASSWORD=’<Password>’
GO

CreateLogin

Create Login

Assign sysadmin role:
SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’

Add sysadmin role

Add sysadmin role

Exit the SQLCMD Command prompt
Type Exit press Enter   (Get out of SQL prompt)
Type Exit press Enter   (Get out of Command prompt)

Exit the sqlservr.exe Command prompt
Type Control C
Do you wish to shutdown SQL Server (y/n)?  –> y
Type Exit press Enter

Start all SQL Services at SQL Configurations Manager

Login using new admin account!

Cheers!

Sanjay

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Decrypt encrypted stored procedures.

 Source : http://jongurgul.com/blog/sql-object-decryption/

Connect to SSMS with a dedicated admin connection.

EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

Open SSMS, choose File, New, Database Engine Query and set your servername to be admin:ServerName

DECLARE @EncObj VARBINARY(MAX),@DummyEncObj VARBINARY(MAX),@ObjectNameStmTemplate NVARCHAR(MAX)
SET NOCOUNT ON
/*
--You must be using a DAC.
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e
on (ec.[endpoint_id]=e.[endpoint_id])
WHERE e.[name]='Dedicated Admin Connection'
AND ec.[session_id] = @@SPID
*/
USE [master] --change to where your encrypted object resides
DECLARE @object_id INT,@name SYSNAME
SELECT @object_id = [object_id],@name = [name] 
FROM sys.all_objects 
WHERE name = N'jjj' --<=Either put your object name here or make sure @object_id is set, and that the object it relates to is encrypted.

SELECT TOP 1 
 @ObjectNameStmTemplate = [ObjectStmTemplate]
,@EncObj = [imageval]
FROM
(
SELECT
SPACE(1)+
(
CASE WHEN [type] = 'P' THEN N'PROCEDURE'
WHEN [type] = 'V' THEN 'VIEW'
WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION'
WHEN [type] IN ('TR') THEN N'TRIGGER'
ELSE [type]
END
)
+SPACE(1)+QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME(ao.[name])+SPACE(1)+
(
CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS'
WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC'
WHEN [type] IN ('FN') THEN N'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END'
WHEN [type] IN ('TF') THEN N'() RETURNS @t TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END'
WHEN [type] IN ('IF') THEN N'() RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 1 N'
WHEN [type] IN ('TR') THEN N' ON ' + OBJECT_NAME(ao.[parent_object_id]) + ' WITH ENCRYPTION FOR DELETE AS SELECT 1 N'
ELSE [type]
END
) +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
,sov.[imageval]
FROM sys.all_objects ao
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND ao.[Object_id] = sov.[objid]
WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF','TR') AND ao.[object_id] = @object_id
UNION ALL
--Server Triggers
SELECT SPACE(1)+'TRIGGER'+SPACE(1)+QUOTENAME(st.[name])+SPACE(1)+N'ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1'
 +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
,sov.[imageval] 
FROM sys.server_triggers st
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND st.[object_id] = sov.[objid] WHERE st.[object_id] = @object_id
--Database Triggers
UNION ALL
SELECT SPACE(1)+'TRIGGER'+SPACE(1)+QUOTENAME(dt.[name])+SPACE(1)+N'ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1'
 +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
,sov.[imageval] 
FROM sys.triggers dt
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND dt.[object_id] = sov.[objid] AND dt.[parent_class_desc] = 'DATABASE' WHERE dt.[object_id] = @object_id
) x([ObjectStmTemplate],[imageval])

--Alter the existing object, then revert so that we have the dummy object encrypted value
BEGIN TRANSACTION
	DECLARE @sql NVARCHAR(MAX)
	SET @sql = N'ALTER'+@ObjectNameStmTemplate
	EXEC sp_executesql @sql
	SELECT @DummyEncObj = sov.[imageval]
	FROM sys.all_objects ao
	INNER JOIN sys.sysobjvalues sov ON sov.[valclass]=1 AND ao.[Object_id]=sov.[objid]
	WHERE ao.[object_id] = @object_id
ROLLBACK TRANSACTION

DECLARE @Final NVARCHAR(MAX)
SET @Final = N''
DECLARE @Pos INT
SET @Pos = 1
WHILE @Pos <= DATALENGTH(@EncObj)/2
BEGIN
	SET @Final = @Final + NCHAR(UNICODE(SUBSTRING(CAST(@EncObj AS NVARCHAR(MAX)),@Pos,1))^(UNICODE(SUBSTRING(N'CREATE'+@ObjectNameStmTemplate,@Pos,1))^UNICODE(SUBSTRING(CAST(@DummyEncObj AS NVARCHAR(MAX)),@Pos,1))))
	SET @Pos = @Pos + 1
END

--If the object is small then just print, else print in chunks
IF DATALENGTH(@Final) <= 8000
BEGIN
	PRINT '--SMALL--'
	PRINT @Final
END
ELSE
BEGIN
	PRINT '--BIG--'
	DECLARE @c INT
	SET @c = 0
	WHILE @c <= (DATALENGTH(@Final)/8000)
	BEGIN
		PRINT SUBSTRING(@Final,@c+(@c*4000),4000)
		SET @c = @c + 1
	END
END

How to start logging Replicating Jobs

1. Launch the replication monitor

replication monitor

2. Expand the publications, select the publication which needs to logged

3. Double click the article at the all subscriptions windowrunning

4. At the subscription window, select action -> Log reader agent job propertieslog reader agent properites

5. At the agent job properties window select steps, and select the run agent job at job list.  Click on edit.ScreenShot

6. Add the logging command to the script

-Output [Full path to log file name at accessible by SQL server]

-Outputverboselevel 4

7. Stop and start the log reader agent.  Be sure the logging is stopped again!!!

Cheers!
Sanjay

 

 

 

 

 

 

TSQL – Replication Topology

Today while trouble shooting replication with MS Support, this script was used, found it useful.

SET NOCOUNT ON
GO
IF ((SELECT COUNT(*) FROM TEMPDB.SYS.TABLES WHERE NAME = '##CE') > 0)
DROP TABLE ##CE
GO
CREATE TABLE ##CE ([DESCRIPTION] VARCHAR(100) NOT NULL, [VALUE] VARCHAR(100) NOT NULL)
GO
INSERT INTO ##CE VALUES('Continue', 1)
GO
DECLARE @CONSOLEMSG VARCHAR(1000)
DECLARE @SQLVersion VARCHAR(2)
SET @SQLVersion = CONVERT(VARCHAR(2), SERVERPROPERTY('ProductVersion'))
IF SUBSTRING(@SQLVersion, 2, 1) = '.'
SET @SQLVersion = SUBSTRING(@SQLVersion, 1, 1)
IF CONVERT(INT, @SQLVersion) < 9
BEGIN
SET @CONSOLEMSG=CONVERT(VARCHAR(24),GETDATE(),121)+ ' SQL Server connected to is not SQL Server 2005 or SQL Server 2008. Exiting.'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
UPDATE ##CE SET [VALUE] = 0 WHERE [DESCRIPTION] = 'Continue'
END
GO
IF ((SELECT [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Continue') = 1)
BEGIN
DECLARE @CONSOLEMSG VARCHAR(1000)
DECLARE @DistInst VARCHAR(1)
DECLARE @BitEd VARCHAR(100)
DECLARE @xIndex INT
SELECT @DistInst = CONVERT(VARCHAR(1), ISNULL([IS_DISTRIBUTOR], 0)) FROM [MASTER].[SYS].[SERVERS] (NOLOCK) WHERE [NAME] = 'REPL_DISTRIBUTOR' AND [DATA_SOURCE] = CONVERT(SYSNAME, SERVERPROPERTY('ServerName'))
IF @DistInst IS NULL OR @DistInst = '0'
BEGIN
SET @CONSOLEMSG=CONVERT(VARCHAR(24),GETDATE(),121)+ ' Selected instance is not a distributor instance. Exiting.'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
UPDATE ##CE SET [VALUE] = 0 WHERE [DESCRIPTION] = 'Continue'
END
ELSE
BEGIN
SET @CONSOLEMSG = REPLACE(CONVERT(VARCHAR(256), SERVERPROPERTY('ServerName')) + ' (DISTRIBUTOR :: ' + CONVERT(VARCHAR(10), SERVERPROPERTY('ProductVersion')) + ')', '.)', '')
SELECT @BitEd = @@VERSION
SELECT @xIndex = CHARINDEX('X', UPPER(@BitEd))
SELECT @BitEd = SUBSTRING(UPPER(@BitEd), @xIndex, 3)
SET @CONSOLEMSG = @CONSOLEMSG + ' ' + @BitEd + ')'
INSERT INTO ##CE VALUES('Distributor', @CONSOLEMSG)
END
END
GO
IF ((SELECT [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Continue') = 1)
BEGIN
DECLARE @CONSOLEMSG VARCHAR(1000)
SET @CONSOLEMSG = '============================================================='
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SET @CONSOLEMSG = ' REPLICATION TOPOLOGY'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SET @CONSOLEMSG = '============================================================='
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SET @CONSOLEMSG = 'SELECT THE PUBLICATION-SUBSCRIPTION PAIR FOR SCOPING THE CASE'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SET @CONSOLEMSG = '============================================================='
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SET @CONSOLEMSG = ' '
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
END
GO
IF ((SELECT [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Continue') = 1)
BEGIN
DECLARE @CONSOLEMSG VARCHAR(1000)
DECLARE @DISTRIBUTIONDBNAME SYSNAME
DECLARE @CURRENTDATABASE SYSNAME
SELECT @DISTRIBUTIONDBNAME = NAME FROM SYS.DATABASES (NOLOCK) WHERE IS_DISTRIBUTOR = 1
SELECT @CONSOLEMSG = [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Distributor'
SET @CONSOLEMSG = @CONSOLEMSG + ' (Distribution Database: ' + @DISTRIBUTIONDBNAME + ')'
DELETE ##CE WHERE [DESCRIPTION] = 'Distributor'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SELECT @CURRENTDATABASE = DB_NAME()
IF @CURRENTDATABASE <> @DISTRIBUTIONDBNAME
BEGIN
SET @CONSOLEMSG = ' Context Database is not the Distribution Database. Exiting.'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
UPDATE ##CE SET [VALUE] = 0 WHERE [DESCRIPTION] = 'Continue'
END
END
GO
IF ((SELECT [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Continue') = 1)
BEGIN
DECLARE @CONSOLEMSG VARCHAR(1000)
DECLARE @DISTRIBUTORSERVERNAME SYSNAME
DECLARE @PUBLISHERNAME SYSNAME
DECLARE @PUBLISHERID INT
DECLARE @PUBLISHERNUMBER INT
DECLARE @PUBLICATIONAME SYSNAME
DECLARE @PUBLICATIONID INT
DECLARE @PUBLICATIONTYPE INT
DECLARE @PUBLICATIONDATABASE SYSNAME
DECLARE @ALLOW_QUEUED_TRAN INT
DECLARE @STMT VARCHAR(MAX)
DECLARE @NUMARTICLES INT
DECLARE @RESERVEDSIZE BIGINT
DECLARE @USEDSIZE BIGINT
DECLARE @INDEXSIZE BIGINT
DECLARE @SUBSCRIBERNAME SYSNAME
DECLARE @SUBSCRIPTIONDB SYSNAME
DECLARE @SUBSCRIPTIONTYPE INT
SET @PUBLISHERNUMBER = 0
SET @DISTRIBUTORSERVERNAME = CONVERT(SYSNAME, SERVERPROPERTY('ServerName'))
SET @CONSOLEMSG = ' |- PUBLISHERS'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
DECLARE PUBLISHERCURSOR CURSOR LOCAL READ_ONLY FOR
SELECT DISTINCT S.NAME, PUB.PUBLISHER_ID FROM SYS.SERVERS (NOLOCK) S JOIN DBO.MSPUBLICATIONS (NOLOCK) PUB
ON S.SERVER_ID = PUB.PUBLISHER_ID
OPEN PUBLISHERCURSOR
FETCH NEXT FROM PUBLISHERCURSOR INTO @PUBLISHERNAME, @PUBLISHERID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PUBLISHERNUMBER = @PUBLISHERNUMBER + 1
SET @CONSOLEMSG = ' |- ' + @PUBLISHERNAME + ' (Publisher ' + CONVERT(VARCHAR(10), @PUBLISHERNUMBER) + ')'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SET @CONSOLEMSG = ' |- PUBLICATIONS'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
DECLARE PUBLICATIONCURSOR CURSOR LOCAL READ_ONLY FOR
SELECT PUBLICATION, PUBLICATION_ID, PUBLICATION_TYPE, PUBLISHER_DB, ALLOW_QUEUED_TRAN
FROM DBO.MSPUBLICATIONS (NOLOCK) WHERE PUBLISHER_ID = @PUBLISHERID
OPEN PUBLICATIONCURSOR
FETCH NEXT FROM PUBLICATIONCURSOR INTO @PUBLICATIONAME, @PUBLICATIONID, 
@PUBLICATIONTYPE, @PUBLICATIONDATABASE, @ALLOW_QUEUED_TRAN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CONSOLEMSG = ' |- ' + @PUBLICATIONAME + ' ('
SET @CONSOLEMSG = @CONSOLEMSG + 'Publication ID: ' + CONVERT(VARCHAR(10), @PUBLICATIONID) + '; '
IF @PUBLICATIONTYPE = 0
BEGIN
IF @ALLOW_QUEUED_TRAN = 0
SET @CONSOLEMSG = @CONSOLEMSG + 'Publication type: Transactional (1-way); '
ELSE
SET @CONSOLEMSG = @CONSOLEMSG + 'Publication type: Transactional (2-way); '
END
ELSE IF @PUBLICATIONTYPE = 1
SET @CONSOLEMSG = @CONSOLEMSG + 'Publication type: Snapshot; '
ELSE IF @PUBLICATIONTYPE = 2
SET @CONSOLEMSG = @CONSOLEMSG + 'Publication type: Merge; '
SET @CONSOLEMSG = @CONSOLEMSG + 'Publication database: ' + @PUBLICATIONDATABASE + ')'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SET @CONSOLEMSG = 'XXX'
IF @PUBLICATIONTYPE < 2
BEGIN
SET @CONSOLEMSG = ' |- ARTICLES'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SELECT @NUMARTICLES = COUNT(ARTICLE_ID) FROM MSARTICLES (NOLOCK) WHERE PUBLICATION_ID = @PUBLICATIONID AND PUBLISHER_DB = @PUBLICATIONDATABASE
SET @CONSOLEMSG = ' |- ' + CONVERT(VARCHAR(10), @NUMARTICLES) + ' article(s)'
END
ELSE
BEGIN
IF @DISTRIBUTORSERVERNAME = @PUBLISHERNAME
BEGIN
SET @CONSOLEMSG = ' |- ARTICLES'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SET @STMT = 'SET NOCOUNT ON' + CHAR(13)
SET @STMT = @STMT + 'DECLARE @NUMART INT' + CHAR(13)
SET @STMT = @STMT + 'SELECT @NUMART = COUNT(MA.OBJID) FROM ' + @PUBLICATIONDATABASE + '.DBO.SYSMERGEARTICLES (NOLOCK) MA JOIN ' + @PUBLICATIONDATABASE + '.DBO.SYSMERGEPUBLICATIONS (NOLOCK) MP ON MA.PUBID = MP.PUBID WHERE MP.PUBLISHER_DB = ''' + @PUBLICATIONDATABASE + ''' AND MP.NAME = ''' + @PUBLICATIONAME + '''' + CHAR(13)
SET @STMT = @STMT + 'INSERT INTO ##CE VALUES (''NUMART'', @NUMART)' + CHAR(13)
EXEC (@STMT)
SELECT @NUMARTICLES = [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'NUMART'
DELETE ##CE WHERE [DESCRIPTION] = 'NUMART'
SET @CONSOLEMSG = ' |- ' + CONVERT(VARCHAR(10), @NUMARTICLES) + ' article(s)'
END
END
IF @DISTRIBUTORSERVERNAME = @PUBLISHERNAME
BEGIN
IF @PUBLICATIONTYPE < 2
BEGIN
SET @STMT = 'SET NOCOUNT ON' + CHAR(13)
SET @STMT = @STMT + 'DECLARE @Reserved BIGINT' + CHAR(13)
SET @STMT = @STMT + 'DECLARE @Used BIGINT' + CHAR(13)
SET @STMT = @STMT + 'DECLARE @Index BIGINT' + CHAR(13)
SET @STMT = @STMT + 'SELECT @Reserved = SUM([Reserved Size (KB)]),' + CHAR(13)
SET @STMT = @STMT + '@Used = SUM([Used Size (KB)]),' + CHAR(13)
SET @STMT = @STMT + '@Index = SUM([Index Size (KB)])' + CHAR(13)
SET @STMT = @STMT + 'FROM (SELECT SUM([PS].[Reserved_Page_Count]) * 8 AS [Reserved Size (KB)],' + CHAR(13)
SET @STMT = @STMT + ' SUM([PS].[Used_Page_Count]) * 8 AS [Used Size (KB)],' + CHAR(13)
SET @STMT = @STMT + ' SUM(' + CHAR(13)
SET @STMT = @STMT + ' CASE' + CHAR(13)
SET @STMT = @STMT + ' WHEN ([PS].[index_id] < 2) THEN ([PS].[in_row_data_page_count] + [PS].[lob_used_page_count] + [PS].[row_overflow_used_page_count])' + CHAR(13)
SET @STMT = @STMT + ' ELSE [PS].[lob_used_page_count] + [PS].[row_overflow_used_page_count]' + CHAR(13)
SET @STMT = @STMT + ' END' + CHAR(13)
SET @STMT = @STMT + ' ) * 8 AS [Index Size (KB)]' + CHAR(13)
SET @STMT = @STMT + 'FROM [MSArticles] [MA] (NOLOCK)' + CHAR(13)
SET @STMT = @STMT + 'JOIN ' + @PUBLICATIONDATABASE + '.DBO.[SysArticles] [SA] (NOLOCK)' + CHAR(13)
SET @STMT = @STMT + 'ON [SA].[artid] = [MA].[article_id]' + CHAR(13)
SET @STMT = @STMT + 'JOIN ' + @PUBLICATIONDATABASE + '.[sys].[dm_db_Partition_Stats] [PS] (NOLOCK)' + CHAR(13)
SET @STMT = @STMT + 'ON [PS].[object_id] = [SA].[objid]' + CHAR(13)
SET @STMT = @STMT + 'WHERE [MA].[publisher_id] = ' + CONVERT(VARCHAR(10), @PUBLISHERID) + CHAR(13)
SET @STMT = @STMT + 'AND [MA].[publication_id] = ' + CONVERT(VARCHAR(10), @PUBLICATIONID) + CHAR(13)
SET @STMT = @STMT + 'GROUP BY [SA].[objid], [MA].[source_owner], [MA].[article]) A' + CHAR(13)
SET @STMT = @STMT + 'INSERT INTO ##CE VALUES (''Reserved'', @Reserved)' + CHAR(13)
SET @STMT = @STMT + 'INSERT INTO ##CE VALUES (''Used'', @Used)' + CHAR(13)
SET @STMT = @STMT + 'INSERT INTO ##CE VALUES (''Index'', @Index)' + CHAR(13)
EXEC (@STMT)
SELECT @RESERVEDSIZE = [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Reserved'
SELECT @USEDSIZE = [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Used'
SELECT @INDEXSIZE = [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Index'
SET @CONSOLEMSG = @CONSOLEMSG + '; Reserved Space = ' + CONVERT(VARCHAR(20), @RESERVEDSIZE) + ' KB, '
SET @CONSOLEMSG = @CONSOLEMSG + 'Used Space = ' + CONVERT(VARCHAR(20), @USEDSIZE) + ' KB, '
SET @CONSOLEMSG = @CONSOLEMSG + 'Index Space = ' + CONVERT(VARCHAR(20), @INDEXSIZE) + ' KB'
DELETE ##CE WHERE [DESCRIPTION] IN ('Reserved', 'Used', 'Index')
END
ELSE
BEGIN
SET @STMT = 'SET NOCOUNT ON' + CHAR(13)
SET @STMT = @STMT + 'DECLARE @Reserved BIGINT' + CHAR(13)
SET @STMT = @STMT + 'DECLARE @Used BIGINT' + CHAR(13)
SET @STMT = @STMT + 'DECLARE @Index BIGINT' + CHAR(13)
SET @STMT = @STMT + 'SELECT @Reserved = SUM([Reserved Size (KB)]),' + CHAR(13)
SET @STMT = @STMT + '@Used = SUM([Used Size (KB)]),' + CHAR(13)
SET @STMT = @STMT + '@Index = SUM([Index Size (KB)])' + CHAR(13)
SET @STMT = @STMT + 'FROM (SELECT SUM([PS].[Reserved_Page_Count]) * 8 AS [Reserved Size (KB)],' + CHAR(13)
SET @STMT = @STMT + ' SUM([PS].[Used_Page_Count]) * 8 AS [Used Size (KB)],' + CHAR(13)
SET @STMT = @STMT + ' SUM(' + CHAR(13)
SET @STMT = @STMT + ' CASE' + CHAR(13)
SET @STMT = @STMT + ' WHEN ([PS].[index_id] < 2) THEN ([PS].[in_row_data_page_count] + [PS].[lob_used_page_count] + [PS].[row_overflow_used_page_count])' + CHAR(13)
SET @STMT = @STMT + ' ELSE [PS].[lob_used_page_count] + [PS].[row_overflow_used_page_count]' + CHAR(13)
SET @STMT = @STMT + ' END' + CHAR(13)
SET @STMT = @STMT + ' ) * 8 AS [Index Size (KB)]' + CHAR(13)
SET @STMT = @STMT + 'FROM ' + @PUBLICATIONDATABASE + '.DBO.SYSMERGEARTICLES MA (NOLOCK) JOIN ' + @PUBLICATIONDATABASE + '.DBO.SYSMERGEPUBLICATIONS (NOLOCK) MP ON MA.PUBID = MP.PUBID' + CHAR(13)
SET @STMT = @STMT + 'JOIN ' + @PUBLICATIONDATABASE + '.[sys].[dm_db_Partition_Stats] [PS] (NOLOCK) ON [PS].[object_id] = [MA].[OBJID]' + CHAR(13)
SET @STMT = @STMT + 'WHERE MP.PUBLISHER_DB = ''' + @PUBLICATIONDATABASE + ''' AND MP.NAME = ''' + @PUBLICATIONAME + ''') A' + CHAR(13)
SET @STMT = @STMT + 'INSERT INTO ##CE VALUES (''Reserved'', @Reserved)' + CHAR(13)
SET @STMT = @STMT + 'INSERT INTO ##CE VALUES (''Used'', @Used)' + CHAR(13)
SET @STMT = @STMT + 'INSERT INTO ##CE VALUES (''Index'', @Index)' + CHAR(13)
EXEC (@STMT)
SELECT @RESERVEDSIZE = [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Reserved'
SELECT @USEDSIZE = [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Used'
SELECT @INDEXSIZE = [VALUE] FROM ##CE WHERE [DESCRIPTION] = 'Index'
SET @CONSOLEMSG = @CONSOLEMSG + '; Reserved Space = ' + CONVERT(VARCHAR(20), @RESERVEDSIZE) + ' KB, '
SET @CONSOLEMSG = @CONSOLEMSG + 'Used Space = ' + CONVERT(VARCHAR(20), @USEDSIZE) + ' KB, '
SET @CONSOLEMSG = @CONSOLEMSG + 'Index Space = ' + CONVERT(VARCHAR(20), @INDEXSIZE) + ' KB'
DELETE ##CE WHERE [DESCRIPTION] IN ('Reserved', 'Used', 'Index')
END
END
IF @CONSOLEMSG <> 'XXX'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
SET @CONSOLEMSG = ' |- SUBSCRIPTIONS'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
IF @PUBLICATIONTYPE < 2
BEGIN
DECLARE SUBSCRIPTIONCURSOR CURSOR LOCAL READ_ONLY FOR
SELECT DISTINCT S.NAME, SUB.SUBSCRIBER_DB, SUB.SUBSCRIPTION_TYPE
FROM SYS.SERVERS S (NOLOCK) JOIN MSSUBSCRIPTIONS SUB (NOLOCK) ON S.SERVER_ID = SUB.SUBSCRIBER_ID
WHERE SUB.PUBLICATION_ID = @PUBLICATIONID AND SUB.PUBLISHER_DB = @PUBLICATIONDATABASE AND SUB.SUBSCRIBER_ID >= 0
OPEN SUBSCRIPTIONCURSOR
FETCH NEXT FROM SUBSCRIPTIONCURSOR INTO @SUBSCRIBERNAME, @SUBSCRIPTIONDB, @SUBSCRIPTIONTYPE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CONSOLEMSG = ' |- ' + @SUBSCRIBERNAME + ' ('
SET @CONSOLEMSG = @CONSOLEMSG + 'Subscription Database: ' + @SUBSCRIPTIONDB + '; '
IF @SUBSCRIPTIONTYPE = 0
SET @CONSOLEMSG = @CONSOLEMSG + 'Subscription Type: Push)'
ELSE IF @SUBSCRIPTIONTYPE = 1
SET @CONSOLEMSG = @CONSOLEMSG + 'Subscription Type: Pull)'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
FETCH NEXT FROM SUBSCRIPTIONCURSOR INTO @SUBSCRIBERNAME, @SUBSCRIPTIONDB, @SUBSCRIPTIONTYPE
END
CLOSE SUBSCRIPTIONCURSOR
DEALLOCATE SUBSCRIPTIONCURSOR
END
ELSE
BEGIN
DECLARE SUBSCRIPTIONCURSOR CURSOR LOCAL READ_ONLY FOR
SELECT SUBSCRIBER, SUBSCRIBER_DB, SUBSCRIPTION_TYPE
FROM MSMERGE_SUBSCRIPTIONS (NOLOCK) WHERE PUBLICATION_ID = @PUBLICATIONID AND PUBLISHER_DB = @PUBLICATIONDATABASE
OPEN SUBSCRIPTIONCURSOR
FETCH NEXT FROM SUBSCRIPTIONCURSOR INTO @SUBSCRIBERNAME, @SUBSCRIPTIONDB, @SUBSCRIPTIONTYPE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CONSOLEMSG = ' |- ' + @SUBSCRIBERNAME + ' ('
SET @CONSOLEMSG = @CONSOLEMSG + 'Subscription Database: ' + @SUBSCRIPTIONDB + '; '
IF @SUBSCRIPTIONTYPE = 0
SET @CONSOLEMSG = @CONSOLEMSG + 'Subscription Type: Push)'
ELSE IF @SUBSCRIPTIONTYPE = 1
SET @CONSOLEMSG = @CONSOLEMSG + 'Subscription Type: Pull)'
RAISERROR (@CONSOLEMSG,10,1) WITH NOWAIT
FETCH NEXT FROM SUBSCRIPTIONCURSOR INTO @SUBSCRIBERNAME, @SUBSCRIPTIONDB, @SUBSCRIPTIONTYPE
END
CLOSE SUBSCRIPTIONCURSOR
DEALLOCATE SUBSCRIPTIONCURSOR
END
FETCH NEXT FROM PUBLICATIONCURSOR INTO @PUBLICATIONAME, @PUBLICATIONID, 
@PUBLICATIONTYPE, @PUBLICATIONDATABASE, @ALLOW_QUEUED_TRAN
END
CLOSE PUBLICATIONCURSOR
DEALLOCATE PUBLICATIONCURSOR
FETCH NEXT FROM PUBLISHERCURSOR INTO @PUBLISHERNAME, @PUBLISHERID
END
CLOSE PUBLISHERCURSOR
DEALLOCATE PUBLISHERCURSOR
END
GO
DROP TABLE ##CE
GO

 

 

TSQL – Monitor Blocking Transctions

Today while trouble shooting distribution issues with MS Support, this script was use, found it useful.

SELECT   s.session_id, 
                 r.status, 
                 r.blocking_session_id                                 'Blk by', 
                 r.wait_type, 
                 wait_resource, 
                 r.wait_time / (1000.0)                             'Wait Sec', 
                 r.cpu_time, 
                 r.logical_reads, 
                 r.reads, 
				 r.writes, 
                 r.total_elapsed_time / (1000.0)                    'Elaps Sec', 
				 db_name(r.database_id) as DBName,
                 '"'+Substring(st.TEXT,(r.statement_start_offset / 2) + 1, 
                           ((CASE r.statement_end_offset 
                               WHEN -1 
                               THEN Datalength(st.TEXT) 
                               ELSE r.statement_end_offset 
                             END - r.statement_start_offset) / 2) + 1)+'"' AS statement_text, 
                 '"'+Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)), 
                          '')+'"' AS command_text, 
                 r.command, 
                 s.login_name, 
                 s.host_name, 
                 s.program_name, 
                 s.last_request_end_time, 
                 s.login_time, 
                 r.open_transaction_count 
        FROM     sys.dm_exec_sessions AS s 
                 JOIN sys.dm_exec_requests AS r 
                   ON r.session_id = s.session_id 
                 CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
        WHERE    r.session_id != @@SPID
        ORDER BY r.cpu_time desc, r.status, 
                 r.blocking_session_id, 
                 s.session_id

Transactions should come in and get out pretty fast. How ever noticed that the Replication Monitoring Refresher runs continuously with a suspended status and wait type of waitfor which pretty much stays which should be fine.