Sql Server : Free Books

Quick one today:

Every now and then, when good Sql Server resources are available, they are posted here for more people to benefit from them. Continuing on that tradition, today we have a gold mine. Microsoft has released many, many & many e-books open to public to download for free. In the list there are several Sql Server books along with BI, Windows, Office, SharePoint, etc

Happy learning !!

 

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 : 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 »

Setting Up Database Mirroring on Sql Server 2008

Following are the steps that I follow to set up DB Mirroring between Sql Server 2008 databases on separate servers.

Overview:

1. Create Sample Database for Mirroring on Principal Server
2. Create some database objects in the database to test i.e. tables, stored procedures, etc
3. Create Database Mirroring EndPoints on Principal, Mirror and Witness
4. CREATE necessary accounts and GRANT permissions.
5. Prepare Mirroring database
6. Enable / Set up mirroring.
7. Create Snapshot database on Mirror database.
8. Failover:
   1. Test Failover
   2. Force Failover
9. Server Objects.
10. Other points.

Server Names:

Initial Instance Role	Domain Service Account	Server Name
Principal	AD\sql1	SalesDB.AD.com
Mirror	AD\sql1	SalesDBFailOver.AD.com
Witness	AD\sqlWitness	WitnessDB.AD.com

Assumptions:

• Principal, Mirror and Witness services run with domain accounts.
• All users and application connections to Sql Server are also domain accounts.

Step 1: Create Sample Database for Mirroring on Principal Server

First let’s create a database on Principal server for mirroring. FULL recovery model is required for databases participating in mirroring.

-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-- Step 01: CREATE Principal Database.
-- Run on Principal Server
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CREATE DATABASE DBMir
ON
PRIMARY
(
NAME            = N'DBMir_PRIMARY_Data'
, FILENAME       = N'E:\MS SQL\MSSQL_2K8\Play1\MDF\DBMir_Primary_Data.MDF'
, SIZE           = 1024 MB
, MAXSIZE        = 2 GB
, FILEGROWTH = 10
)
LOG ON
(
NAME            = N'DBMir_Log'
, FILENAME       = N'E:\MS SQL\MSSQL_2K8\Play1\LDF\DBMir_Log.LDF'
, SIZE           = 1024 MB
, MAXSIZE        = 2048 MB
, FILEGROWTH = 10
)
GO

--    SET recovery model to FULL
ALTER DATABASE [DBMir]
SET RECOVERY FULL
GO

Step 02: Create some database objects and server level objects to test after failover i.e. logins, tables, stored procedures, etc.

--   sample server level object
USE MASTER
GO

CREATE LOGIN [test] WITH PASSWORD = 'test'
WITH  DEFAULT_DATABASE = [DBMir]
, DEFAULT_LANGUAGE = [us_english]
GO
USE [DBMir]
GO

CREATE USER [test] FOR LOGIN [test]
GO

--  Sample table
CREATE TABLE dbo.Test
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, Name   VARCHAR(40)     NULL
, Age    INT             NULL
, Blob   VARCHAR(MAX)    NULL
)
GO

-- Insert a few records, before setting up Mirroring.
INSERT INTO dbo.Test (Name, Age, Blob) VALUES('Name1', 10, REPLICATE(CONVERT(VARCHAR(MAX), 'ac'),10000))
GO 10

Step 03: Create Database Mirroring EndPoints on Principal, Mirror and Witness.

-- Create endpoint on Principal
CREATE ENDPOINT DBMirror_Principal
STATE = STARTED
AS
TCP (LISTENER_PORT = 7022) -- Do not use port 7022 on prod. server
FOR DATABASE_MIRRORING (ROLE = PARTNER)
GO

-- Create endpoint on Mirror
CREATE ENDPOINT DBMirror_Mirror
STATE = STARTED
AS
TCP (LISTENER_PORT = 7023)
FOR DATABASE_MIRRORING (ROLE = PARTNER)
GO

-- Create endpoint on Witness
CREATE ENDPOINT DBMirror_Witness
STATE = STARTED
AS
TCP (LISTENER_PORT = 7024)
FOR DATABASE_MIRRORING (ROLE = WITNESS)
GO

Step 04: CREATE necessary accounts and GRANT permissions

Run on Principal

