SQL Server Backup compression

How to do Backup compression in SQL Server?

The backup compression option determines whether SQL Server creates compressed or uncompressed backups .Backup compression option is off by default in SQL Server.The default behavior can be modified by sp_configure option “backup compression default” .

Syntax:

USE master;
GO
EXEC sp_configure ‘backup compression default’, ‘1’;
RECONFIGURE WITH OVERRIDE;

To override the backup compression:

You can change the backup compression behavior for an individual backup by using WITH NO_COMPRESSION or WITH COMPRESSION in a BACKUP statement. We cannot take compressed and non-compressed backups on the same file. If we take COMPRESSION backup on a file were already non-compressed backup has taken,error will be shown.So we have to use different files for compressed and uncompressed backup.

Example to take backup for AdventureWorks with NO_COMPRESSION:

BACKUP DATABASE [AdventureWorks] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\uncompressed.bak’ WITH NO_COMPRESSION
GO

Example to take backup for AdventureWorks WITH COMPRESSION:

BACKUP DATABASE [AdventureWorks] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\compressed.bak’ WITH COMPRESSION
GO

To calculate the compression ratio of a backup:

After taking backup with compression and without compression the backup_size can be compared to see the difference.

Syntax:

select backup_size,compressed_backup_size,100- ((compressed_backup_size/backup_size)*100) as “compressed %” from msdb..backupset

Types of isolation levels in SQL Server

The ISO standard defines the following isolation levels in SQL Server Database Engine:

Microsoft SQL Server supports these transaction isolation levels:

Read Committed

SQL Server acquires a share lock while reading a row into a cursor but frees the lock immediately after reading the row. Because shared lock requests are blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed. Read committed is the default isolation level setting for both SQL Server and ODBC.

Read Uncommitted

SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursors can be populated with values that have already been updated but not yet committed. The user is bypassing all of the locking transaction control mechanisms in SQL Server.

Repeatable Read

SQL Server requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction instead of being freed after the row is read. So phantom rows are This has the same effect as specifying HOLDLOCK on a SELECT statement.

(Phantom read:Phantom reads occurs when an insert or delete action is performed against a row that is being read by a transaction.The second transaction read shows a row that did not exist in the original read as the result of an insertion by a different transaction or due to deletion operation some rows doesn’t appear)

Serializable

In serializable read phantom reads are not allowed because while the first transaction is in progress other transaction wont execute.


Snapshot: SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursor is populated with the values as of the time when the transaction first started. Scroll locks are still requested regardless of use of snapshot isolation.

Read uncommitted example: Uncommitted Read allows your transaction to read any data that is currently on a data page, whether that has been committed or not. For example,another user might have a transaction in progress that has updated data, and even though it’s holding exclusive locks on the data, your transaction can read it anyway.

If we execute the select query before the update transaction gets committed,it will not wait for the update transaction to commits.Query will be executed immediately without any time lapse.

Read Committed example

Read committed allows your transaction to read only if the data is committed.Read Committed operation never reads data that another application has changed but not yet committed.

If we execute the select query before the update transaction gets committed,it will wait till the update transaction gets committed.

Repeatable Read example :In Repeatable Read issuing the same query twice within a transaction will not make any changes to data values made by another user’s transaction.Repeatable Read allows phantom reads(Data getting changed in current transaction by other transactions is called Phantom Reads).So phantom rows will appear.

While the transaction(first query) is in progress,repeatable read allows another transaction(second query) to execute.It means it allow phantom reads.So second transaction(second query),need not wait till first transaction(first query) completes.Here values will be added before first query completes.

Serializable example : The Serializable isolation level adds to the properties of Repeatable Read by ensuring that if a query is reissued, rows will not have been added in the table. In other words, phantoms will not appear if the same query is issued twice within a transaction.

While the transaction(first query) is in progress,serializable read does not allow another transaction(second query),It means it don’t allow phantom reads.So second transaction(second query), must wait till first transaction(first query) completes.Here values will be added only after first query completes.

snapshot example : To use the snapshot isolation level you need to enable it on the database by running the following command

ALTER DATABASE DBname
SET ALLOW_SNAPSHOT_ISOLATION ON

SQL Server database snapshot

What is the use of SQL Server database snapshot and how to create SQL Server database snapshot?

Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots.

A database snapshot is a read-only, static view of the source database.Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation.A snapshot persists until it is explicitly dropped by the database owner.

Snapshots can be used for reporting purposes.In the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot’s creation.

Creating a series of snapshots over time captures sequential snapshots of the source database. Each snapshot exist until it is dropped.Each snapshot will continue to grow as pages in original database are updated, you may want to conserve disk space by deleting an older snapshot after creating a new snapshot.

Steps to create database snapshot in SQL Server:

1.Create a new database or use existing database to create the database snapshots.

Syntax:

USE master
GO
— Create database
CREATE DATABASE test
GO
USE test
GO

2.Create table and Insert values into table.

Syntax:

–create a Table
CREATE TABLE test1 (a INT, b INT,c INT)
—Insert values
declare @a nchar(10)
set @a=1
while (@a<5)
begin
insert into test1 values (@a,’1′,’1′)
set @a=@a+1
end

3.Create a snapshot for a source database.

Syntax:

— Create Snapshot Database
CREATE DATABASE Snapshottest ON
(Name =’test’,
FileName=’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\testsnap.ss’)
AS SNAPSHOT OF test;
GO

Snapshottest database is created successfully.

4.Now let us view both the source database and snapshot database.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

Data’s in tables of source and snapshot database are same.

Size on disk of the testsnap .ss could be viewed by opening its properties window.Let us note the size on disk of the source database.

5.Let us update existing rows in the table.

Syntax:

update test1 set a=0,b=0,c=0

6.After updating rows ,let us view the source and snapshot database.The values are updated only in source database,not in snapshot database.Because snapshot database is consistent with the source database as of the moment of the snapshot’s creation.

Syntax:

— Select from test and Snapshottest Database
SELECT * FROM test.dbo.test1;
SELECT * FROM Snapshottest.dbo.test1;
GO

But size on disk is increased,It clearly shows that when we update data in Source database, it copies the old/existing data pages to Snapshot database. This is the reason why the size of the snapshot database is increasing while updating the source database.

7.We could revert the source database.Reverting overwrites updates made to the source database since the snapshot was created by copying the pages from the sparse files back into the source database.

Syntax:

