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.

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

 

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.
 

SQL Server Encryption Part I

Encryption is the process of altering data in such a way that hackers cannot read it whereas authorized users can read it. In SQL Server we can encrypt data using a key or password, without having key or password the data cannot be decrypted. Companies that are maintaining sensitive data should meet various compliance requirements such as Gramm-Leach-Bliley Act (GLBA), European Union Data Protection Directive (EUDPD), Health Insurance Portability and Accountability Act (HIPAA), Payment Card Industry Data Security Standard (PCI DSS) and Sarbanes-Oxley (SOX) act. They require encryption of sensitive information like (account numbers, credit card numbers etc) at database levels as well as OS levels.

SQL Server provides various encryption options like database-level encryption, OS level encryption, column-level encryption, and transport-level encryption. 

Transparent Data Encryption (TDE) -- It introduced in SQL Server 2008 and available in Enterprise, Developer in 2008R2 and 2012 versions. It has ability to encrypt an entire database and it is completely transparent to application. It encrypts mdf and ldf files data using AES (Advanced Encryption Standard) and 3DES (Triple DES) encryption methods. This method encrypts the backup file so if we lost the backup media then this backup cannot be restored without a key.

Cell Level Encryption -- It is also known as column level encryption introduced in SQL Server 2005 and this feature is available in all editions including express edition. This methods needs application must be changed to use encryption and decryption operation, in addition it affects performance.

Encrypting and Decrypting Data with .NET Framework -- SQL Server stores encrypted data but encryption and decryption is performed thru application. Application need to perform encryption and decryption by calling specific methods.

SQL Server encrypts data with a hierarchical encryption and key management infrastructure. It is three layer hierarchies, OS Level, SQL Server Level and Database Level.

The service master key (SMK) is the top-level key and is the father of all the keys in SQL Server. The SMK is an asymmetric key that encrypt by the Windows Data Protection API (DPAPI). The SMK is automatically created when you encrypt something for the first time and tied to SQL Service account. The SMK is used to encrypt database master key (DMK). The second layer of encryption is DMK, It encrypts symmetric keys and asymmetric keys and certificates. Each database will have only one DMK.

Symmetric Key -- In this cryptography the sender and receiver will share a common key to encrypt or decrypt the message. This is easy to implement and the sender and receiver can encrypt and decrypt the messages.

Asymmetric Key -- This cryptography is also known as public-key cryptography in which sender and receiver will have a pair of cryptography keys known as public key and private key to encrypt and decrypt messages. In this method sender has to use his key to encrypt the message whereas he couldn’t decrypt the message and receiver has to use his key to decrypt the message whereas he couldn’t encrypt it. This is a resource intensive process. 

Cluster network name resource 'SQL Network Name (VirtualServerName)' failed to create its associated computer object in domain 'domain.com' for the following reason: Unable to create computer account.

The following error has occurred:
The cluster resource 'SQL Server (virtual name)' could not be brought online due to an error bringing the dependency resource 'SQL Network Name (virtual name)' online.

We may recieve this error message while performing SQL Server cluster installation if there is no computer object defined at Active Directory,

1. Create computer object with virtual server name on associated OU
2. Provide full rights to Cluster Name for the added computer object
3. Provide full rights to DBA group (recommended)


 

Database Mirroring Part IV Suspended (Error: 1453, Severity: 16, State: 1) Add Log files to Mirrored Database

How to add additional log files to Mirrored database?

If you add new transaction log file for the same folder at the time of configuration of mirroring there wont be any issues, whereas if you add a transaction log file in new folder on principal server (if that folder is not exists on the mirror server) then we will get the below error and Mirroring will goes into suspended state
Error: 1453, Severity: 16, State: 1 (In principal server)
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file Path (In Mirror server)

To fix the issue we should create the folder in mirror server same as principal server then we can resume the mirroring.



 

The server network address can not be reached or does not exist Microsoft SQL Server, Error: 1418

The server network address "TCP://ServerName.domainname.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

The above error may occur while configuring database mirroring

1. Make sure you restored full backup on Mirror server (with norecovery mode)
2. Make sure you restored atleast one log backup on Mirror server (with norecovery mode)
3. Try to connect mirror instance from principal server and vice versa ( to make sure mirror is contacting principal and principal is contacting mirror)
3. Verify the listener port using the below command
telnet sqlservername portnumber
telnet sqlservername 5022   -- If the port is not working it displays connection could not open, If it works then it doesn't prompt you anything on the console
4. Issue the below command from both principal and mirror instances

