RATHEESH K NAIR

SQL Server Page Verification

2010-07-18T22:57:00.000-07:00

You know how SQL server checks whether the page is good or corrupted?

There are two ways by which SQL Server finds the corrupted page:Torn Page Detection and Checksum.

Checksum is a new type of page verification introduced in SQL Server 2005 which offers better corruption checking than torn page detection.This is why when we create a new database in SQL Server 2005 or 2008 the checksum option is turned on by default.

What Checksum do?
For a database with checksum ON SQL Server computes the checksum of the page both when a page is written and when a page is read. The SQL Server computes the checksum based on the bit pattern on the page, stores it in the page header and then issues an I/O to write the page. When the SQL Server reads the page, it re-computes the checksum using the same logic and then compares it with the value available in the page header. If the checksum value matches then it is assumes the page did not get corrupted during the write-read cycle.Keep in mind that checksum is not 100% accurate and there are cases when corruption remains undetected even if you have checksum enabled.

How Torn Page Detection works?
Torn page detection on the other hand does a different type of page verification in which it writes a bit for every 512 byte of a page. This will help you in finding whether the page has been succesfully written or not but it cant tell you whether the data you have written is correct or not. We can use the same checksum for verifying backup.This will give added protection for backup.

How to enable page verification?
--CHECKSUM
ALTER DATABASE RKN set PAGE_VERIFY CHECKSUM

--TORN PAGE DETECTION
ALTER DATABASE RKN SET PAGE_VERIFY TORN_PAGE_DETECTION

You can also change the page verify option using GUI
DATABASE PROPERTIES-->OPTIONS-->PAGE VERIFY Here you can select the type of page verification you want.

LOCKS in DB2

2010-07-12T22:25:00.000-07:00

I found it not as easy as SQL Server to manage DB2 servers when it comes to administration.

Here I am briefing on how to find the locks on DB2 and how to configure the configuration manager so that we can capture the locks.

First we need to update the database manager for configuration to change the parameter DFT_MON_LOCK to ON.

UPDATE DATABASE MANAGER CONFIGURATION USING DFT_MON_LOCK ON

Then we need to update the monitor switches so that we can view the locks

UPDATE MONITOR SWITCHES USING LOCK ON
The above command will help you in updating the monitor switch.

Now we have all set so that we can check the locks on particular database.

GET SNAPSHOT FOR LOCKS ON DB_NAME will give us the list of all locks currently on that particular database.

We can kill the process by using the command

FORCE APPLICATION(APPLICATION_HANDLE)
APPLICATION_HANDLE is a unique number which we will get when we check the lock using GET SNAPSHOT FOR LOCKS ON DB_NAME along with the object name causing lock.

FORCE APPLICATION ALL will kill all the process on the particular database where we executing the command.

Here comes a handy script for viewing locks.This is very helpful as it gives a detailed information on application connecting,IP address,Victim etc.

--SCRIPT TO VIEW LOCKS
Select substr(ai.appl_name,1,10) as Application,
substr(ai.primary_auth_id,1,10) as AuthID,
int(ap.locks_held) as "# Locks",
int(ap.lock_escals) as "Escalations",
int(ap.lock_timeouts) as "Lock Timeouts",
int(ap.deadlocks) as "Deadlocks",
int(ap.int_deadlock_rollbacks) as "Dlock Victim",
substr(inbound_comm_address,1,15) as "IP Address"
from sysibmadm.snapappl ap, sysibmadm.snapappl_info ai
where ap.agent_id = ai.agent_id
;

Symmetric Key Encryption

2010-06-18T05:30:00.000-07:00

Symmetric Key Encryption
In a symmetric key algorithm, there is but one key. That same key is used to encrypt the data and decrypt, the data. If someone were to get possession of the key, that person could take anything you've encrypted, and decrypt it immediately.

Advantages
Symmetric key encryption is known to be faster and stronger than their asymmetric counterpart. It uses less overhead on system resources. For simple encryption this is the best way.
When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following: certificate, password, symmetric key, asymmetric key, or PROVIDER. The key can have more than one encryption of each type. In other words, a single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys, and asymmetric keys at the same time
Here I am going to explain the simplest way of encrypting data in a database, i.e. Symmetric Key Encryption with Password
Symmetric Key Encryption with Password
Using this method is very simple and can be used if we want to encrypt data in whole column.
The simple example below will help you understand.
USE master
GO
CREATE DATABASE RKN_Test
ON PRIMARY ( NAME = N'RKN_Test', FILENAME = N'D:\RKN_Test.mdf')—Any Path you prefer
LOG ON ( NAME = N'RKN_Test_log', FILENAME = N'D:\RKN_Test_log.ldf')
GO

Master key is used to encrypt the Certificates and Keys in a database.
Passwords should meet windows password policy if you have set any.

USE RKN_Test
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'RKN@123'
GO

We can use many types of algorithm while creating Symmetric keys like DES, TRIPLE_DES, TRIPLE_DES_3KEY, AES_128, AES_192, AES_256 etc.Please be careful while choosing the encryption algorithm.

USE RKN_Test
GO
CREATE SYMMETRIC KEY RKN_TableKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD='Passw0rd1'

USE RKN_Test
GO
CREATE TABLE TestTable (FirstCol INT, SecondCol VARBINARY(256))
GO

SELECT * FROM TestTable

USE RKN_Test
GO
OPEN SYMMETRIC KEY RKN_TableKey DECRYPTION
BY PASSWORD='Passw0rd1'
INSERT INTO TestTable VALUES (1,
ENCRYPTBYKEY(KEY_GUID('RKN_TableKey'),'Testing'))
GO
CLOSE SYMMETRIC KEY RKN_TableKey

USE RKN_Test
GO
OPEN SYMMETRIC KEY RKN_TableKey DECRYPTION
BY PASSWORD='Passw0rd1'
SELECT FirstCol,CONVERT(VARCHAR(50),DECRYPTBYKEY(SecondCol)) AS DecryptedValue
FROM TestTable
GO

SSIS DEPLOYMENT

2010-05-18T22:44:00.000-07:00

SSIS Packages can be deployed in 2 ways
1. File System Deployment
2. SQL Server Deployment
File System Deployment
This will install SSIS package and their dependencies to the specified folder in the file system.
SQL Server Deployment
This installs the SSIS package in SQL Server. This option is typically used if you use SQL Server for sharing SSIS packages between Servers. Package dependencies will be installed in a folder in the file system.
File System Deployment
Double Click the file having .SSISDeploymentManifest extension automatically takes to package installation wizard.

Click Next

Here we can choose the type of deployment. There is check box available in this page which enables us to validate the package after installation
Going ahead with File system Deployment takes you to a page where we can specify the location on which package to be installed

By default the location is Program Files\Microsoft SQL Server\100\DTS\Packages\Package_Name
We can change the location using browse button provided.
Click NEXT will take you to a confirm installation page

Click NEXT shows Finish installation page which contains the summary
-------------------------
SQL Server Deployment

Select SQL Server Deployment in wizard, click next

Give preferred authentication (Windows or SQL Server)
Entering proper username and password enables the browse button for selecting the package location.

Select the location to which the package has to be saved and click OK.

Click NEXT

Select the folder for installing the package dependencies and then click NEXT
After confirm installation page click NEXT

Click Finish.
Thus your package is saved in SQL Server.
You can view the package by connecting to SQL Server Integration service, expand stored packageMSDBspecific folder on which the package have been installed

NB:I am not able to paste the images of every step which I create for SSIS deployment steps

can Truncate Rolled back??

2010-05-18T20:57:00.001-07:00

This is my very first blog and I got the inspiration to start only because of misunderstanding that exists on truncate command.
The question is can we rollback Truncate?
In many webistes and forums I found the answer to be a big NO and I know many who reading this blog have the same mind set.
The exact answer to the question is YES..Amazed??
we can verify the same by simple scripts
Just create a simple table insert some values truncate that and run rollback.
USE RKN
CREATE TABLE TEST(NAME CHAR('10'))
SELECT * FROM TEST
This will give an empty result
INSERT INTO TEST VALUES('RKNAIR')
GO 1000
Will insert 1000 records
Now I am truncating the table
BEGIN TRAN
TRUNCATE TABLE TEST
We can cross check whether the table got truncated by selecting the whole table.
SELECT * FROM TEST
This will give empty result as expected.
Now execute the rollback command
ROLLBACK
We can check the roll back worked by again issusing the select.
SELECT * FROM TEST
AMAZED????? :-)
Yes we can rollback Truncate.
Many believe truncate that truncate is a no logged operation which is far from truth. Truncate is MINIMALLY LOGGED and can be rolled back.
Hope this will help.

Please post your comments .