— Restore old data from Snapshot Database
USE master
GO
RESTORE DATABASE test
FROM DATABASE_SNAPSHOT = ‘Snapshottest’;

8.View the data in the tables from the source and the snapshot database after the restore from snapshot.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

9.We drop the snapshot database like any other database using drop database command.

Syntax:

— drop snapshottest
DROP DATABASE [snapshottest];

10. We can also create database snapshot on mirror database for load balancing.

How to create table with filestream column and Insert data

How to create table with filestream column and Insert data?

Filestream data type in SQL Server can be used to store images,documents,etc., in database.

In this blog I will explain how to create table with filestream data type.

Before creating table with filestream column,we have to enable filestream feature in SQL Server configuration manager.Follow “How to enable and configure Filestream in SQL SERVER 2008 / 2012”

Create a table and adding filestream data:

1.Create Filestream enabled database.

Syntax:

CREATE DATABASE test
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

2.After you have a filestream enabled database.we now need to create a table and add data to it.

Syntax:

CREATE TABLE [dbo].[test](
   [ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [Number] VARCHAR(20),
   [Description] VARCHAR(50),
   [Image] VARBINARY(MAX) FILESTREAM NULL
)

3.Insert values into table. In below example I am inserting image file in to the table.

Syntax:

DECLARE @img AS VARBINARY(MAX)
— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(Bulk ‘C:\image\jellyfish.jpg’, SINGLE_BLOB ) AS y
— Insert the data to the table
INSERT INTO test (ID,Number,Description, Image)
SELECT NEWID(), ‘MS1001′,’jellyfish’, @img

4.To view the table with filestream included column

How to enable and configure Filestream in SQL SERVER 2008 / 2012

How to enable Filestream in SQL SERVER 2008 and SQL Server 2012?

Filestream was introduced in SQL Server 2008 for the storage and management of unstructured data.

Follow the below steps to enable this filestream in SQL Server2008.Let us see one by one.

To enable filestream through SQL Server configuration manager:

1.Open SQL Server configuration manager.Open SQL Server services

2.Select the instance for which you want to enable Filestream.Right click the instance->properties.

3.In the SQL Server Properties dialog box, click the Filestream tab.

4.Select the Enable Filestream for Transact-SQL access.

5.If you want to read and write Filestream data from Windows, click Enable Filestream for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

6.If remote clients must access the Filestream data that is stored on this share, select Allow remote clients to have streaming access to Filestream data.

7.Click Apply.

Enable Filestream access level server configuration option:

In SQL Server Management Studio, click New Query.Execute the below query

[0 -Disables FILESTREAM,1 -Enables FILESTREAM for T-SQL,2 -Enables FILESTREAM for T-SQL and Win32 streaming access]

Syntax:

EXEC sp_configure filestream_access_level,2

RECONFIGURE with override

Create filestream enabled database:

We can enable file stream while creating the database (or) If the database is already created we can enable filestream using alter database.

To create file stream enable database you can use below query

CREATE DATABASE DBname
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

Enable filestream on existing database:

To enable file stream on existing database you can use alter database command with SET FILESTREAM similar to the example below or SSMS

ALTER DATABASE [DBNAME] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’Directoryname’ ) WITH NO_WAIT
GO

Enable filestream for database using SQL Server 2008 Management Studio:

1. Connect to SQL Server Instance using SQL Server Management Studio

2. In the Object Explorer, right click the instance and select Properties.

3. On the left panel click on the Advanced tab, then click on the drop down list of Filestream Access Level and select Full access enabled option.

4. Click Ok to save the changes.

How to import/Export data in SQL Server

SQL Server Import and Export Wizard can be used to copy data from a source to a destination. source and destination can be sqlserver or any other connection.

Steps to export data in SQL Server 2008:

In Microsoft SQL Server Management Studio, expand the databases.

Right-Click on the database you want to copy to another database->Tasks->Export data/import data.

SQL Server Import and Export Wizard window will open.

Click Next to continue. Now, you will need to choose a Data Source. You can leave the Data source as SQL Server Native Client 11.0. Enter the Server name. Check windows Authentication for your database. From the Database dropdown menu, select the name of your database.

Click on Next to continue.

In the Choose a Destination window, you can select a different option to export to Microsoft Access, Microsoft Excel,SQL Server database,etc.,

Then select the destination database,where you want to export your data.

Specify whether to copy tables or to copy the results of a query from the data source.Click Next.

Select source tables and click Next.

Check Run immediately.Click Next.

Click Finish to complete the wizard.

Close the wizard after completing it successfully.

Create script for all objects in database with data

You can create T-SQL scripts for database, objects in database and insert script for data in tables by using the Generate Scripts Wizard.

These scripts can be used to create database in secondary site with and with out data.

I this blog I will explain how to generate T-SQL script to create database, database objects and data.

Right click the database which you want to script—>Select tasks—>Generate scripts.

Generate and Publish Scripts wizard screen appears.click Next to continue.

In the Choose Objects wizard screen Select “Script entire database and all database objects” option if want to script all the objects in database or select specified objects which you would like to script and click Next to continue.

In the Set Scripting Options wizard screen.Select the output type as “Save scripts to a specific location” and specify the path where you would like to save the database script file .Click the Advanced button for specifying the schema and data scripting option.

In Advanced Scripting Options screen; choose the option Schema and data for the Types of data to script option if you want to generate insert scripts for rows in tables and click OK to save the changes and to return to Set Scripting Options wizard screen.

a) Data only – If this option is selected, it will only script out data within the tables
b) Schema and data – If this option is selected, it will script out Schema as well as the data within the selected objects
c) Schema only – If this option is selected, it will script out the Schema only

In the Summary wizard screen; you will be able to see a quick summary of all the options which you have selected so far. Click Next to confirm and generate the scripts.

In Save or Publish Scripts wizard screen; you will be able to see a Success or Failure message against each object for which the script was requested to be generated. Finally, click Finish to close the wizard.

Once the script is generated you can view it like any other SQL Script.

Steps to enable Alwayson in SQL Server 2012

Steps to configure Always on availability groups in SQL Server 2012

1. Always on availability groups can be configured in standalone or clustered SQL Server instance.

2. All SQL Server instance (principal and all replicas) must reside on servers which are part of same Windows Server Failover Cluster.

3. The server instance must be running an edition of SQL Server that supports AlwaysOn Availability Groups

To install and configure windows failover cluster follow the steps in HOW TO CREATE CLUSTER USING HYPER-V

