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

image

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.

 image

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.

image

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

image 

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.

image

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

image

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.

Advertisements

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.

clip_image001

SQL Server Import and Export Wizard window will open.

clip_image001[4]

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.

clip_image001[22]

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.,

clip_image001[26]

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

clip_image001[24]

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

clip_image001[28]

Select source tables and click Next.

clip_image001[30]

Check Run immediately.Click Next.

clip_image001[32]

Click Finish to complete the wizard.

clip_image001[34]

Close the wizard after completing it successfully.

clip_image001[36]

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.

 

image

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

image 

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

image

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

image

 

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

 

image

 

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

image

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

image

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.

image

image

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

image

9. Choose the backup destination

image

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.

image

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.

image

  • Plan is in progress

image

 

image

12.Maintenance plan wizard is successfully created.Click Finish

image

SQL Server monitoring

Every SQL Server DBA would have faced situations similar to SQL Server not accepting connections for few minutes, SQL Server not responding for few minute or Applications not able to connect with SQL Server for few minutes. Before DBA’s gets alerted about the situation and starts troubleshooting the issue. Everything becomes normal. Challenge in this situations is it becomes very difficult to understand where the underlying problem was, It could be a network connectivity, Application server problem or It might be an issue with SQL Server itself. How do we collect diagnostic data to prove that SQL Server was stable at the time of issue (or) If the issue is with SQL Server then how to collect data we need for diagnosing the issue when there is issue?

SQL Monitor to monitor SQL Server Services

SQL Monitor monitors the SQL Server services and creates log if SQL Server service is down (or) If SQL Server is not accepting (or) SQL Server is not responding to Queries

How it works?

SQL Monitor checks the SQL Server in 3-Phases

1. Check the status of SQL Server service through the windows service control manager

2. If the service is running then check if SQL Server is accepting connections

3. If SQL Server is accepting Connections then probe to perform a simple query and see if SQL Server is responding properly.

4. If SQL Server is not accepting Connections then connect to SQL Server using DAC and take a stack dump.

How to Configure

1. Create a folder call SQLMonitor in C:\

2. Create a Text file called serverlist.txt to fill all the SQLServer information in your account.

Format:

Servername [TAB] Servicename;

Ex:

Server1 MSSQLServer;

Server2 MSSQL$Prod;

3. Invoke command prompt and open attached SQLmonitor.EXE.

Advantage:

1. Multi-threaded . Each server and service is verified using its own thread so retrieving information from one server will not affect the pooling interval to other server.

2. Single exe can be scaled to monitor more than 1000 servers and 1000 services.

3. Uses few MB of memory and system resources.

You can Download SQL Monitor from this link

Trace waits in SQLServer (SQLTRACE_BUFFER_FLUSH,TRACEWRITE,SQLTRACE_WAIT_ENTRIES,SQLTRACE_LOCK)

When you run Profiler trace from client systems or on server with large number of events you will see below wait types.

SQLTRACE_WAIT_ENTRIES
SQLTRACE_LOCK
SQLTRACE_BUFFER_FLUSH
TRACEWRITE

There is no way to completely avoid this wait type without stopping all the traces. We can reduce this waitypes by configuring server side trace instead of client side trace.

select * from sys.traces will give you information about all the traces. ( Status column 0 stopped and 1 active)

For the traces collected using profiler you will find a NULL Path. Profiler traces can cause large number of above waittypes.

Thread which raises the trace event is responsible to Get buffers to write event and write event. So collecting the trace on network share or on slow disk or using profiler can slow down the trace write and make the threads wait, sometimes we may also end with dead locked schedulers.

So ideally you have to avoid running profiler when you see below waits and use sp_trace_create if you like to capture the trace and pass the local path for tracefile parameter.

XP_readerrorlog fails with Failed to open loopback connection.

We might get below error when we open sqlserver errorlog in SSMS or using xp_readerrorlog or sp_readerrorlog

{

Msg 22004, Level 16, State 1, Line 0

Failed to open loopback connection. Please see event log for more information.

Msg 22004, Level 16, State 1, Line 0

error log location not found

}

1. If you get this error in Clustered instance of SQL Server then disable “shared memory protocol” in client configuration.

2. Check if you have wrong alias created and fix them.

3. UAC can also cause this (You might see following message in sqlserver errorlog “Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors”).when you run SSMS run it in elevated mode (right click SSMS–>Run as administrator).

4. OOM condition in sqlsever can also cause this error

5. Executing xp_readerrorlog from DAC connection can also cause this error.

TCP Provider: The semaphore timeout period has expired

TCP Provider: The semaphore timeout period has expired error from SQL Server agent and other applications at times.

1. Disable TCP Chimney.Refer KB:942861

2. If you are in windows 2003 Change the value of the processor affinity to match the number of processors in the system.Follow KB:892100

{
1.Click Start, click Run, type regedit, and then click OK.
2.Expand the following registry subkey:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\NDIS\Parameters
3.Right-click ProcessorAffinityMask, and then click Modify.
4.In the Value data box, type one of the following values, and then click OK:
◦If you have two processors, use the binary value 0b11, or hex value 0x3.
◦If you have three processors, use the binary value 0b111, or hex value 0x7.
◦If you have four processors, use the binary value 0b1111, or hex value 0xF.
5.Quit Registry Editor.
Note The 0x0 or 0xFFFFFFFF values are used to disable the ProcessorAffinityMask entry.
}

3. Check if priority boost is enabled for SQL Server. If yes disable it.

4. Make sure there is no working set trim and system wide memory pressure. You can use second query in significant part of sql server process memory has been paged out to identify and follow the same blog to fix it)

5. Check if paged pool and non-paged is empty. (Event ID:  2019  in event log)

6. If you see this problem in cluster make sure you have set the network priority of “private heart beat” network higher than the “public” network.Refer KB:258750