Sql Server: How to use RAISERROR

RAISERROR is an interesting and very useful t-sql command. It allows us to send information back to the caller about the status of the current execution.

Since RAISERROR supports character substitution similar to printf function is C language, it is very valuable in some situations.

Here we see some sample examples.

Simple error message

--
-- A simple RAISERROR example - sends error message to STDOUT, without terminating the connection
--
RAISERROR ('Non critical error message.', 15, 1)
GO

Severity is an important parameter of this command:

• Severity levels from 0 – 10 are informational. Error message is displayed to STDOUT, but does not affect the execution flow.
• Severity levels from 11 – 19 are error triggering. They pass the control from TRY BLOCK to CATCH block.
• Severity levels from 19 – 25 can only be specified by members of sysadmin. Also WITH LOG is required for this level.
• Severity levels from 20 – 25 are considered fatal; Connection is terminated and error message is logged in Sql Server Error log and Application log.

 

Character Substitution example

--
-- Character Substitution
--
RAISERROR (N'Apples are %s; We have %d of them.',
           10, -- Severity,
           1, -- State,
           N'red', -- First argument.
           10); -- Second argument.
GO
--
-- The message text returned is:
--
Apples are red; We have 10 of them.

As you can see, character substitution is useful in some scenarios (above example may not be).

 

With Option example

In this below example, we see a way to send error/information message back to STDOUT immediately. This was covered in more detail in one of my previous post here, but I’ll briefly touch on this here.

--
-- Send message to STDOUT immediately
--
RAISERROR ('End of Step 1', 10, 1) WITH NOWAIT
GO

Hope this helps,
_SqlTimes

Read Full Post »

Sql Server Drop multiple columns from a table in a single statement

Quick one today

Sometimes, there is a need to DROP more than one columns from a table. The usual approach of DROPping one column at a time still works. Today we’ll explore the option to DROP multiple columns at the same time using a single ALTER TABLE statement.

It is intuitive this way…

--
-- DROP multiple columns using single ALTER TABLE statement
--
ALTER TABLE dbo.SampleTable
DROP COLUMN Name           --  add comments for reason for each column
          , Age            --  'Age' is not required any more
          , Date_of_Birth  --  'DoB' is depricated
          , SSN            --  'SSN' is moved
GO

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Change database mirroring expired certificate

Since its introduction, in Sql Server 2005, Database Mirroring has been one the popular features. We have database mirroring configured on several production databases. It is easy to configure, maintain and very efficient in transporting transactions from Principal server to the Mirror. With more new improvements in later versions, the transfer of transactional log to Mirror is more optimized. That is a topic for some other time.

Today, let us look at renewing/re-configuring expired certificate on Database Mirroring.

The option of creating mirroring using certificates is not too common, but it has a lot of benefits of its own. When we create certificates for this purpose, the usual syntax used it something like this:

--
-- Default syntax used to create certificates
--
CREATE CERTIFICATE Principal_Certificate
WITH SUBJECT = 'Principal Certificate'
GO

This works, but the problem is when you do not specify ‘EXPIRY_DATE’ parameter the certificate defaults to one year. So come next year, you’ll need to change the certificate. And this is how to do it:

Steps to configure new certificate

Phase 1

• Step 1: On Principal, create new certificates (C1) with longer duration
• Step 2: Backup the certificate (on Principal)
• Step 3: Copy the Principal certificate backup to Mirror server

Phase 2

• Step 4: On Mirror, create a new certificate (C2) with longer duration
• Step 5: Backup the certificate (on Mirror)
• Step 6: Copy the Mirror certificate backup to Principal server

Phase 3

• Step 7: Restore new certificate (C2) from Mirror on to Principal server. Make sure the owner of this new certificate is the same as the old certificate owner login.
• Step 8: Alter endpoint on Principal to Principal to use new certificate (C1)

Phase 4

• Step 9: Do same as Step 7 on Mirror server (C1). Make sure the owner login stays the same as owner of previous certificate
• Step 10: Alter endpoint on Mirror to use new certificate (C2)

Phase 5

• Step 11: DROP both the old certificates on Principal server
• Step 12: DROP both the old certificates on Mirror server

 

Now sample code is below:

Phase 1