Ensure all the systems which has SQL Server instances and will host principal and all replica databases are part of same windows cluster.

Note: Windows failover cluster can be installed and configured without shared disk starting from windows2008 unlike windows2003.

Once the cluster is configured follow the below step by step guide to setup always on

In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server for which you want to enable AlwaysOn Availability Groups, and click Properties.

Select the AlwaysOn High Availability tab.

Verify that Windows failover cluster name field contains the name of the failover cluster. If this field is blank, this server instance currently does not support AlwaysOn Availability Groups. Either the local computer is not a cluster node, the Windows Server Failover cluster has been shut down, or this edition of SQL Server 2012 that does not support AlwaysOn Availability Groups.

Select the Enable AlwaysOn Availability Groups check box, and click OK.

Open SQL Server Management Studio.

Choose any one instance to become PRIMARY. Expand Alwayson High availability->Right-click Availability Groups and select New Availability Group wizard.

New Availability group introduction window will appear,Click Next.

Specify availability group name and click Next.

Select the user databases for the availability group(Database Should have Full backup) and click Next.

Specify an instance of SQL Server to host a secondary replica by selecting Add replica.

Add NODE2 as secondary replica by connecting it.

After selecting secondary replica,Optionally, you can specify endpoint details or leave them default.

Set the backup preference.

Specify your preference for an availability group listener that will provide a client connection point or else select do not create an availability group listener now.

Select your data synchronization preference as Full and specify a shared network location accessible by all replicas.

Summary of availability group validation will be displayed,Click Next.

Verify the choice made in this wizard.Click Finish.

Wizard completed successfully,Click close and finish the availability group wizard.

Successfully we have created a availability group called AGroup.

You can suspend and resume data movement by expanding Availability groups->Group name->Availability databases->Choose the database –>resume or suspend database.

You can view the synchronization status through the dashboard reports.

If you want to do a failover,right click the available group->group name->Failover.

How to create database mirroring

What is database mirroring and How to create database mirroring?

Database mirroring is software solution for increasing database availability.It maintains two copies of single database,one as principal database and other as mirror database.So during disaster if primary database server fails then mirror database can be used by application to access the data.This reduces the down time caused during disasters. Mirrored database can not be accessed by application unless roles are switched (Transferring principal role to mirror server) but database snapshot can be created on mirrored database which can be used for read only queries (Load balancing). Refer SQL Server database snapshot steps to create snapshot

Database Mirroring can be implemented only on databases,that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring.

Database mirroring is not applicable in system databases(master,msdb, tempdb, or model databases)

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.

There are two mirroring operating modes.

1.High-safety mode:Commit changes at the principal and then transfer them to the mirror.It is asynchronous.

2.High-performance mode:Always commit changes at both the principal and mirror server.It is synchronous

All database mirroring sessions support only one principal server and one mirror server.

Steps to create mirroring:

I have two instances SQLPC\PRADEEPA which will act as principal server and SQLPC\PK92 will act as mirrored server.

Choose a database in the principal instance which will act as Principal database,

I have created database named “Mirrordatabase” for which we are going to start mirroring.

Create a backup for the Mirrordatabase in the principal server.

Set the backup type in full recovery mode.Select back up destination as disk.Select add to add the destination folder.

In destination,add the destination folder with .bak extension and click ok.

Click ok and complete the backup.

Now restore the backup of the database in mirrored server (destination server) with same name.

Type the name of the database and restore the database from device.

Select the backup file and click ok.

Now backup location is added.click ok and continue.

Select the backup sets to restore.Then click Options from select a page.

Select the recovery state as RESTORE WITH NORECOVERY.Also change the restore file name in restore as if you are creating mirror on same server for testing.Click ok.

Database restore completed successfully.

Configure mirroring in primary database by selecting properties.

In Database properties window select mirroring and configure it.

Here we dont have witness server.But we can include witness server instance also.Click Next.

Specify principal server instance,listener port and endpoint name.Click Next.

Connect mirror server instance.

Connect it.

Click Next and continue.

Service accounts could be created in this window.

Click Finish and complete the wizard.

Close the window after configuring database mirroring.

Start the mirroring by clicking start mirroring.

We could select either High performance mode or High safety mode based upon our application need from below window.

Mirroring is successfully created.

You could perform failover between primary database and mirror database by selecting Failover option in properties window.

How to create log shipping in SQL Server

What is log shipping and how to create log shipping in SQL Server 2008?

SQL Server Log shipping automatically allows you to send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases.

Log shipping can be used with databases using the full or bulk-logged recovery models.

Steps to create log shipping in SQL Server 2008:

On the primary server, right click on the database for which you want to enable log shipping and select Properties. Then select the Transaction Log Shipping Page. Check the “Enable this as primary database in a log shipping configuration” check box.

Select Backup settings.Specify the network path to backup folder and Click ok.

Select secondary database settings.Connect secondary server instance.

After connecting,select the secondary database.

Then initialize secondary database by Specifying how you want to restore the backup in secondary database.

Select copy files option.Specify the destination folder for copied files.

Select the Restore transaction log option.Select the database mode as standby mode.Click ok.

View the configuration options we selected and click ok.

successfully we have configured Log shipping.Click ok and finish the configuration.

HOW TO INSTALL SQL Server CLUSTER IN HYPER-V

To install SQL Server failover cluster we need to have windows cluster configured with a shared disk.

Step 1

If you do not have a windows cluster configured you can create using Hyper-v

Follow the steps in “HOW TO CREATE CLUSTER USING HYPER-V” to create Domain controller and two member servers which will act as nodes of the cluster.

Once the domain controller and two member servers are created proceed to step 2

Step 2

Once the domain controller and nodes are created install failover cluster feature and create cluster

To install the failover cluster feature and create a cluster follow the steps in “How to install cluster in windows 2008 and windows 2012“

Step 3

Once the cluster is created we have to create shared Disk for installing failover aware clustered application like SQL Server.

Follow the steps in ”How to create shared disk using iSCSI Software Target” to create a shared disk using iSCSI target software.

Once shared disk is configured invoke Setup from SQL server installation media .Make sure the node in which you perform the installation has shared disk online.

Run the setup.

Start the installation by selecting New SQL Server Failover Cluster Installation option.

Setup support rules will analyze and identify the problems that might occur during setup installation.

After setup validation ,you will get details about operations completed,Click Next and continue

On the Product key page, specify the Product ID key of the product. Click Next.

