How to change Integration Services Catalog (SSISDB) database Master Key encryption password?

To change the Integration Services Catalog (SSISDB) database Master Key encryption password, run the following Transact-SQL statement:

USE [SSISDB];
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = N'[old_password]'; -- Password used when creating SSISDB
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = N'[new_password]';
GO

In the above Transact-SQL fragment, we first open the Integration Services Catalog (SSISDB) database Master Key with the existing password, and then regenerated it with a new one.

Advertisements

Database Issue: The transaction log for database ‘db_name’ is full due to ‘ACTIVE_BACKUP_OR_RESTORE’.

Today, I was called in the early hours because the transaction log of one of the SQL Server 2016 database was full. I logged onto the SQL server instance via SQL Server Management Studio, and saw the following errors in the SQL Server error log:

The transaction log for database 'OLTPDB' is full
due to 'ACTIVE_BACKUP_OR_RESTORE'.

Reason for failure

This occurs because the full backup for the ‘OLTPDB’ database was still running, and as a result, the transaction log of this database was not freed up for reuse. This caused the transactions that were running against this database to fail.

Issue resolution

As we know, the portion of the transaction log file that is used during the full backup operation must be maintained while the full backup operation is running, which will also be backed up at the end of the full backup operation. That’s because the full database backup contains all the database data at the start of the full backup operation plus the transition log used until the end of the full backup operation. Therefore, to fix the issue, I had to manually increase the size of the transaction log file, to accommodate the high load against the database.

An Overview on SQL Server Checkpoints

checkpoint

Commercial database systems like SQL server have many recovery mechanisms to restore data hardware or software failure. Checkpoints are part of such recovery mechanism.SQL Server Database Engine is programmed to perform changes or modifications to database pages in buffer cache (memory) first and after some processing the modification are written on the disk files. This “processing” involves Checkpoint which writes all the dirty pages existing on Buffer Cache to Physical disk. It also enters log records from buffer log to physical file. Checkpoint in SQL Server was introduced to reduce the time required for recovery during an unexpected shutdown or system failure. Database Engine issues a checkpoint for each database on regular intervals.

Various factors and conditions (depending upon the Recovery Interval settings done) commit a checkpoint to issue. It can be an execution of ALTER DATABASE command, manual execution of CHECKPOINT, Server clean shutdown, or even in case SQL database is in SIMPLE mode and its log is 70% full.

Talking about Checkpoints, a regular definition states that once a transaction is made checkpoint writes about it. But this is not true, checkpoint writes about all the pages which have been changed (marked dirty) since the last checkpoint. It doesn’t depend on transaction, whether it is committed or not. tempdb is an exception where data pages are not written to disk as a part of checkpoint. Below section will elaborate in detail, what exactly happens when checkpoint is triggered;

Working Operation of CHECKPOINT

Changes done in memory of respective databases are checked as per last checkpoint and then all the dirty pages of databases are written to the disk. It is independent of the state of the transaction which has made the changes. Then, all the log records along with the most recent log record describing changes made in the database are written to disk first before page is written to disk assuring the recovery can be done through this write-ahead logging.

Sequential entries are made to the log from all the transactions. And it is not possible to write selective records to disk. Thus, when a user writes a dirty page to disk even if having only single log record creating problem in it, all the log records prior to this log record will be written on the page.Later, generation of log records with information about checkpoints takes place.The LSN (Log Sequence Number) of checkpoint is recorded in boot page of that database in the dbi_checkptLSN field along with other critical information. If SIMPLE Recovery mode is assigned, the VLFs in log are checked if it is possible to mark them inactive. These tasks are independent of what type of CHECKPOINT has been made (Manual or Automatic).

Categories of SQL Server Checkpoints

Automatic

Automatic Checkpoint is the most common one, and it issues automatically in the background as per the settings done in Recovery Interval server configuration option. This Recovery Interval parameter is defined at server level. By default, this parameter value is 0(zero) in which target recovery interval is 1 minute. Automatic checkpoints are throttled on the basis of number of outstanding writes and on the fact whether Database Engine senses any rise in write latency above 20 milliseconds.

The following is the query to define the [recovery interval]:

USE [master];
GO

EXEC [sp_configure] '[recovery interval]', 'seconds'
GO;

Indirect

Indirect Checkpoints were added in SQL Server 2012 and this also runs in the background but the difference is it runs on the basis of user-specified target time for recovery for respective databases. If user has used ALTER DATABASE to set TARGET_RECOVERY_TIME as >0, it will be used overriding the Recovery Interval specified at server level completely, avoiding Automatic Checkpoint for that Database. It has been observed that Indirect checkpoint are faster and provide more predictable recovery time as compared to automatic checkpoints.

