Wednesday, October 30, 2013

SQL Server Error Log Management


SQL Server maintains Error log file which can store information messages, and errors with numbers similar to windows event viewer. When we encounter any issue in the SQL Server this error log is the first place we need to check whats wrong with the server. These error log files are stored in the LOG folder of each instance example "c:\program files\microsoft sql server\mssql10.50\mssql\log",
If required we can change the location of error log files by modifying -e parameter in the SQL Server Engine Configuration.

We can see these error logs in SSMS - Management - SQL Server Logs - Current, Archive#1, Archive#2,  Archive#3, Archive#4, Archive#5, Archive#6
These will be maintained by SQL Server, Each time if you restart SQL Server these files get recycle, what it means is the Archive#6 file gets deleted  and Current will become Archive#1 and so on and new file will be created and named as Current.

There are couple of disadvantages using the default behavior of SQL Server that there wont be any clue for size of the error log files, that means you cannot except the size of all error logs should be same; one file can contain 10 days of data and other file contains only 2 days of data.  And recycling happend thru SQL Server so users may not control the default behaviour, so that we need to set seperate process to make sure one file has only 1 day of information.

Also we need to modify the default error log files from 7 to some large number, otherwise we may lost the history.

As a first step we need to increase the error log files from 7 to 50, To do so
SSMS - Management - SQL Server Logs - RightClick - Configure - Check Limit the number of error files before they are recycled We can also create one error log file for every one day by calling the sys.sp_cycle_errorlog on daily basis, It will be very helpful while managing error log files.

One day data will be stored for one log file so that we can get go back and verify those files at any time easily. The same is applicable for SQL Server Agent also, but there 10 files will be maintained instead of 7 files.

Like SQL Server Error logs we cannot change increase the number of error logs for SQL Server Agent, sp_cycle_agent_errorlog helps to recycle Agent error logs without restarting Agent.

Monday, October 28, 2013

Convert Run Duration in sysjobhistory


Convert run duration of sysjobhistory to hh:mm:ss

The run_time 25823 means started at 02:58:23 and run_duration 355 means the job executed for 00:03:55 means 3 minutes 55 seconds


select j.name, step_name, run_date, run_duration,
substring(cast(replicate('0',6-len(run_duration)) + cast(run_duration as varchar) as varchar),1,2) + ':' +
substring(cast(replicate('0',6-len(run_duration)) + cast(run_duration as varchar) as varchar),3,2) + ':' +
substring(cast(replicate('0',6-len(run_duration)) + cast(run_duration as varchar) as varchar),5,2)
as 'hh:mm:ss'
from sysjobs j, sysjobhistory jh where j.job_id = jh.job_id and j.name like 'JobName%'
and jh.step_name like '%outcome%'
order by run_date desc

 

Wednesday, October 23, 2013

Encryption Examples

TDE -- Transparent Data Encryption

Below are the steps for TDE

1. Create master key for the database

2. Create certificate that protected by master key

3. Create a special key Database Encryption Key that used to protect database, and secure it using certificate.

4. Enable encryption on database.

use master
create master key encryption by password = 'calyansql@123'
create certificate encryption_cert with subject = 'KalyanDB_Certificate'

use KalyanDB
create database encryption key with algorithm = AES_128 encryption by server certificate encryption_cert
--Alogirthm can be AES_128, AES_192, AES_256, or Triple_DES_3Key

Alter database KalyandB set encryption on

-- Backup the certificate is very important step to move this to target server while restoring the Encrypted Database

use master

backup certificate encryption_cert to file ='c:\temp\encryption1_cert.cert'
with private key (file='c:\temp\encryptionprivatekey.key', encryption by password='calyansql@123')

backup database kalyandb to disk='c:\temp\kalyandb.bak'
select * from sys.certificates

How to restore encrypted database on target server?

1. Move database backup and certificate files to target server
2. Create master key
3. Create certificate using the transferred file from the source server

Example

use master
create master key encryption by password='calyansql@123'
create certificate encryption_cert from file='E:\temp\encryption_cert.cert'
with private key (file='E:\temp\encryptionprivatekey.key', Decryption by password='calyansql@123')

After creating certificate we can restore the database in normal way, without creating the certificate we cannot do restore.