Then read and select the check box to accept terms and agreements.Click Next.

Select the developer features which you want and click Next.

Perform instance configuration.Give SQL server network name.Either you could have default instance or named instance.But you could have only one default instance.

Click Next.

Allocate required disk space for your installation.Click Next.

Select the SQL Server cluster resource group name.Click Next and continue.

Cluster disk selection will help us to view the disk we have selected for SQL Server failover cluster.

Configure the network resource for your SQL Server failover cluster.Select DHCP check box if you are going to use DHCP network.Otherwise give the IP address for your network(IP address which you provide not be in use currently).

Select the database engine domain group and SQL Server agent domain group. These groups have to be pre created in AD before the installation and startup account of SQL Server services will be added to these groups. You can also use service SIDs option.

Specify the account name and password for SQL server agent and SQL server database engine.Click Next.

Specify the authentication mode for your SQL Server failover cluster.Click Next and continue your installation.Verify the cluster installation rules.

Enable error reporting service if you want or else,click Next and continue.

Cluster installation rules will run the setup to determine whether any operations will block the installation.After operation completes.Click Next and continue.

Ready to install page will display the summary of options which we have selected during the setup process.If you want to make any changes,go back and make the required changes or else Click Install and start the installation.

Click Close and Complete the installation process.

Successfully we have installed the SQL server failover cluster in first node.

We can install the SQL server failover cluster in remaining nodes by selecting Add node to a SQL Server failover cluster in installation centre window. To add node to existing SQL Server failover cluster follow the steps in “How to add node to SQL Server cluster”

How to create merge replication in SQL Server

How to create merge replication in SQL Server?

For all types of replication it is mandatory to create distribution for the publisher instance before configuring replication. For configuring distribution follow the steps in “How to configure distribution for replication”

Once the distribution is configured follow the below steps to create publisher

Let us create New publisher:

Right click Local publication->New Publication

New publication wizard appears.Click Next and continue.

Select the database that contains the data or objects you want to publish.Click Next.

There are different types of publication.

Select the publication type that supports your application.Click Next.

Specify the SQL Server version that will be used by subscribers for this publication.Click Next.

Select the tables to publish as articles.Click Next.

If you don’t have unique identifier in the table.Then SQL Server will automatically add unique identifier to the table.Click Next.

Add filters to exclude unwanted rows from published tables.Click Next.

Specify when to run the snapshot agent and click Next.

Select security settings.

Specify the domain or machine account under which the snapshot agent process will run.

Select Run under the SQL Server agent service account.Click ok.

After selecting the account for snapshot agent and log reader agent,click Next.

Check create the publication.Click Next.

Give the publication name and finish the wizard.

Close the window after creating publication successfully.

Let us create the subscribers:

Before creating subscribers create one empty database where we could subscribe the publisher database.

Right click Local subscriptions->New subscriptions’

New subscription wizard window appears.Click Next and continue.

Choose the publisher and click next.

Select the distributor agent location.Click Next.

Select subscription database which you created.The database should be empty database.Click Next.

Specify the process account and connection option for each distribution agent.

Specify the domain or machines account under which the distribution agent process will run and click ok.

Schedule the agent and click Next.

Specify the type of subscription as client or server and click Next.

Check create the subscription and click Next.

Click Finish and complete the wizard.

Close the window after creating subscription.

clip_image001[56]

Successfully we have created publisher,distributor and subscriber for merge replication.

How to start replication agent in command prompt.

In this blog I will explain how to run replication agents from command prompt and enable additional parameters for agent’s while running from command prompt.

All the replication agent’s (Snapshot agent, Logreader agent, distributor agent, Merge agent) can be run from command prompt.

All these replication agents will have corresponding exe in C:\Program files\Microsoft SQL Server\100\COM folder

Snapshot agent –>Snapshot.exe

Logreader agent—>Logread.exe

distributor agent –>Distrib.exe

Merge agent –>Replmerge.exe

Before we start running the replication agents from command prompt we have to stop and disable the corresponding agent job in management studio by expanding jobs->Right click job->Stop Job

After stopping the job.Disable it.

Copy the folder path in which replication agent exe’s are located ( C:\Program files\Microsoft SQL Server\100\COM I have highlighted the agent binaries for visibility)

Open a command prompt and Change the directory to C:\Program files\Microsoft Sql Server\100\COM in command prompt.

Running Logreader agent from command prompt:

Double click Log reader Job.

Select steps –>Double click Run agent.

Copy the command and paste it in command prompt.

To run Log read agent from cmd type logread.exe in command prompt.Then paste the command that you copied from Job step properties.

We can monitor the progress of agent from command prompt.

We can also add additional parameters when we run the agents from command prompt. I will show you how to add verbose level parameter to increase the level of logging when running agent from CMD. (–Outputverboselevel 3 increases the level of logging by replication agents).

Like below.

Running Replication distributor agent:

Double click the distributor job.

Then select steps->Run agent.

Copy the command.

Change the directory to C:\Program files\Microsoft SQL Server\100\COM.

Then give distrib.exe –>copy the command from Job step properties.

Now we could start distributor agent in command prompt itself.

Running Snapshot agent:

Double click snapshot agent.

Select Steps->Run agent.

Change the directory to C:\Program files\Microsoft SQL Server\100\COM.

Then give snapshot.exe –>copy the command from Job step properties.

Then we could start the snapshot agent in command prompt.

How to create transactional replication in SQL Server

How to create transactional replication in SQL Server?

Replication allows as to automatically distribute copies of data from one server to one or more distributor server.In replication we will have Distributor,Publisher and Subscriber.

In transactional replication snapshot of the publication database objects and data is saved.If there is any data changes and schema modifications made at the Publisher,it will be delivered to the Subscriber. The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher.Therefore, within a publication, transactional consistency is guaranteed.

Transaction replication has three agents namely,Snapshot agent,Log reader agent and distribution agent .

The Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder.

The Log Reader Agent monitors the transaction log of each database that is configured for transactional replication and copies the transactions from the transaction log into the distribution database.

The Distribution Agent is an executable that moves the snapshot and the transactions held in the distribution database tables to the destination tables at the Subscribers.

Once the distribution is configured follow the below steps to create publisher

Let us create New publisher:

Right click Local publication->New Publication

New publication wizard appears.Click Next and continue.

Select the database that contains the data or objects you want to publish.Click Next.

There are different types of publication.

Select the publication type that supports your application.Click Next.