Grant connect on endpoint::mirroring to domain\account (If this doesnot works, use the below command)

Grant connect on endpoint::mirroring to public

Recovery Models in SQL Server

Recovery Models -- Recovery model defines how to maintain log file in SQL Server
There are 3 recovery models

1. Simple 2. Bulk – Logged 3. FULL

Simple Recovery Model 

Every DML & DDL operation will record into T-log file (to support basic recovery ability when SQL Server shutdown or a long operation is being cancelled)
Using this recovery model we can’t achieve point in time recovery
Log file gets truncated after the transactions being committed or checkpoint occurred.
No log backups can be generated in this mode
Not able to configure Logshipping or mirroring using this mode
In simple recovery model we can generate FULL, Differential backups (except TLog backups)
It is suitable for Test / DEV environments where point in recovery is not required.

Bulk Logged Recovery Model

Every DML & DDL operation will record into T-Log file except Bulk Logged operations such as SELECT INTO, BULK INSERT, CREATE INDEX etc.,
The advantage of using this recovery model is it doesn’t occupy large space of transaction log while doing bulk operations
If there are no Bulk operations then it acts like a FULL recovery model.
Logshipping can be configured using this recovery model, Mirroring cannot be configured.
Point in time recovery is possible however we need to re-run the Bulk operations.
In this we can generate all kinds of backups (full, diff, tlog etc)
It is suitable where normal data is critical whereas bulk operations are not critical.

FULL Recovery model

Every DML & DDL operation will record into T-Log file
Using this recovery model Point in time recovery is possible
Logshipping and Mirroring can be configured in this recovery model
Log file grows very faster
Checkpoint Process
It is a program executed by SQL Server to transfer all committed transactions from Transaction log to data file
It takes all committed transactions PageIds, LSN from buffer pool and writes into respective pages in the data file
Lazy writer is a process which takes pages from data cache and writes into data file
Checkpoint truncates TLog file, If recovery model is set to Simple
Checkpoint occurs periodically (based on recovery interval) or can be issued manually.
It occurs automatically when a backup start.
It occurs if size of Tlog file is 70% full.
When a new data file or log file was added to database.

How to attach MDF without transaction log file

We can attach MDF file without LDF in two ways:
Using SSMS
Using T-SQL


Using SSMS

SSMS - Databases - Rightclick - Attach - Browse and select the MDF file
In the database details section it displays two files
1) MDF file with location from where you want to attach
2) LDF file with some other location (Select this file and Click on Remove button)
3) Click OK (Database will attached to that location)