--
-- Steps to change mirroring certificate
--

 --
 -- Step 1: On Principal, create new certificates with longer duration
 --
 USE [master]
 GO

 CREATE CERTIFICATE Principal_Certificate_New
 WITH SUBJECT = 'Principal Certificate New'
    , EXPIRY_DATE = '12/31/2050'
 GO

 --
 -- Step 2: Backup the certificate (on Principal)
 --
 BACKUP CERTIFICATE Principal_Certificate_New
 TO FILE = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\Principal_Certificate_New.cer'
 GO

 --
 -- Step 3: Copy the Principal certificate backup to Mirror server
 --
     -- copy the certificate to Mirror

Phase 2

 --
 -- Step 4: On Mirror, create a new certificate with longer duration
 --
 CREATE CERTIFICATE Mirror_Certificate_New
 WITH SUBJECT = 'Mirror Certificate New'
    , EXPIRY_DATE = '12/31/2050'
 GO

 --
 -- Step 5: Backup the certificate (on Mirror)
 --
 BACKUP CERTIFICATE Mirror_Certificate_New
 TO FILE = N'C:\Program Files\Microsoft SQL Server\MSSQL11.PLAYR2\MSSQL\Log\Mirror_Certificate_New.cer'
 GO

 --
 -- Step 6: Copy the Mirror certificate backup to Principal server
 --
 -- copy the certificate to Principal

Phase 3

 --
 -- Step 7: Restore new certificate from Mirror on to Principal server. Make sure the owner of this new certificate is the same as the old certificate owner login.
 --
 CREATE CERTIFICATE Mirror_Certificate_New
 AUTHORIZATION [DBMirror_Mirror]
 FROM FILE = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\Mirror_Certificate_New.cer'
 GO

 --
 -- Step 8: Alter endpoint on Principal to use new certificate
 --
 ALTER ENDPOINT EP_Mirroring_P
 FOR DATABASE_MIRRORING
     (
        AUTHENTICATION = CERTIFICATE Principal_Certificate_New
     )
 GO

Phase 4

 --
 -- Step 9: Do same as Step 7 on Mirror server. Make sure the owner login stays the same as owner of previous certificate
 --
 CREATE CERTIFICATE Principal_Certificate_New
 AUTHORIZATION [DBMirror_Principal]
 FROM FILE = N'C:\Program Files\Microsoft SQL Server\MSSQL11.PLAYR2\MSSQL\Log\Principal_Certificate_New.cer'
 GO

 --
 -- Step 10: Alter endpoint on Mirror to use new certificate
 --
 ALTER ENDPOINT EP_Mirroring_M
 FOR DATABASE_MIRRORING
     (
         AUTHENTICATION = CERTIFICATE Mirror_Certificate_New
     )
 GO

Phase 5

 --
 -- Step 11: DROP both the old certificates on Principal server
 --
 DROP CERTIFICATE Mirror_Certificate
 DROP CERTIFICATE Principal_Certificate
 GO

 --
 -- Step 12: DROP both the old certificates on Mirror server
 --
 DROP CERTIFICATE Mirror_Certificate
 DROP CERTIFICATE Principal_Certificate
 GO

 

Points:

• Mirroring session does not need to be brought down for this operation.
• Production database will be live and running while this operation is conducted in the background. But it is a good idea to do this during a maintenance window.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Backup Certificate along with Private Key

Quick one today:

Certificates are used everywhere these days. In Sql Server, certificates are used in several features to make communication secure — with proper authentication. Certificates are asymmetric keys, so they have public and private keys.

Following are the steps to create and backup certificates and sometimes with its private key.

Create a Certificate

Following syntax allows to create a new certificate and specify an expiration date as well.

--
-- Create a new certificate
--
USE [master]
GO

CREATE CERTIFICATE Principal_Certificate_New
WITH  SUBJECT = 'Principal Certificate New'
    , EXPIRY_DATE = '12/31/2040'
GO

Backup Certificate

This syntax below allows us to take certificate backup. This backups up the public key of the certificate (not private key).

--
--	Back up the certificate
--
BACKUP CERTIFICATE Mirror_Certificate_New
TO FILE = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Mirror_Certificate_New.cer'
GO

Backup Certificate along with its private key

This syntax below allows us to take backup of the certificate with its private key. Private key is encrypted with a password.

--
--	Back up the certificate with private key
--
BACKUP CERTIFICATE Mirror_Certificate_New
TO FILE = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Mirror_Certificate_New.cer'
WITH PRIVATE KEY
  (
     FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Mirror_Certificate_New.Key'
   , ENCRYPTION BY PASSWORD = 'UseaRandomPasswordWithNumbers123'
  )
GO

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : How to check if a user define table type exists using query (TSQL)

Quick one today