Select the tables to publish as articles.Click Next.

Add filters to exclude unwanted rows from published tables.Click Next.

Specify when to run the snapshot agent and click Next.

Select security settings

Specify the domain or machine account under which the snapshot agent process will run.

Select Run under the SQL Server agent service account.Click ok.

After selecting the account for snapshot agent and log reader agent,click Next.

Check create the publication.Click Next.

Give the publication name and finish the wizard.

Close the window after creating publication successfully.

Let us create the subscribers:

Before creating subscribers create one empty database where we could subscribe the publisher database.

Right click Local subscriptions->New subscriptions’

New subscription wizard window appears.Click Next and continue.

Choose the publisher and click next.

Select the distributor agent location.Click Next.

Select subscription database which you created.The database should be empty database.Click Next.

Specify the process account and connection option for each distribution agent.

Specify the domain or machines account under which the distribution agent process will run and click ok.

Schedule the agent and click Next.

Initialize the subscription as immediately and click next.

Check create the subscription and click Next.

Click Finish and complete the wizard.

Close the window after creating subscription.

Launch replication monitor to view the connection.

To open Replication monitor Right click Replication->Launch replication monitor.

Successfully we have created publisher,distributor and subscriber.

How to configure distribution for replication

Replication allows as to automatically distribute copies of data from one server to one or more distributor server.In replication we will have Distributor,Publisher and Subscriber.

First we have to configure a Distributor to set up replication.Distributor is a server that contains the distribution database.It stores all the transactions for transactional replication and meta data for all types of replication . Each Publisher can have only one Distributor instance.But multiple publishers can share one Distributor.We can also Configure a centralized Distributor for multiple Publishers.

There are two types of distributor

1.Local distributor.

2.Remote distributor.

Let us see how to configure distribution:

Right click Replication->Configure distribution

Configuration Distribution wizard window will appear.Click Next and continue.

Select the distributor which will act as server and responsible for storing replication information used during synchronizations. If you want to choose remote distributor select the second option.

Configure SQL Server agent to start automatically.To start the sever agent automatically, when computer is started.Click Next and continue.

Specify the distribution database name.Also specify the valid path for database file and log file.Click Next

Enable the servers which will use the distributor when they become publishers.

In complete wizard window,we will get the options which we selected during the process.Click Finish.

Close the wizard and complete the distribution database creation.

June 24, 2013 by Pradeepa

How to add node to SQL Server cluster

We can add nodes to the existing SQL server failover cluster by following below steps. (Below method can be followed to add node in SQL Server 2008, SQL Server 2008r2 and SQL Server2012)

1. To add new node to existing SQL Server failover cluster, from new node invoke Setup.exe installation media .

2.The Installation Wizard will show the SQL Server Installation Center as shown in below image.Click Installation an then, select Add node to a SQL Server failover cluster.

3.Install the setup support files.

4.After setup validation ,you will get details about operations completed,Click Next and continue.

5.On the Product key page, specify the Product ID key of the product. Click Next.

6.Then read and select the check box to accept terms and agreements.Click Next.

7.Then in cluster Node configuration choose the SQL Server instance name and Current Node which we are adding would automatically appear in “Name of this node” . Click Next and continue.

8.On the Service Accounts page, specify login accounts for SQL Server services.Specify strong password for each account.Click Next after finishing.

9.On the Reporting page by default the option for error reporting is enabled.If you don’t want, disable it and continue.

10.Add Node rules page provides the information about operation completed.

11.Ready to Add Node page displays the options that were specified during Setup. Click Install.

12.Add Node Progress page provides the status of the installation process.

13.After installation, the page provides the summary for the installation.

To complete the SQL Server installation process, click Close.

Move services or application between nodes to verify if SQL Server resources are able to failover successfully:

1.After installation you could move the service between all nodes.

2.Open failover cluster manager->expand SQL Cluster->expand services and applications->select the SQL Server group –> Move this service or application to another node –>Choose the newly added node.

3.After moving the resources to newly added Node check if all the resources are able to come online on newly added node.

June 18, 2013 by Pradeepa

HOW TO CREATE CLUSTER USING HYPER-V

Hyper –V can be used to create virtualized computers to improve the hardware efficiency. In this blog post I will try to cover how to create windows failover cluster using Hyper-V with out using multiple physical computers. Cluster we are building can be used to install Clustered SQL Server and similar cluster aware applications.

1. We will create a Domain controller

2. We will create two member servers which will act as two nodes of the cluster.

We will start the installation by Turning on window feature Hyper-V.

To enable Hyper-V Open control panel—>Programs –>Get Programs->Turn windows features on or off –>Enable Hyper-V as shown in picture below

Once the Hyper-v feature is enabled open the hyper v manager and follow the below steps one by one.

Before creating virtual machine create virtual switch to configure network access between the computers which will create and name it.

Select the type of virtual switch for your virtual machines.

Give the name for your Virtual switch and click ok.

Now create New virtual machine by Right clicking SQLPC –>New->Virtual machine.

2.New virtual machine wizard will appear.Click Next to continue.

Specify the name for your virtual machine.Let us Create domain controller as DC.

Create a new folder or use the existing folder to store the virtual machine.Specify that location in location tab.Click Next

Specify the amount of memory you want to allocate for this machine.Then,click Next.

Inorder to do OS installation ,Connect to virtual hard disk and specify the size for it.Click Next

Then install an Operating system from CD or as image file which you have in your system.

Browse and select the file for installation.Click open.

Now,click finish and complete the virtual machine wizard.

Similarly,Follow the above steps and create two cluster nodes as clusternode1 and clusternode2 .

Now Hyper –V manager will have three virtual machines which we created one as DC and others as clusternodes.

Right click DC->Start->connect.(similarly do for all)

Cluster nodes will start running.

DC virtual machine connection window will appear,click next.

Installation window will appear,click install now to start installation.

Select the operating system you want to install.Select windows server 2008 R2 Enterprise(Full installation) for DC(domain controller).Click Next.

Accept the license term and click next

Select the type of installation as Custom(advanced)

clip_image001[5]

Click Next and continue

After installation restart your virtual machine to update with new setting

Give the password for login.

Domain controller is installed with OS.

Perform the task which you want or else close the window.

Configure TCP/IP address and enable LAN connection.

To configure TCP/IP, open control panel->Network and internet->Network connections->click IPV4

Give IP address ,subnet mask and default gateway ,click ok.

Open dcpromo .

