Home » 2014 » May

Monthly Archives: May 2014

How Database Mail works in SQL Server ?

Database Mail is email feature in SQL Server from SQL Server 2005 onwards. This feature used for mailing alerts, notification, reports etc.

Requirements for Setting up Database Mail :-

1) Details of SMTP server

2) Connectivity with SMTP server

3) Access to SMTP server to send e mails

Components of Database Mail :-

1) Configuration & Security Details : Database Mail configuration details like user details, SMTP Server IP, Port No., profile name, default profile etc. will be stored in MSDB database.

2) Messaging components : MSDB database stored several tables & store procedures for Database Mail to send e-mail. The sp_send_dbmail stored procedure is used to send emails.

3) Database Mail executable : The Database Mail feature is something that is not inside in MSDB & SQL Server. MSDB connects to external executable DATABASEMAIL90.exe to send mails using configured SMTP server.

4) Logging and auditing components : MSDB database stored several tables to store Database Mail records logging information. Information like message status (Send / Failed / Pending), attachment details etc. saved in logging tables.

Completed Process of Database Mail :-

1) Database Mail feature use service broker for its functionality. When you execute sp_send_dbmail stored procedure to send email, it inserts a record into mail queue that contain e-mail message.

2) Entering a new record in the mail queue activates the external Database Mail executable (DatabaseMail90.exe).

3) Database Mail executable (DatabaseMail90.exe) reads e-mail details & sends the e-mail message to the appropriate e-mail server or servers.

4) Database Mail executable (DatabaseMail90.exe) inserts a record in the Status queue for the outcome of the each email send operation.

5) Inserting the new record in the status queue trigger an internal stored procedure that updates the status(send / Pending / Failed) of the e-mail message

(Image Source : http://technet.microsoft.com/en-us/library/ms189635(v=sql.110).aspx )

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 10 for SQL Server 2012 Service Pack 1 Is Now Available !

The 10th cumulative update release for SQL Server 2012 Service Pack 1 is now available for download at the Microsoft Support site. Cumulative Update 10 contains all the hotfixes released since the initial release of SQL Server 2012 SP1.

Those who are facing severe issues with their environment, they can plan to test CU10 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP2 final release to deploy on your production environment, to have consolidate build.

KB Article For CU10 of SQL Server 2012 SP1

Previous Cumulative Update KB Articles:

· CU#9 KB Article: http://support.microsoft.com/kb/2931078

· CU#8 KB Article: http://support.microsoft.com/kb/2917531

· CU#7 KB Article: http://support.microsoft.com/kb/2894115

· CU#6 KB Article: http://support.microsoft.com/kb/2874879

· CU#5 KB Article: http://support.microsoft.com/kb/2861107

· CU#4 KB Article: http://support.microsoft.com/kb/2833645

· CU#3 KB Article: http://support.microsoft.com/kb/2812412

· CU#2 KB Article: http://support.microsoft.com/kb/2790947

· CU#1 KB Article: http://support.microsoft.com/kb/2765331

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Database Backup Encryption with SQL Server 2014

A brand new & long awaited feature “Backup Encryption” along with SQL Server 2014. Microsoft reveled this upcoming feature in PASS summit & from then users are waiting for this. Currently backup encryption supporting four encryption algorithms: AES 128, AES 192, AES 256, and Triple DES (3DES).

Currently, User are using TDE or 3rd party software only to encrypt there database backups. This feature is going to reduce your complexity.

Points to Remember :-

1) TDE(Transparent Data Encryption) is different from backup encryption

2) Native backup encryption available only in Standard, Business Intelligence and Enterprise Editions. Web and Express edition does not supported this feature

3) VIEW DEFINITION permission required on the certificate or asymmetric key that used to encrypt the database backup

4) SQL Server Express and SQL Server Web do not support backup encryption. But restoring encrypted backup to an instance of SQL Server Express or SQL Server Web is supported

5) Backward compatibility, Previous versions of SQL Server cannot read encrypted backups

6) Server on which Encrypted backup going to restore, Master key & Encryption certificate need to exists

7) Appending new backup file to an existing backup set is not supported for encrypted backups. If you try, you will receive below error.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Msg 3095, Level 16, State 1, Line 1

The backup cannot be performed because ‘ENCRYPTION’ was requested after the media was formatted with an incompatible structure. To append to this media set, either omit ‘ENCRYPTION’ or create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Benefits of Backup Encryption :-

  1. Database Backup Encryption secure the data. SQL Server provides the option to encrypt the backup data while creating a backup.

If we try to open backup file in notepad, we can see actual data is visible & that can lead to data theft threat.