When we prepare SQL scripts to run in a production environment, we practice defensive coding mechanism. So, before we DROP an object, we check if it actually exists; Check if a function exists, before DROPping it.

Today’s question is how to do that for a user-defined table type?

See example below:

--
-- Query the sys.types
--
SELECT *
FROM sys.types
WHERE is_table_type = 1
AND name ='StoreLocationType'
GO

 

Now let’s use this in an actual example:

-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-- Step 1: Create new user defined table type : StoreLocationType
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
IF NOT EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name ='StoreLocationType')
    CREATE TYPE dbo.StoreLocationType AS TABLE(
        LocationID BIGINT NOT NULL PRIMARY KEY CLUSTERED (LocationID ASC) WITH (IGNORE_DUP_KEY = OFF)
    )
GO

Types are different from other objects because they are not schema bound. So we need to use sys.types system view.

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Create Master Key (database key)

Quick one today

Creating database master key is important and prerequisite to creating other certificates in a Sql Server database. Database Master key is at each database level and is stored in both the user database and in master database.

Some highlights of this database Master key:

1. It is a symmetric key
2. Used to protect other keys in the user databases
   • Private keys of certificates
   • Other symmetric keys
3. Encrypted by password
4. Algorithm used is AES_256
5. Backup this key and store off-site, if you can.

--
-- Script to create master database key
--
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'W#$%#$5jsdfkl3q4w3kl;jsdfji'
GO

The copy of this database master key stored in master database and is encrypted with Service Master key stored at the instance level (master database).

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server Difference between Checkpoint, Lazy Writer and Log Flush

Check point

Check point writes out, to disk, all data pages that have changed since the last check point. The goal of checkpoint is to reduce the amount of time Sql Server takes to perform rollforward and rollback operations. As part of this, it makes sure all the dirty pages are written to disk, so when Sql Server restarts after a catastrophe or something else, the amount of work Sql Server needs to do to bring the database current is minimized.

During normal operations, all the data changes are written directly to the data pages in memory. These changes need to be written to the disk (MDF & LDF) files. On a busy database server, if CHECKPOINT does not happen for longer periods of time, there will be many dirty pages in memory. If such a database experiences unexpected restarts (or other catastrophe’s), all the dirty pages in memory are lost (not really…Sql is smarter). CHECKPOINT minimizes this in two ways.

1. By regularly writing pages to disk, all the data changes are safe.
2. The dirty pages that are still in memory during such catastrophe (and are lost) need to be re-played using entries in transactional log file (LDF).
   1. Rollforward helps replay the transactions that are committed, but not written to disk yet.
   2. Rollback helps remove transactions that were written to disk but were not committed.
3. For large & busy databases there will be many such transactions, resulting it longer duration to bring the database back to operational after a disaster. CHECKPOINT helps minimize this by frequently writing pages to disk.

One can manually issue CHECKPOINT, but running the command like this:

CHECKPOINT
GO

• Checkpoint, writes any dirty data to disk irrespective of the transaction commit status. When a transaction modifies a bunch of pages it results in dirty pages. Let’s say the transaction is still running and has not committed yet. But if the CHECKPOINT occurs before the transaction commit, those dirty pages are still written to disk by CHECKPOINT.
• Usually this runs every 1 minute or so, but is configurable. One of the settings in sys.configurations table (“recovery interval (min)”) affects the frequency of CHECKPOINT.

Lazywriter

Lazywriter also flushes dirty  pages to disk. Sql Server constantly monitors memory usage to assess resource contention (or availability); It’s job is to make sure that there is a certain amount of free space available at all times. As part of this process, when it notices any such resource contention, it triggers LazyWriter to free up some pages in memory by writing out dirty pages to disk. It employs Least Recently Used (LRU) algorithm to decide which pages are to be flushed to the disk.

• There is a dedicated LazyWriter thread for each NUMA node.
• If LazyWriter is always active, it could indicate memory bottleneck.

Log Flush

Log Flush also writes pages to disk. The difference here is that it writes pages from Log Cache into the Transactional log file (LDF). Once a transaction completes, LogFlush writes those pages (from Log Cache) to LDF file on disk.

Each and every transaction that results in data page changes, also incurs some Log Cache changes. At the end of each transaction (commit), these changes from Log Cache are flushed down to the physical file (LDF). So, in essence, the number of log flushes depend on number of transactions.

• Please note that SELECT statements do not result in any data page changes, so there are no changes to Log Cache, so no Log Flushes to Transactional Log file.

 

Hope this helps,
_Sqltimes

Read Full Post »