Active directory domain service wizard will appear.Click Next and continue

Select create new domain in a new forest

Give forest root domain which acts as domain.

Set forest functional level .Default Functional level is windows sever 2003.

Check DNS server and click Next.

Click Yes and continue.

Select the folder to store databases,log files and click Next.

Give the administrator password and click Next.

Summary of your selections will appear,review your selection and click next.

DNS installation will be completed.

Click Finish and complete domain services installation wizard.

Restart your computer after installation.

Now do the similar things for cluster nodes 1&2.

Select computer->properties->computer name.Change the computer name and give domain name which you gave in DC.

Configure IPV4 properties for cluster nodes.

Click OK.

Restart your computer to apply the changes.

Click Ok,to restart.

Now we have domain controller and member servers. We have to install failover cluster feature and make this members servers as node of cluster which we are going to create.

To install the failover cluster feature and create a cluster follow the steps in https://sqlserverscribbles.com/2013/06/17/how-to-install-cluster-in-windows-2008-and-windows-2012/

Once the cluster is created we have to create shared Disk for installing failover aware clustered application like SQL Server. Follow the steps in https://sqlserverscribbles.com/2013/06/16/how-to-create-shared-disk-using-iscsi-software-target/ to create a shared disk using iSCSI target software.

June 17, 2013 by Pradeepa

How to install cluster in windows 2008 and windows 2012

Failover cluster feature can be installed enterprise and datacenter edition of windows2008,windows2008R2 and windows2012.

There are changes to the quorum configuration in windows2008,windows2008R2 and windows2012 cluster compared with earlier versions in which it is mandatory to have a shared disk to install the cluster (Exceptions Geo clusters). In this blog I will explain how to install failover cluster feature and create cluster. Shared disk is not mandatory for installing windows cluster but it is required for server applications like SQL Server when you are installing them on cluster.

If you would like to learn how to create shared disk for using in cluster follow the steps in How to create shared disk using iSCSI Software Target

To install failover cluster feature Open Sever Manager (Start—>Run—>Servermanager.msc),Expand Features.Select Add Features.

Then install Failover Clustering.

Confirm the installation and Click install.

Click close and complete the installation.

After installation ,open failover cluster manger.

Right click failover cluster manager->Create a cluster wizard.

Enter Server name of all the nodes which will be part of cluster you are creating.If you do not know the server name you can click on browse and select the servers.

Below window appears when you click browse–>Advanced->Find now. Select the systems which you want to add in the cluster.

In the below example I have select Node1

Configure the validation warning and click Next.

Enter the IP address for cluster and Click Next.

Click Finish and complete the cluster creation.

Once you have created the cluster follow the steps in http://blogs.msdn.com/b/clustering/archive/2010/05/14/10012930.aspx to configure the Quorum setting for your cluster

If you liked this post, do like us on Face book at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Pradeepa

June 16, 2013 by Pradeepa

How to create shared disk using iSCSI Software Target

One of the mandatory requirement to install SQL Server and similar application in cluster is to have shared disk.

I will explain how to create shared disk using iSCSI software target in this blog. You can download iSCSI Software Target from This link.

Installing Microsoft iSCSI software target.

When you invoke the self extracting package from downloaded above link it extracts X64 and x86 version of installer.

Select iSCSI_target for installation on the system which will act as iSCSI target. We will create iSCSI target disks in this machine and these disks will be shared by nodes using iSCSI initiator.
Select iSCSItargetClient_public and install in nodes in which you would like to have shared disk probably all nodes which is going to be part of cluster you are installing.

INSTALLATION IN CLUSTER NODES:(Follow same steps in all the nodes which will be part of cluster which you are installing)

Install iSCSItargetClient _public in all nodes which are going to be part of cluster which you are installing .

Run the file to start the installation.

Click Next and continue

Select the type of installation as Typical.

Specify users account that is member of administrators group in system which is acting iSCSI target and iSCSI initiator (current system).

Configure other settings.

Start the installation by clicking Install button.

Click finish to complete the installation

Install the iSCSI_target on the system which will act as iSCSI target probably file server (or) Domain controller if you are installing failover clustering for testing.

CONFIGURE iSCSI INITIATOR IN CLUSTER NODE’S:

After installation,Open ISCSI initiator.

Select yes and continue.

Type the target server name and click on Quick Connect

(Or)

Click Discover panel and select the target sever.

Give the server IP address.Click ok.

Similarly Follow the above steps and select the target in all nodes of cluster.

Configuring iSCSI target

Now after selecting the target,we have to add the all the iSCSI clients in iSCSI target.

Open MS iSCSI Target in system which is acting as iSCSI target.

Right click iSCSI target->create ISCSI target.

Create target wizard will open.

Specify the target node name,click Next.

Browse and select the iSCSI initiator,click ok.

Selected initiator will appear.Click Next.

Click Finish and complete the target creation.

Target Node1 is added.

Follow the above steps in all nodes.

CREATING VIRTUAL DISK FOR ALL NODES:

To create virtual disk in iSCSI target right click Node1->Create virtual disk.

Click Next.

Specify the file name which will be used as virtual disk.Specify it with .vhd extension.Click Next.

Specify the size for your virtual disk.Click Next.

Click Finish.

For node2,Right click Node2->Add Existing virtual disk to iSCSI target.

Click OK.

CLUSTER NODES:

Now after adding the nodes as target in DC,we have to initiate the server in cluster nodes.

Select iSCSI initiator.Click Refresh,if you didn’t get any discovered target.

Select the target and connect it.Click OK.

Open Disk management in cluster nodes:

Right click Disk1->Online

Again ,Right click Disk1->New Simple Volume.

New Simple Volume Wizard->Format this volume with the following setting.Click Next.

Then Finish the setting.

Open Failover cluster manager.Expand SQL Cluster.mssql.wiki.com.

Storage->Add a disk.

Click OK and add disk to a cluster.

Disk is added. (If disk is created for one node,automatically it will be shared by other node)

Now the shared disk is ready for use and can be used for installation of SQL Server cluster (or) any other cluster aware application which needs shared disk.

June 13, 2013 by Pradeepa

NON CLUSTERED COLUMNSTORE INDEX

What is non clustered column store index in SQL Server?

Column store index is a new feature introduced in SQL Sever 2012.In Non clustered column store index data is stored as column.Column order is not important in column store index.In this non clustered column store index we don’t have traditional row execution mode instead of that,we have batch execution mode.This improves query execution.Batch execution mode executes several rows at a time as a batch.This reduces CPU consumption.