Recovery time required for database recovery is reduced when indirect checkpoints are preferred. This is done by factoring in cost of random I/O during a REDO operation. These checkpoints also reduceassociated I/O thwarting by continuously writing dirty pages to disk.

Here is the syntax of the query to produce indirect checkpoint:

USE [master];
GO

ALTER DATABASE … SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES}
GO;

Manual

As the name defines, this command runs like any other T-SQL statement and once issued it will run to its completion. It must be noted that Manual Checkpoint will run for the current database only. Checkpoint_Duration can also be defined in seconds at database level, which defines the time to complete checkpoint and is optional.

Here is the syntax to issue manual checkpoint:

USE [master];
GO

CHECKPOINT [ checkpoint_duration ]
GO;

Internal

User cannot control these Internal Checkpoints. This is issued by various server operations like; backup & database-snapshot creation ensuring that the images taken synchronize with the state of log. Following events will follow up with generation of internal checkpoints;

When databases are added or removed with ALTER DATABASE command. It is triggered when backup of a database is taken or database snapshot is created explicitly or internally. A clean Shutdown (Shutdown with NOWAIT) will also trigger internal checkpoint. Changes in Recovery Model from Full\Bulk-logged to Simple will also initiate internal checkpoint.

How Recovery-Interval Stimulates Recovery Performance?

Normally, default values are enough to provide optimum recovery performance.But changing the recovery interval can be practiced to improve performance. You can opt to change the interval time in some conditions like; if the recovery process takes longer than 1 minute when long-running transactions are not rolled back. Frequent checkpoints are ruining performance, as under high frequency of running checkpoints performance of SQL Server can drop due to heavy I/O activity.

In case recovery interval setting has to be changed, it is recommended to increase it slowly with small increments. You can examine the relative recovery performance with gradual increase in recovery interval. This will help you get effective results.

Conclusion:

Checkpoints are useful repository of information and serves best for recovery of SQL server databases. This article shows Checkpoint’s importance, relevance and categories of checkpoints. Practicing a better checkpoint method will not only enhance the server performance but it also renders a better recovery plan. Recovery Interval can be chosen as per I/O activities and significance of the databases; it must be up-to-date and at the same time should not hamper the performance of server. One thing must be considered that the checkpoint log records are never overwritten by successive checkpoints. It will be only overwritten when log wraps and VLFs (Virtual Log Files) are re-used.

About the author:

This is the guest post by Andrew Jackson, a US based SQL Server DBA, who is currently working for SysTools Group, a company that provides its services across a diverse range that includes data recovery, digital forensics, and cloud backup. 

He can be contacted through his blog or via LinkedIn.

SQL Server: Converting binary data to a hexadecimal string

Problem

I have a requirement to convert the binary data inside a table column or variable to a hexadecimal string. Is it possible to directly convert the binary data to a hexadecimal string characters?


Solution

Before SQL Server 2005, it was not possible to directly convert the binary data to a string of hexadecimal characters, however, since SQL Server 2005; you can do the binary to hexadecimal conversion with one of the following two Transact-SQL approaches:

Option 1: Using CONVERT function of Transact-SQL

In SQL Server 2008 and later versions, you can use the Transact-SQL CONVERT function with its one of the following two binary styles to convert the binary data into a hexadecimal string:

Convert_function_binary_style

Microsoft Reference: http://msdn.microsoft.com/en-GB/library/ms187928.aspx

The following is the basic syntax for CONVERT function:

CONVERT ( destination_data_type [ ( length ) ] , expression, style )

For example, suppose you want to convert the following binary value to hexadecimal string: 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00. To do that, you use the CONVERT function with binary styles as follows:

SET NOCOUNT ON;

DECLARE @BinaryValue VARBINARY(256) 

SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00

SELECT  CONVERT([varchar](512), @BinaryValue, 1)
SELECT  CONVERT([varchar](512), @BinaryValue, 2) 

SET NOCOUNT OFF;

It returns the following output:

Convert_function_binary_example_output

For more information, see CAST and CONVERT (Transact-SQL).

Option 2: Using xml value() Method with the XQUERY functions