-- CREATE LOGIN for Witness (service account) on Principal
-- and GRANT CONNECT permission on Principal's EndPoint
CREATE LOGIN [AD\sqlWitness] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Principal TO [AD\sqlWitness]
GO

-- CREATE LOGIN for Witness (service account) on Mirror
-- and GRANT CONNECT permission on Mirror's EndPoint
CREATE USER [AD\sqlWitness] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Mirror TO [AD\sqlWitness]
GO

-- CREATE LOGIN for Principal/Mirror(same service account on both) on Witness
-- and GRANT CONNECT permission on Witness's EndPoint
CREATE LOGIN [AD\sql1] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Witness TO [AD\sql1]
GO

Step 05: Prepare Mirroring database

-- Run on Principal
BACKUP DATABASE DBMir TO DISK = N'E:\MS SQL\MSSQL_2K8\Play1\Backup\DBMir_Principal.BAK'
GO

BACKUP LOG DBMir TO DISK = N'E:\MS SQL\MSSQL_2K8\Play1\Backup\DBMir_Principal.TRN'
GO

-- Copy the database files over to Mirror Instance

-- Run on Mirror
RESTORE DATABASE DBMir FROM DISK = N'E:\MS SQL\MSSQL_2K8\Play2\Backup\DBMir_Principal.BAK'
WITH MOVE 'DBMir_Principal_Data' TO 'E:\MS SQL\MSSQL_2K8\Play2\MDF\DBMir_Mirror.MDF'
, MOVE 'DBMir_Log' TO  'E:\MS SQL\MSSQL_2K8\Play2\LDF\DBMir_Mirror_Log.LDF'
, NORECOVERY
, REPLACE
GO
RESTORE LOG DBMir FROM DISK = N'E:\MS SQL\MSSQL_2K8\Play2\Backup\DBMir_Principal.TRN'
WITH NORECOVERY
GO

Step 06: Enable / Set up mirroring

-- Run on Mirror: Inform Mirror about Principal (first step)
ALTER DATABASE DBMir
SET PARTNER = 'TCP://SalesDB.AD.com:7022'
GO

-- Run on Principal: Inform Principal about Mirror
ALTER DATABASE DBMir
SET PARTNER = 'TCP://SalesDBFailOver.AD.com:7023'
GO

-- Run on Principal: Inform Principal about Witness
ALTER DATABASE DBMir
SET WITNESS = 'TCP://WitnessDB.AD.com:7024'
GO

Step 07: Create Snapshot database on Mirror database.

-- Create snapshot on Mirror to run sample queries
USE MASTER
GO
CREATE DATABASE DBMir_SnapShot
ON
(
NAME        =   'DBMir_Primary_Data'
, FILENAME   =   'E:\MS SQL\MSSQL_2K8\Play2\MDF\DBMir_SnapShot2.DS'
)
AS  SNAPSHOT OF DBMir
GO

SELECT COUNT(*) FROM dbo.Test
GO

Step 08: Failover

If you want to test the cussecc of failover in our environment use the following FAILOVER command.

-- To test FailOver, run the following query on Principal
-- Smooth failover happens. If there are any SQL Logins,
-- To map orphan users, run sp_change_users_login on Mirror
ALTER DATABASE DBMir
SET PARTNER FAILOVER
GO

But if the Principal goes down and you want to force FAILOVER to
the mirror server, use the following command. This could result in
some data loss.

-- To force failover, when the Principal is unavailable, run this on Mirror
-- Pre-requisite: Principal must be unavailable
ALTER DATABASE DBMir
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO

Step 09:  Server Objects

A note on logins: Make sure logins are created in the same order on the Mirror server as on the Principal server. This way login SID for will remain the same and will not create any orphans.

Any other server level objects could be transferred using an SSIS package designed specifically for this purpose.

Step 10:  Other points

If you want to run any maintenance on one of the servers participating in Mirroring the following commands will come to good use. Lets say, you want to upgrade patches on Principal and Mirror database server.

1. First, you want to perform a manual FAILOVER [from Principal to Mirror]
2. Suspend DB mirroring [stop any data flow from new Principal to new Mirror]
3. Upgrade original Principal Server
4. Bring Principal up and running
5. Resume DB Mirroring to bring previous Principal (now Mirror) up to date.
6. Perfom another manual FAILOVER to the newly upgraded Mirror (original Principal).
7. Suspend Mirroring
8. Upgrade Mirror (original)
9. Bring it back up.
10. Resume DB Mirroring.