In column store execution, only columns that query needs will be read.This reduces I/O and memory utilization.

We cant have Unique,Primary or Foreign key constraints in column store index and also it does not have feature like INCLUDE.Once we have created the Non clustered column store index for a table,we can’t update the table with new values because the table is READ-ONLY.We could have only one non clustered column store index.Column store index could have only 1024 columns.And we don’t have clustered column store index only Non clustered is available.

This column store index uses the benefits of segment elimination based on some conditions.It gives faster performance for common data warehousing queries.

Create a Column Store Index in SQL Server by using below steps.

Using GUI:

1.In Object Explorer, expand Tables, right click -> Indexes and click New Index. Select non clustered column store index .

2.Add the column store columns.

select the table column to be added to the index.

Selected Column store column will be added.

3.Set the options by selecting options from select page.In that give the value for Max degree of parallelism as greater than or equal to (2) to have batch execution.

Set other extended properties you need by clicking extended properties node.

4.Non clustered column store index is successfully created.

USING T-SQL:

NONCLUSTERED COLUMNSTORE index without options:

Create database product;

Use product;

CREATE TABLE Test

(ID [int] NOT NULL,

RecDate [int] NOT NULL,

DelDate [int] NOT NULL,

ExtDate[int] NOT NULL);

CREATE CLUSTERED INDEX cl_test ON Test (ID);

CREATE NONCLUSTERED COLUMNSTORE INDEX csindex_test ON Test (RecDate, DelDate, ExtDate);

(or)

NONCLUSTERED COLUMNSTORE index with options:

CREATE NONCLUSTERED COLUMNSTORE INDEX csindex_test ON Test

(RecDate, DelDate, ExtDate)

WITH

(DROP_EXISTING = ON, MAXDOP = 2)

ON “default”

June 12, 2013 by Pradeepa

How to create clustered and non-clustered index

Index is a database object, which can be created on one or more columns(max 16 columns).The index will improve the performance of data retrieval and adding.indexes are created in an existing table to locate rows quickly and efficiently.

What is clustered index?

Table can have only one clustered index.In clustered index data’s in table is sorted in particular order based on index keys(either AESC/DESC).In clustered index page chain that holds data pages is also sorted in same order as index keys.So,SQL server follows the page chain in order to retrieve the data rows.By this new rows could be added just by adjusting the links in the page chain without moving entire pages.The leaf nodes of a clustered index contains the data pages with index keys.

What is Non clustered index?

For a table we could have more than one non clustered index because it doesn’t affect data pages organization.The leaf node of a non clustered index does not consist of data pages. Instead, the leaf nodes contain index rows.If we have both the clustered index and non clustered index for a table then index row will have Clustered index key columns that point to the data row. If there is no clustered index then the index row contains Non-Clustered index key columns which is stored along with row locator (or) row identifier.The pointer from an index row to a data row is called a row locator.

Both clustered and non clustered indexes can be unique. That is no two rows can have the same value for the index key. Also we have one special type of index called Non clustered column store index.

Creating a clustered index using Object Explorer:

1.In Object Explorer, expand the table for which you want to create a clustered index.

2.Following window will allow as to select the key columns for index.

3.We could add other options by selecting options node from select page.

4.Click ok.Clustered index will be created.

Create Non-Clustered index using object explorer:

1.Similarly Select Non clustered index from New index.Select the Key columns for an index.

2.In non clustered index we could add included columns for your index.In order to overcome existing index limits.After including needed included columns ,Click ok.

3.Similarly other options could be added by selecting different nodes from select page.

4.Click ok .Index will be created.

June 8, 2013 by Pradeepa

How to create SQL Server Agent Jobs

SQL Server agent can be used to create JOBS which can be scheduled to execute automatically. SQL Server Agent uses SQL Server to store job information. Jobs can have one or more job steps. Each step contains its own task.Each action in a job is a job step.We can schedule the job based on need as daily, weekly, monthly, hourly etc

For example, Let us create a job to back up a database automatically.

Steps to create Job in SQL Server Agent

1. Expand SQL Server Agent in Object Explorer.(If SQL Server Agent is stopped ,right click and start it)

2.Right click Job->New job.

3.Fill the Name field with a name for your job,Give description.

4. On the left side of the New Job window, you’ll see a Steps icon under the Select a page.Next add the individual steps for your job. Click the New button to create a new job step and you will see the New Job Step window.

Use the Step Name textbox to provide a name for the Step. select the database where the job has to be applied.
Use the Command textbox to give the Transact-SQL syntax corresponding to the desired action for this job step. Once you have completed entering the command, click the Parse button to verify the syntax.
Validate the syntax, click OK to create the step.

5.Schedule the job by clicking the Schedule icon in the Select a Page.Then schedule new job.

6.Create alerts for your job by Giving alert name and also select the database.Also select the type of alert and severity of your alert

7.Specify the actions to be performed when the job completes or when job fails.

Note :All the above steps can be performed by TSQL (with out the GUI) using follow stored procedures

sp_add_job to create a job.
sp_add_jobstep to create one or more job steps.
sp_add_schedule to create a schedule.
sp_attach_schedule to attach a schedule to the job.

June 3, 2013 by Pradeepa

How to create backups using database maintenance plan

You can create a database maintenance plan to automate the SQL Server database backups. SQL Server backup maintenance plan can be scheduled to backup the databases automatically or executed manually.

Follow the steps below to create a database backup maintenance plan and schedule it to execute automatically

1.Open SQL Server Management Studio, expand the Management node, and then expand the Management Plans node.

2.Right-click Maintenance Plans, click Maintenance Plan Wizard.

3.SQL Server Maintenance Plan Wizard window will appear.click next.

4.Then type a name for this database backup plan.

Select schedule according to your need.Generally daily,weekly,monthly or hourly.

5.Select the maintenance tasks,which you wanted to plan.

6.select the order of your plan.Click Next.

7.On the Define Back Up Database (Full) Task dialog box, specify information about the full backup. Specify database where this plan has to be applied.Press ok.Then Click Next.

8.On the Define Back Up Database (Transaction Log) Task dialog box, configure the transaction log backup. Click Next.

9. Choose the backup destination

10.On the Define Maintenance Cleanup Task dialog box, configure the cleanup tasks.specify the folder name where you take backups.Then specify the backup folders extension.Click Next.