In SQL Server 2005 and later versions, you can use xml value() method with the XQUERY functions to convert the binary data into a hexadecimal string. The xml value() method let you extract the value of a specified XML node as a scalar value and then converts the value to a specified SQL Server data type. You can use SQL Server XQuery Extension Functions and xs:hexBinary base type Constructor Function within a xml value() method to convert the binary data into a hexadecimal string. The SQL Server XQuery Extension Functions includes sql:column() and sql:variable() functions, which can be used to expose a relational value inside an XQuery expression. For example, sql:variable() Function exposes a variable that contains a SQL relational value inside an XQuery expression while sql:column() Function exposes a column that contains a SQL relational value inside an XQuery expression. The xs:hexBinary base type Constructor Function creates the hexadecimal character sequence.

The following is the basic syntax of the value() method with the XQUERY functions to convert the binary data into a hexadecimal string:

value(xs:hexBinary(sql:variable("VariableName") , SQLType)
-- or -- 
value(xs:hexBinary(sql:column("ColumnName") , SQLType)

For example, suppose you want to convert the following binary value to hexadecimal string: 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00. To do that, you use the xml value() Method with the XQUERY functions as follows:

SET NOCOUNT ON;

DECLARE @BinaryValue	[varbinary](256) ,
		@x				[xml]

SET @x = '<root></root>;'

SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00

SELECT  N'0x' + @x.value('xs:hexBinary(sql:variable("@BinaryValue"))',
                         '[varchar](512)')

SET NOCOUNT OFF;
GO

It returns the following output:

value_function_with_XQuery_functions

Option 3: Using sys.fn_varbintohexstr undocumented function and sp_hexdecimal stored procedure

You can use sys.fn_varbintohexstr undocumented function to return a character string which contains the hexadecimal representation of a binary value. For example, the following script shows how you can use sys.fn_varbintohexstr undocumented function to convert binary value to a hexadecimal string:

SET NOCOUNT ON;

DECLARE @BinaryValue [varbinary](256); 

SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00;

SELECT  [sys].[fn_varbintohexstr](@BinaryValue);

SET NOCOUNT OFF;
GO

It returns the following output:

fn_varbintohexstr_undocumented_version

You can also use sp_hexdecimal stored procedure as described in a Microsoft Knowledge Base Article: “INFO: Converting Binary Data to Hexadecimal String” to convert binary value to a hexadecimal string.

My preferred option

I prefer option 1 and 2 mentioned in this post for converting the binary value to a hexadecimal string. That is why; I wrote the following function using the first two options, which you can use to return a string containing the hexadecimal representation of a binary value:

IF OBJECT_ID(N'dbo.ufn_binvaluetohexdecstr') IS NOT NULL
    BEGIN
        DROP FUNCTION [dbo].[ufn_binvaluetohexdecstr];
    END
GO

CREATE FUNCTION [dbo].[ufn_binvaluetohexdecstr] (@p_binhexvalue [varbinary](256))
RETURNS [varchar](512)
AS
    BEGIN

        DECLARE @x				[xml] ,
				@OutPutStrHex	[varchar](512) ,
				@Version		[numeric](18, 1);

		SET @x = '<root></root>';
        SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)),
						CHARINDEX(N'.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.'
						+ REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)),
						LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))
						- CHARINDEX(N'.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10));
		        
        IF @Version >= 10.5
            BEGIN 
                SELECT  @OutPutStrHex = CONVERT([varchar](512), @p_binhexvalue, 1);
            END
        ELSE
            BEGIN 
                SELECT  @OutPutStrHex = N'0x' + @x.value('xs:hexBinary(sql:variable("@p_binhexvalue"))',
                                                         '[varchar](512)');
            END
		
        RETURN (SELECT @OutPutStrHex) 
    END
GO

I hope you will find this post useful. 🙂

Have a good week!

Basit

SQL Server 2005/2008/2008R2: HTTP endpoints

You can use an HTTP endpoint to allow access to data over HTTP and Secure Sockets Layer (HTTPS) without needing to install Internet Information Services (IIS).

Important Note: HTTP Endpoints feature has been deprecate in SQL Server 2012 and later versions.  For more information, see here

Click here to read full article on SSWUG.org

Configuring SQL Server Database Mail Feature

You can send e-mail from within stored procedures, functions, and triggers by using SQL Server Database Mail. You can also configure SQL Server Agent Mail to use Database Mail. Some features of Database Mail include:

  • Uses Simple Mail Transport Protocol (SMTP)
  • Does not require Outlook or Extended Messaging Application Programming Interface (MAPI) support
  • Can use multiple SMTP servers
  • Can be used in a cluster server configuration
  • Supports asynchronous background delivery
  • Supports multiple profiles and accounts
  • Supports security features, included prohibited file extensions, a limit on attachment size, and auditing
  • Allows you to send messages formatted in HTML

Click here to read full article on SSWUG.org