To perform all the above steps, the following commands will be useful.

-- To drop DB Mirroring
ALTER DATABASE DBMir
SET PARTNER OFF      -- to DROP DB Mirroring
GO

-- To re-establish DB Mirroring
ALTER DATABASE DBMir
SET PARTNER ON
GO

-- To temporarily suspend mirrorring
ALTER DATABASE DBMir
SET PARTNER SUSPEND
GO

-- To resume, suspended mirroring
ALTER DATABASE DBMir
SET PARTNER RESUME
GO

Hope this helps,
_Sqltimes

Read Full Post »

Compare features in different High Availability options in Sql Server.

Since Sql Server 2005, High Availability (HA) has become very easy to implement in a Sql Server environment. Also, starting Sql 2005, there are 4 methods to implement HA.

• Failover Clustering
• Database Mirroring
• Replication
• Log Shipping

Though, Failover clustering still is the most popular option, my guess is the other options might make it a more level field going forward.

In the following matrix, I compare some features of each HA option in one table. This gives a good comparison picture of the features available in each option.

Property \ Feature	FailoverClustering	DB Mirroring	Replication	Log Shipping
Server Level	Yes	No	No	No
Database Level	No	Yes	Yes	Yes
Automatic Failover	Yes	Yes (Only HA Option)	No	No
Automatic Failure Detection	Yes	Yes (Only in HA option)	No	No
Hot, Warm or Cold standby	Hot	Hot and Warm Options	Warm	Warm
Server Level Objects Failover	Yes (Automatic)	Manual	Manual	Manual
Is data on Failover available for queries while the Primary server is running?	No	Yes (use Database Snapshots)	Yes	Yes (Standby Mode)
Are changes allowed at Failover location?	No	No	Yes (Merge and TRN Replication with updating subscribers)	No
Is Main and Failover server in a 1-1 relationship?	Yes	Yes	Not Required	Not Required
Is 1-Many relationship possible between the Main and Failover servers?	No	No	Yes	Yes
Is Many-1 relationship possible between Main and Failover server?	No	No	Yes	No
Is Automatic Application redirection possible after failover?	Yes	Yes	No	No
Recovery model required for the participating databases?	Any	FULL	ANY	FULL & BULK
Can you filter what data is sent to the Failover server?	No	No	Yes	No
Where does the logic HA reside?	OS + Sql	Sql	Replication Agents (they run outside Sql Server	Sql Agents (Run outside Sql Server)
If you want a reports server, what is the best solution?	Not Possible	Works, but needs constant refresh of the Snapshot database for latest data.	Best Option.	Works, but users connections need to be kicked out, when new log is applied.
Best solution if the Main and Failover server are separated by long distance?	Not good.	Works, but not recommended.	Works, but with high latency	Best Option. (Log compression reduces latency significantly.

Note: Please know that this is not all the features, these are just he ones I could think of off the top of my head. If you add your comments below, I can add them to the list.

Hope it helps,

_Sql Times

Read Full Post »

Sql Server 2008 new features Compression, FILESTREAM and TDE compatibility with with all High Availability (HA) options

Some of the new features in Sql Server 2008 are very useful (and long over due). Features like

• Compression
• FileStream
• TDE (Transparent Data Encryption).

Though they have their own limitations, they are very useful when available natively.

Here we’ll look into their compatibility with existing High Availability (HA) features.

• FailOver Clustering
• Database Mirroring
• Replication
• Log Shipping

Lets start with the matrix, and then we’ll go into details:

	Compression	FileStream	TDE
FailOver Clustering	Y	Y	Y
Database Mirroring	Y	No	Y
Replication	Y	Y	Y
Log Shipping	Y	Y	Y

When you look at it this way, it makes it much easier to remember all the options. Of all, as you can see, only one stand out i.e. FileStream is not configurable in Database Mirroring. All the other configurations are fairly well supported. Please know that there are, in fact, some limitations for these features in each HA implementation (but, that is for an article some other day).

Hopefully, future versions of Sql Server will support FileStream in Database Mirroring.

In future, I will try to add implementation details about options in each cell.

Hope this helps,

_SqlTimes

Read Full Post »