But data in Encrypted backup files is not readable.

  1. Database Backup Encryption can also be used for databases that are encrypted using TDE. But I feel, if you enabled TDE, you are good enough
  2. Database Backup Encryption supported for backups done by SQL Server Managed Backup to Windows Azure, which provides additional security for off-site backups
  3. Database backup encryption supporting four encryption algorithms: AES 128, AES 192, AES 256, and Triple DES (3DES). This gives you the option to select an algorithm as per your requirements.

How to check which backup set is encrypted ? :-

1) You need to look into BACKUPSET in MSDB about encryption

2) No change in RESTORE HEADERONLY, so you will not get any information about encryption in HEADERONLY

Steps to take encrypted backup :-

1) Create a Database Master Key for the master database on the instance

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pass@word1’

2) Create an encryption certificate

Use Master

GO

CREATE CERTIFICATE DBBackupEncryptCert

WITH SUBJECT = ‘Backup Encryption Certificate’;

3) Backup Master key & encryption certificate

Use Master

GO

BACKUP CERTIFICATE DBBackupEncryptCert

TO FILE = ‘C:BackupDBBackupEncryptCert.cert’

WITH PRIVATE KEY

(

FILE = ‘C:BackupDBBackupEncryptCert.cert’,

ENCRYPTION BY PASSWORD = ‘Pass@word1’

);

GO

BACKUP MASTER KEY TO FILE = ‘C:BackupMasterKey.key’

ENCRYPTION BY PASSWORD = ‘Pass@word1’;

4) Backup database with encryption option & required encryption algorithm

a. By Script

BACKUP DATABASE UserDB1

TO DISK = ‘C:BackupUserDB1_Encrypt.bak’

WITH

ENCRYPTION

(

ALGORITHM = AES_256,

SERVER CERTIFICATE = DBBackupEncryptCert

),

STATS = 10 , INIT

b. By GUI

Steps to restore encrypted backup :-

1. Scenario 1 – Server on which Encrypted backup going to restore has Master key & Encryption certificate. No Change is restore steps either from script or GUI required.

RESTORE DATABASE [UserDB1] FROM DISK = N’C:BackupUserDB1_Encrypt.bak’

2. Scenario 2 – Server on which Encrypted backup going to restore, Master key & Encryption certificate does not exists

It’s not possible, You will get below error.

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint ‘0x49FB256B3F7F586205178E1D26C28E724F432F59’.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Add Node to Windows Server 2008 Cluster

Steps : Add Node to Windows Server 2008 Cluster

1) Open Cluster Administration wizard > Right click on Windows Cluster & select Add Node

2) Add Node Wizard appears, Click Next to start

3) Browse Node to add, We select Node 3 to add

4) Select if you want to validate cluster settings or not & click Next

Note : Validation cluster settings is recommended

5) Validation a Configuration wizard will come, Click Next to start validation

6) Select type of test & click Next

7) Click Next to start Validation

8) Validation completed successfully(Warning to re configure quorum comes, we can ignore that), Click Finish to continue add node process

9) Click Next to start add process

10) Add node in process

11) Node 3 successfully added to cluster

Note : We need to reconfigure quorum to clear this warning.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Thanks to Toadworld.com team for recognition !

Thanks to Toadworld.com team for recognition !

Toadworld.com Profile : http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 12 for SQL Server 2008 R2 Service Pack 2 Is Now Available !

The 12th cumulative update release for SQL Server 2008 R2 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 12 contains all the hotfixes released since the initial release of SQL Server 2008 R2 SP2.

Those who are facing severe issues with their environment, they can plan to test CU12 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP3 final release to deploy on your production environment, to have consolidate build.

KB Article For CU12 of SQL Server 2008 R2 SP2

Previous Cumulative Update KB Articles:

· CU#11 KB Article: http://support.microsoft.com/kb/2926028

· CU#10 KB Article: http://support.microsoft.com/kb/2908087

· CU#9 KB Article: http://support.microsoft.com/kb/2887606

· CU#8 KB Article: http://support.microsoft.com/kb/2871401

· CU#7 KB Article: http://support.microsoft.com/kb/2844090

· CU#6 KB Article: http://support.microsoft.com/kb/2830140

· CU#5 KB Article: http://support.microsoft.com/kb/2797460

· CU#4 KB Article: http://support.microsoft.com/kb/2777358

· CU#3 KB Article: http://support.microsoft.com/kb/2754552

· CU#2 KB Article: http://support.microsoft.com/kb/2740411

· CU#1 KB Article: http://support.microsoft.com/kb/2720425

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 1 for SQL Server 2014 RTM Rollback by Microsoft !

The 1st cumulative update release for SQL Server 2014 RTM was released by Microsoft.

But due to issues in setup Microsoft Rollback the release of CU1 of SQL Server 2014 RTM.

KB Article For CU1 of SQL Server 2014 RTM

Image Source : http://blogs.msdn.com/

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

LinkedIn Auto Publish Powered By : XYZScripts.com
%d bloggers like this: