Latest Entries »

1.What is ACID property?

Atomicity-All or none

Consistency-Make sure that transaction never leaves database in an inconsistent state.

Isolation-keeps transactions separated from each other until they’re finished.

Durability-guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

2.What are the versions of SQL Server and its latest Service pack?

SQL Server 2000 (SP4),2005(SP4),2008(SP3),2008 R2(SP2),2012 (SP1)

3.What is the default port for SQL Server?

1433

4.What are recovery modes of SQL Server?

Full

Simple

Bulk-Logged

5.What are the types of backup in SQL Server?

Full

Differential

Transaction Log

6.Difference between SQL Server 2000,2005 & 2008?

Please refer below site

http://codemyne.net/articles/Differences-between-sqlserver-2000-2005-and-2008.aspx?visitid=81&type=2

(Thanks Renuka.)

7.How you check the version of SQL Server?

Select @@Version . In 2000 this will give only windows Service Pack information

8.How to take the backup in sql server?

Backup database [rkn] to disk=’path’

9.What is the difference between login and a user?

Login -server Level

User-Database Level

10.what are the system databases in sql server 2005 and its use?

Master ——– Logical repository for the system objects residing in the sys schema from SQL Server 2005 onwards In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.

Model —– Works as template for all user database.

MSDB —- Contains information about all jobs,dts packages,sql server agent configuration.

Tempdb ——- Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Reinitialised when SQL Server restarts.

Resource ——— This is a hidden database responsible for physically storing all of the SQL Server 2005 system objects

11.What are the main types of indexes in SQL Server?

Clustered & Nonclustered

These are very basic level interview questions,feel free ask any questions and I am always ready to help you.

I recommend everyone to always go through msdn.

Brief Introduction on SQL Azure

Microsoft SQL Azure Database is a cloud-based relational database service built on SQL Server technologies. SQL Azure (10.25.XXXX) now resides above SQL Server 2008(10.0.XXXX) and below SQL Server 2008 R2 (10.50.XXXX). It promises highly availability of 99.99%, scalability and multi-tenant database service in Cloud. High availability and fault tolerance is built-in and no physical administration is required.


The below image will give you an idea on what SQL Azure is. The application or client can connect to Azure database using internet connection. There are many different way to connect to SQL Azure databases; the most common are SSMS (SQL server 2008 R2), SQLCMD, Omega Web Client for SQL Azure and SQL Azure Manager.






Unlike normal SQL Server, the databases in SQL Azure may reside in different physical locations. The multiple replica of the database is kept in different physical locations which ensure high availability of databases.


Architecture


SQL Azure uses four different layers for application to connect to databases namely the client layer, the services layer, the platform layer, and the infrastructure layer.



SQL Azure Compatibility


SQL azure supports almost everything like Tables, Indexes, views, Stored Procedures, Triggers, Table Variables, Temp tables etc. Currently SQL azure not support distributed transactions, Distributed query, CLR, Service Broker, Physical Server or Catalog DDL and Views.


Connectivity & Tools


SQL Azure can be connected the same way you connect to normal SQL Server. We have many different options to connect like SSMS (SQL server 2008 R2), SQLCMD, OLEDB, Omega Web Client for SQL Azure and SQL Azure Manager. You must make sure that you have allowed IP or range of IP to connect to your Azure account in server info page which will be provided when you create an account.



SQL Azure manager can be downloaded from http://hanssens.org/tools/sqlazuremanager/.



Omega Web Client can be accessed from https://onlinedemo.cerebrata.com/SQLAzureClient/default.aspx



Many numbers of tools that support SQL Azure are available for various purposes like verifying the code, migrating existing databases to Azure etc. You can view them in http://beyondrelational.com/wikis/sqlazure/sql-azure-tools.aspx


SQL Azure is available now in two flavours, Web and Business.






















SQL Azure Flavours Consumption
Web Databases $9.99 per database up to 1 GB per month
$49.95 per database up to 5 GB per month
Business Databases $99.99 per database up to 10 GB per month
$199.98 per database up to 20 GB per month
$299.97 per database up to 30 GB per month
$399.96 per database up to 40 GB per month
$499.95 per database up to 50 GB per month

As per Microsoft the monthly Availability Service Level for SQL Azure is


i. Definitions


1. “Total time intervals” is the number of 5-minute intervals in a monthly billing cycle, calculated by multiplying the number of days in the cycle by 24 * 60 / 5.


2. A 5-minute interval is marked as unavailable if all the customer’s attempts to establish a connection to SQL Azure fail or take longer than 30 seconds to succeed, or if all basic valid read and write operations (as described in our technical documentation) fail after connection is established. Failures caused by the software, hardware or network at the site used by the Customer to connect to SQL Azure are not included.


3. “Scheduled Downtime” means those times where Microsoft notifies Customer of periods of Downtime at least five days prior to the commencement of such Downtime. Scheduled Downtime of fewer than 10 hours per calendar year is not considered Downtime for purposes of this SLA.


4. “Monthly Uptime Percentage” for a specific Customer is calculated by taking the total number of minutes in a calendar month multiplied by the total number of users minus the total number of minutes of Downtime experienced by all users in a given calendar month, all divided by the total number of minutes in that calendar month multiplied by the total number of users. This is reflected in the following formula:



ii. Uptime Service Levels












Monthly Uptime Percentage Service Credit
<99.9% 10%
<99% 25%

Conclusion


This article is mainly intended for those who want to know what SQL Azure is and how to start using it.


Please feel free to comment on this and are really appreciated.

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 successfully written or not but it can’t 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

Can we rollback Truncate???

I got the inspiration to write this 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 .

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
;

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 FilesMicrosoft SQL Server100DTSPackagesPackage_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, 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

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.

The above figure shows the data encryption used by Symmetric keys and the one below shows how data decryption happens in Symmetric key Algorithm.

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

Follow

Get every new post delivered to your Inbox.