Using TSQL

 CREATE  DATABASE AdventureWorksDW2012 ON (FILENAME = N'E:\DATA1\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG


If Database has multiple log files you may not attach with SSMS use the T-SQL Syntax to do so, it will create only one ldf file while attaching not multiples.

SQL Server Database Mirroring Part III

Steps to add secondary data file to Mirrored Database

1. Make sure mirrored database is in synchronized state using below query - at principal server

SELECT db_name(database_id) as database_name, mirroring_state_desc,
mirroring_role_desc, mirroring_partner_name, mirroring_partner_instance
FROM sys.database_mirroring where mirroring_state_desc = 'SYNCHRONIZED'
2. Disable backup jobs at principal server if any

3. Run the below command to stop the mirroring -- at principal server
Alter database MirrorNew set partner off

Now you can observe db mirroring will be removed and mirror server database state becomes restoring

4. Add secondary data file / filegroup to principal server database - at principal server
alter database mirrornew add file
(name='second_datafile',
filename='E:\Program Files\Microsoft SQL Server\DATA\MirrorNew_second.ndf',
size = 50mb)
5. Make sure file got added to principal server database -- at principal server
select * from MirrorNew..sysfiles

6. Generate log backup of principal database -- at principal server
backup log mirrornew to disk='e:\backups\mirrornewwithsecondary.trn' with init

7. You can verify whether log backup has newly added file or not using the below command
restore filelistonly from disk='e:\backups\mirrornewwithsecondary.trn'

8. Copy the log backup to mirror server

9. Run the below command to verify the file location of the database
select * from sysaltfiles where dbid = DB_ID('mirrornew')

10. Restore the log file on mirror server database using with move option -- at mirror server

restore log mirrornew from disk='e:\backups\mirrornewwithsecondary.trn'
with norecovery, move 'second_datafile' to 'E:\Program Files\Microsoft SQL Server\\DATA\MirrorNew_second.ndf'
11. Verify newly created file added to database or nore -- at mirror server
select * from sysaltfiles where dbid = DB_ID('mirrornew')

12. Reinitiate the mirroring from mirror server -- at mirror server
alter database mirrornew set partner ='tcp://kalyanmirror:5022' -- It moves database into Inrecovery mode

13. Finally initiate mirroring step from principal server -- at principal server
alter database mirrornew set partner='tcp://kalyanprimary:5023'

Now database mirroring will established between principal and mirror, Execute the step 1 to make sure both databases are in sync state.

SQL Server Database Mirroring Part II

EndPoints -- These are used to define how servers will communicate each other. Database mirroring is based on TCP endpoints to send and receive information between servers instances that are exists on mirroring sessions.  One TCP endpoint will be used for multiple mirroring sessions. In database mirroring only committed transaction data is sent to mirror instance, rolled-back transaction data is not sent to the mirror server, however in log shipping both committed and rolled-back information will sent to secondary server.

Requirements

Service Accounts will need to be granted permission to connect to endpoints on the other partner instances (principal, mirror and witness). If SQL Server is not running in single domain the certificate authentication need to be used.

Creating Endpoints -- Configuring endpoints (port listeners) where the principal and mirror are going to establish communication.

Principal – Create Endpoint Principal_Endpoint state=Started as TCP(Listener_port =  5022) for database_mirroring (role = partner) – Enable as Partner only

Mirror – Create Endpoint Mirror_Endpoint state=Started as TCP(Listner_port=5023) for database_mirroring (role = all) – Enable as mirror or witness server
To view mirroring endpoints
Select name, role_desc, state_desc from sys.database_mirroring_endpoints;

Important points

Database mirroring is high availability solution for database
Database mirroring can be configured only with FULL recovery model database
Express edition can be configured only as witness server
Database mirroring cannot be configured with multiple mirror servers
Mirroring is specific to database, so logins will not moved to mirror server, need to move them manually
We cannot configure mirror for master, model, msdb and tempdb databases
Include failover partner connection string in ADO.NET to make use of automatic failover
One witness can be configured with multiple mirror servers
Mirror can be used as read-only using snapshots.
Snapshots can be created on mirror server.
Write a script on active instance to enable Agent jobs when failover happened, if automatic failover is configured.

SQL Server Database Mirroring Part I

Database Mirroring is a high availability solution for database availability. It maintains two copies of database to ensure database availability to minimize or not downtime. These two copies maintained on two different servers, one principal server – which serves the user requests and database on principal server is in available mode to users and the second mirror – which acts as a hot standby for principal server and database is in restoring mode (not available to users). We can bring secondary server (mirror server) online if something goes wrong with Principal server or it can be automated using Witness server.

When databases are in synchronized state the secondary server acts as a hot standby that supports immediate failover without data loss from committed transactions

When databases are not in synchronized state the secondary server acts as a warm standby (with data loss).

Requirements

Database mirroring is available in SQL 2005, 2008, 2008R2 and 2012
Database mirroring is available in enterprise and standard edition (In STD there are certain limitations)
Database mirroring works only on FULL recovery model, Simple and bulk-logged recovery models doesnot support.
Mirroring cannot be configured on system databases Master, Model, MSDB and Tempdb.
For automatic failover you need to configure witness server must have SQL Server 2005+ and can be any edition (standard, enterprise or express edition also)
We need to initialize the mirroring database (need to restore principal server full backup on mirror server on norecovery mode)

Mirroring Modes

Synchronous (High Protection Mode) – With this mode transactions cannot commit on the principal until all the transactions logs are copied to mirror server, mirror server sends acknowledgement to principal server in this mode. This mode guarantees that both principal and mirror servers are in sync and there wont be any data loss if principal server fails. It acts similar to High availability mode except the automatic failover (principal to mirror) process is manual.

Asynchronous mode (High Performance) – With this mode the overall architecture as a warm standby and doesn’t support automatic failover. The data transfer between principal and mirror server are asynchronous, Transaction log records are committed only on the principal server before the log records shipped to mirror server, in this mode if there is anything happen to primary database there will be a data loss.

High Availability (High Safety with witness) – It provides synchronous transfer of data between principal and mirror server databases also it supports automatic failure detection and supports automatic failover. This mode requires all three servers principal, mirror, witness servers, There will be no data loss because it uses synchronous data transfer.

In SQL Server 2008 few additional functionality been added to Database Mirroring

Automatic Page Repair -- If a corrupt page found in the principal (or mirror) server then database mirroring system will attempt to obtain page from the mirror (or principal) database then repair the corruption.

Logstream compression -- By compressing the log records before sending them to the mirror database which increase the performance of log records while travel across the network.