11.On the Select Report Options dialog box, select whether to write the report to text file or send the report through email. Click Next.

Plan is in progress

12.Maintenance plan wizard is successfully created.Click Finish

June 1, 2013 by Pradeepa

How to set Max degree of parallelism (MAXDOP)

Max degrees of parallelism (a.k.a MAXDOP) in SQL Server is used to control the maximum number of threads per execution plan operators work.MAXDOP does not restrict the number of CPU’s/Threads used by single batch or Query.

Ideally MAXDOP should be equal to number of online schedulers per node. You can use the below query to get the number of online schedulers per node. All the parallel threads for the tasks of the query will be assigned from schedulers of same node so having MAXDOP beyond the number of online schedulers per node may not really improve the performance (With some exceptions).

select count(*) as Maxdopcount, parent_node_id from sys.dm_os_schedulers
 where status='VISIBLE ONLINE' group by parent_node_id

Depending upon the workload in your environment you may increase or decrease the value.

Note: Always ensure you have same number of online schedulers in each node else you may face uneven workload and memory distribution among the SQL Server schedulers more details in SQL Server NUMA load distribution.

To configure Max degree of parallelism follow the below steps.

1.Connect to the Database Engine.

2.From the Standard bar, click New Query.

3.Then execute the following query.
Syntax:
</pre>
sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 1; /*Replace 1 with your preferred MAXDOP value */

GO

RECONFIGURE WITH OVERRIDE;

GO

<span style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px;">
Method2

1.In Object Explorer, right-click a server and select Properties.

2.Click Advanced from select a page.

3.In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.

June 1, 2013 by Pradeepa

SQL Server Database tuning advisor

Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, statistics and partitions for the tables to improve the Query performance .

Database Engine Tuning Advisor uses trace files, trace tables, or plan cache as workload input when tuning databases or else it could use the query which you select for workload analysis.

The Database Engine Tuning Advisor (DTA) helps you to tune databases to improve query processing.

To start a Database Engine Tuning Advisor tuning session, follow these steps:

Before going into this session let us see how indexes (or)stats affects the performance of the query

1.I have created a new database(E.g..DTA).In the new database I have created a table named Test with 100000 rows in it. Let us see how to create necessary indexes and stats on tables to improve query performance based on the queries fired this table .

First let us enable statistics time on and statistics IO on to view the compile time,CPU time ,elapsed time and I/O’s performed by the query

set statistics time on
set statistics IO on

Example:Execute the query select * from Test where a=10,

Before creating index,it takes 1158 logical reads, cpu time=16ms and elapsed time=28ms.The performance is very low.In order to increase the performance we go for indexes.

After creating index, logical reads have become 329,cpu time=15ms and elapsed time= 11ms.

2. Let us see how to create this indexes and statistics using database tuning advisor. Copy the query which has to be tuned in SSMS new query windows.

3. select your query->right click->select Analyze query in database tuning advisor.

4.Then this window will appear .In that select the database to do workload analysis.

5.Then click start analysis in tool bar.Tuning process will be started.

6.After tuning process completes, recommendations will be provided for your table to improve the query performance.

7.Either you could save the recommendation to apply later or you could apply it immediately .

8.We can also use trace files as workload to tune the database. Let us create a trace file by using SQL Server Profiler and see how that can be used to tune the database.

Open SQL Server Profiler

9.SQL Server Profiler window appears.In that give trace name.Then Run the profiler.

10. Now execute the queries which you would like to tune Trace file will capture the events.

For example: Let us execute the same select query which we used in the beginning.

11. Save the trace file.

12. Open the Database tuning advisor. In the Workload area, select the file that you created by using SQL Server Profiler.Select the database for workload analysis and to tune.Then click start analysis.

13.Index recommendations will be provided.By applying this recommendation the estimated improvement will be 56%.

14.Then apply the recommendation.Actions->Apply recommendation.You could apply the recommendations immediately or you could schedule it for later.

15.Here I choose to apply the recommendations immediately.

16. Once the recommendations are applied performance of the query will improve.

May 27, 2013 by Pradeepa

How to configure maximum server memory

Follow the steps below to configure max server memory and minimum server memory for SQL Server.

If you would like to derive the value for max server memory and minimum server memory for sql server follow http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/

Method 1

1.In Object Explorer, right-click a server and select Properties.

2.Click on Memory .

3.Under Server properties –> Memory enter the value that you want for Minimum server memory and Maximum server memory.

Method 2

sp_configure ‘show advanced options’,1
reconfigure with override
sp_configure ‘max server memory (MB)’,2000 — Specify the value here
reconfigure with override

sp_configure ‘min server memory (MB)’,100– Specify the value here
reconfigure with override

SQLServerScribbles.COM

SQL Server, SQL, Replication, SQL Server memory, SQL Server performance, SQL Server architecture, SQL Server cluster, SQL Server 2008, SQL Server 2012

Author: Pradeepa

SQL Server Backup compression

Types of isolation levels in SQL Server

SQL Server database snapshot

How to create table with filestream column and Insert data

How to enable and configure Filestream in SQL SERVER 2008 / 2012

How to import/Export data in SQL Server

Create script for all objects in database with data

Steps to enable Alwayson in SQL Server 2012

How to create database mirroring

How to create log shipping in SQL Server

HOW TO INSTALL SQL Server CLUSTER IN HYPER-V

How to create merge replication in SQL Server

How to start replication agent in command prompt.

How to create transactional replication in SQL Server

How to configure distribution for replication

How to add node to SQL Server cluster

HOW TO CREATE CLUSTER USING HYPER-V

How to install cluster in windows 2008 and windows 2012

How to create shared disk using iSCSI Software Target

NON CLUSTERED COLUMNSTORE INDEX

How to create clustered and non-clustered index

Index is a database object, which can be created on one or more columns(max 16 columns).The index will improve the performance of data retrieval and adding.indexes are created in an existing table to locate rows quickly and efficiently.

What is Non clustered index?

Both clustered and non clustered indexes can be unique. That is no two rows can have the same value for the index key. Also we have one special type of index called Non clustered column store index.

Creating a clustered index using Object Explorer:

1.In Object Explorer, expand the table for which you want to create a clustered index.

How to create SQL Server Agent Jobs

How to create backups using database maintenance plan

How to set Max degree of parallelism (MAXDOP)

SQL Server Database tuning advisor

How to configure maximum server memory

Follow the steps below to configure max server memory and minimum server memory for SQL Server.