Restoring a SQLServer database that uses Change Data Capture

Problem
When restoring a database that uses Change Data Capture (CDC), restoring a backup works differently depending on where the database is restored. In this tip we take a look at different scenarios when restoring a database when CDC is enabled.

Solution
For solution, please check my new article on MSSQLTips.com

http://mssqltips.com/tip.asp?tip=2421

Script to Update Statistics by passing database name

You can use below script to update the statistics with the FULL Scan. You can pass the database name in below script, I have given JShah as database name.

[sourcecode language="sql"]
EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’
[/sourcecode]

You can use below script to update the statistics with the SAMPLE Percent agrument. You can pass the database name in below script, I have given JShah as database name.


[sourcecode language="sql"]
EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’
[/sourcecode]

Msg 15009, Level 16, State 1, Procedure sp_help, sp_helptext or object_definition()

Whenever user has in-sufficient permission and he is executing sp_help, sp_helptext or the object_definition function, user will get the below error message. This error occurs because user doesn’t have have permissions to the see the object metadata.

EXEC sp_helptext sp_indexdefrag
Msg 15009, Level 16, State 1, Procedure sp_helptext

To fix the issue we have to grant the VIEW DEFINITION permission to user.
We can turn on the View Definition permission on all databases for all the users having public role using below code.

USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC

We can turn on the View Definition permission on all databases for the user Jugal using below query.

USE master
GO
GRANT VIEW ANY DEFINITION TO Jugal

We can turn on the View Definition permission on SQLDBPOOL for the user Jugal using below query.

USE SQLDBPOOL
GO
GRANT VIEW DEFINITION TO Jugal

Doing a lot of SQL coding - Try SQL Complete

Are you doing a lot of SQL coding every day and looking devouringly at every tool offering replacement of the native SSMS IntelliSense available only for SQL Server 2008? Then don't miss Devart dbForge SQL Complete while you are seeking an assistant to do the boring part of your work that should have been automated and simplified long ago.

General Information
The tool is available in two editions:
Express - a free edition providing basic functionality to complete and format SQL code.
Standard - fully-featured edition providing all necessary functionality for completing and formatting T-SQL code.

30-day trial period available for Standard Edition should be enough for anyone to test all product features and check if it meets one's requirements.

During evaluation and usage, you can submit requests and suggestions to the tool's support team. Besides, you can take part in creating its development roadmap at UserVoice.

Features

• Context-based prompt of different object types


• Context-based prompt of keywords


• Context-based prompt of function parameters


• Word autocompletion


• Automatic filtering of object in the suggestion list


• Context-based sorting of suggestions in the list


• Determining a current database or schema


• Supporting queries to various databases


• Automatic displaying suggestions while typing


• Two ways to insert a suggested word into a query


• Usage of syntax highlight settings for the suggestions list


• Query formatting


• Support of various query types


• Semi-transparent view of the suggestion box


• Inserting columns list on pressing Tab


• Suggesting methods for columns and variables


• Suggesting conditions for JOIN statement


• Automatic alias generation in SELECT statements


• Sorting keywords by relevance


• Quick object info


• Expanding INSERT statements


• Export/Import settings wizard



Review

I chose to install trial of SQL Complete Standard Edition. The installation took several seconds, and the tool and all its options are ready to use and easy to access from the SQL Server Management Studio main menu:



OK, but we will peep into the options later, when we see how the tool actually works and be sure that it's worth spending time on learning different advanced settings. Now we want to type any query we think of first. This can be as simple as SELECT * FROM:



What can we see? The tool filtered available suggestions depending on the probability of their usage. This saved me from tedious selecting of the needed word in a list sorted alphabetically (imagine it was the last one in it!) or typing almost the whole word

Now I'd like to exclude some columns from the table I am working with in this statement. Unfortunately, the “*” symbol is rarely replaced with the columns list by code author. But I found a way to do this quickly and effortlessly using the tool – just pressed Tab, as was written in the hint, and it was all:



Surely, you often have to join tables in queries just as I do. Quite simply, these statements allow combining data in multiple tables to quickly and efficiently process large quantities of data, but often they take too much time to write. There is a feature declaring SQL Complete capability to do such a trick painlessly for the one who is writing code:



I can say these were positive impressions, and the tool is really worth spending more time for testing. I decided on using some more complicated unformatted query, as the vendor puts emphasis on the tool's advanced formatting capabilities. Here's what we've got:

Before:



After:



My query was successfully formatted and is readable now.

Being able to access essential information on a database object is pretty useful and saves some efforts on looking it up:



The tool offers quite a lot of formatting options and a wizard for importing and exporting settings – this should be useful for large companies where some standardized T-SQL code formatting is necessary:

Summary

SQL Complete can be used by professionals, amateurs, and everybody who has something to do with writing SQL code. Besides, the price of the fully-featured edition (less than $50.00), availability of the free edition, effective product support provided by its development team make it worth testing seriously when choosing a tool from a number of alternatives offering similar functionality.

Undocumented Server Property ErrorLogFileName

You can use Server Property errorlogfilename to get the error log file path.

SELECT SERVERPROPERTY('ErrorLogFileName')

DMVs for SQL Server Cluster

sys.dm_os_cluster_nodes
This view returns a row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance has been defined.

sys.dm_io_cluster_shared_drives
This view returns the drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty rowset.

Permission
You must have VIEW SERVER STATE permission for the SQL Server instance.

SELECT *
FROM   sys.dm_os_cluster_nodes
--OR
SELECT *
FROM   Fn_virtualservernodes()

--Shared Drives
SELECT *
FROM   sys.dm_io_cluster_shared_drives 

Can we restore SQL Server 2008 database to SQL Server 2005?

No we can't restore it. SQL Server is not allowing the restore of higher version databases to a lower version. It is not possible to restore a database from a backup of a newer version to older version as database backups are not backward compatible.

You can do below workaround to transfer higher version database to lower version.

1. Generate database script. Right Click database -> Tasks -> Generate Scripts



2. Execute the script on the lower version server and it will create the database and its objects

3. Transfer data between these two databases using DTS/SSIS

Enabling IntelliSense and Refreshing IntelliSense Data in SSMS 2008

Dear Readers,

You can check out IntelliSense Article on MSSQLTips.com.

Click Me to read...

 
Thanks,
Jugal Shah

Central Management Server

Central Management Server
SQL Server Central Management Server is just a central repository that holds a list of managed servers. Microsoft has introduced CMS feature in SQL Server 2008 SSMS.

Use of Central Management Server
1. Multiple Server Query Execution, we can execute query against multiple servers and get the result at source.
2.Centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort.
3.Evaluate policy against the multiple server from single source.
4.Control Services and bring up SQL Server Configuration Manager
5.Import and export the registered servers:

Pre-requisite:
You must have at least 1 SQL Server 2008 instance which can be used as CMS

Steps to Create CMS and Register Server
1. Open the “Registered Servers” from the “View” Menu in the management studio of SQL server 2008.
2. Right click on the Central Management Servers and select “Register Central Management Server” and Register the SQL Server 2008 instance as CMS.



3. Create the groups under Registered CMS servers to define the group for each server.
4. Right Click on groups and register the all the SQL Server instances as per their group. (SQL Server 2000,2005 and 2008)

How to Create Alias in SQL Server?

What is Alias?
A SQL Server alias is the user friendly name. For example if there are many application databases hosted on same SQL Server. You can give the different alias name for each application. Simply Alias is an entry in a hosts file, a sort of hard coded DNS lookup a SQL Server instance.

You can create Alias from Configuration Manager.

Go to SQL Server Configuration manager - Go to SQL Native Client -> Right Click and SELECT New Alias from the popup window.

Alias Name -- Alernative name of SQL Server
Port No -- Specify the Port No
Server - Mentioned the Server Name or IP address

Make sure On a 64-bit system, if you have both 32-bit and 64-bit clients, you will need to create an alias for BOTH 32-bit and 64-bit clients.

Backup Start Date Time and Finish Date Time

As best practice it is recommended that you have to backup date time with the backup file name so anyone can get the idea of Backup creation.

Sometimes due some issue we took backup without specifying the datetime with the backup file name so during restore we are unsure that how much data backed up in the backup file, type of backup, Is it Copy only and more.

SQL Server stores the Backup Metadata into backup header. You can restore header only command to get the required information.

RESTORE headeronly FROM disk = 'c:\jshah.bak' 

Column Name	Values	Description
BackupName	NULL
BackupDescription	NULL
BackupType	1	Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = Partial 8 = Differential partial
ExpirationDate	NULL
Compressed	0	0 = Un-Compressed Backup 1 = Compressed Backup
Position	1
DeviceType	2
UserName	JShah
ServerName	SQLDBPool
DatabaseName	jshah
DatabaseVersion	655
DatabaseCreationDate	12/31/10 9:55 AM
BackupSize	1453056
FirstLSN	28000000006000100
LastLSN	28000000013000000
CheckpointLSN	28000000006000100
DatabaseBackupLSN	0
BackupStartDate	12/31/10 10:06 AM
BackupFinishDate	12/31/10 10:06 AM
SortOrder	52
CodePage	0
UnicodeLocaleId	1033
UnicodeComparisonStyle	196609
CompatibilityLevel	100
SoftwareVendorId	4608
SoftwareVersionMajor	10
SoftwareVersionMinor	0
SoftwareVersionBuild	2757
MachineName	SQLDBPool
Flags	512	1 = Log backup contains bulk-logged operations. 2 = Snapshot backup. 4 = Database was read-only when backed up. 8 = Database was in single-user mode when backed up. 16 = Backup contains backup checksums. 32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors. 64 = Tail log backup. 128 = Tail log backup with incomplete metadata. 256 = Tail log backup with NORECOVERY.
BindingID	85A5505D-ADB1-4B33-A181-549DC520A0F8
RecoveryForkID	03DE5437-1E27-4885-9011-91CFED12338A
Collation	SQL_Latin1_General_CP1_CI_AS
FamilyGUID	03DE5437-1E27-4885-9011-91CFED12338A
HasBulkLoggedData	0	1 = Yes 0 = No
IsSnapshot	0	1 = Yes 0 = No
IsReadOnly	0	1 = Yes 0 = No
IsSingleUser	0	1 = Yes 0 = No
HasBackupChecksums	0	1 = Yes 0 = No
IsDamaged	0	1 = Yes 0 = No
BeginsLogChain	0	1 = Yes 0 = No
HasIncompleteMetaData	0	1 = Yes 0 = No
IsForceOffline	0	1 = Yes 0 = No
IsCopyOnly	0	1 = Yes 0 = No
FirstRecoveryForkID	03DE5437-1E27-4885-9011-91CFED12338A
ForkPointLSN	NULL
RecoveryModel	FULL
DifferentialBaseLSN	NULL
DifferentialBaseGUID	NULL
BackupTypeDescription	Database
BackupSetGUID	62EB4399-C119-42C2-91F1-BF0FF19CB896
CompressedBackupSize	1453056

How to check database ISOLATION Level?

You can use below query to check the database isolation level.

DBCC UserOptions

How to move Cluster Resource/Services to proper node using command prompt?

cluster group "SQL Server Group" /move: Physical_Name_Of_Node1

cluster group "SQL Server Group INST2" /move: Physical_Name_Of_Node2

Happy Birthday to Me (It's 1-Sept)

Server: Msg 7391, Level 16, State 1, Line 2

Problem Symtomps
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.

Cause
The problem occurs because Microsoft Distributed Transaction Coordinator (MS DTC) is not configured for network access. By default, the network access settings of MS DTC are disabled on new installations of SQL Server 2000 on computers that are running Windows Server 2003.
Resolution

Step 1: Open Component Services Wizard using below steps
Go to -> Start -> All Programs -> Administrative Tools -> Component Services

Step 2: Expand the Componenet Service and Double Click on Computers

Step 3: Right Click on MyCompute and select properties

Step 4: Click on MSDTC Tab and then click on Security Configuration

Step 5: In the Security Configuration dialog box, check the Network DTC Access check box

Step 6: Under  Network DTC Access, click on New transaction

 Please find the below images for the same.



Figure 2



Figure 3

Insert Extended Character using OSQL Utility

Problem: OSQL utility uses ODBC to communicate with the server. User’s problem is that the ODBC driver he is using to connect to the database is performing translations on the character data in the T-SQL script. Extended characters, which are not in the standard ASCII character set, are translated by the driver based on drive settings. The character translation option is ON by default when SQL Server executes scripts through the OSQL utility. 

Below query is inserting garbage data in the table. 

CREATE TABLE #temp(col1 varchar(40) NOT NULL )

INSERT INTO #temp VALUES( 'Tëst' )

SELECT col1 FROM #temp

DROP TABLE #temp 

Solution: By using Unicode script files and converting the column to Unicode, user can avoid the character translation. For that user needs to add N against the column, which is already added. 

Save As below script file as UNICODE file 

CREATE TABLE #temp(col1 varchar(40) NOT NULL )

INSERT INTO #temp VALUES( 'Tëst' )

SELECT col1 FROM #temp

DROP TABLE #temp 

User needs to do the following with the ODBC DSN to execute the scripts successfully, without any translation:

1.    Create an ODBC system data source called MyDSN on the machine where he is executing OSQL with the "Perform translation for character data" option cleared

2.    Specify this data source name as a parameter to OSQL so that OSQL can read the DSN settings and use them upon connection to SQL Server. 

osql -S. -itest.sql –DMyDSN

OR

User needs to develop a script which can pass the ASCII value 

Select ASCII(‘ë’)

INSERT INTO #temp (Col1) Select ‘T’ + chr(233) +  'st'

How to improve communication skills & Support Service

As DBA comes under the support industry, it is extremely important for DBAs to improve communication skills. As we are working production servers and backend, it is extremely important for us to communicate properly. 

• Please take care of the below points to improve your written communication skill. 


•  When you reply all, make sure to move names from TO to CC and CC to TO list according to whom you are writing emails

• Be concise and to the point. Use simple English.


• Avoid the use of slang words/abbreviations


• Use proper spelling, grammar & punctuation


• Do not write in CAPITALS. CAPITALS indicate that we are shouting.


• Numbers should be expressed as words when the number is less than 10 or is used to start a sentence (example: Ten years ago, my brother and I…). The number 10, or anything greater than 10, should be expressed as a figure (example: My brother has 13 Matchbox cars.)


• You should just mention the name instead of Hi Jugal.As we are working as support team, use `We` instead of `I`. For example; Jugal instead of Hi Jugal


• You should change the text in the Subject line during reply emails if it is not relevant.


• If you are asking for something in the email, you should ask them first and then mention why you need that.

• Don’t write open ended email to user


• If you are not sure for any support request, please ask end user for more inputs on particular request.


• Inform your team regarding any conversation with client through chat or voice call


• Update your work log every day


• If you are not sure for any resolution steps, discuss it with your team and then provide it to client


• When sending large attachments, always "zip" or compress them before sending.


• Always include subject in your mail body.


• Don’t send email to any account using your client account


• Discuss as much as possible with your client


• Grab as much as possible knowledge of the client process/environment

Save Water, Save Life and Save the Workl

Troubleshoot Suspect Database Issue

Problem

How to troubleshoot suspect database problem? How to bring it back online? What are the do you need to perform once the database is online?



Solution

Step 1: Bring the database online using below script

USE Master

GO

 

-- Determine the original database status

SELECT [Name], DBID, Status

FROM master.dbo.sysdatabases

GO

 

-- Enable system changes

sp_configure 'allow updates',1

GO

RECONFIGURE WITH OVERRIDE

GO

 

-- Update the database status

UPDATE master.dbo.sysdatabases

SET Status = 24

WHERE [Name] = 'SuspectedDatabaseName'

GO

 

-- Disable system changes

sp_configure 'allow updates',0

GO

RECONFIGURE WITH OVERRIDE

GO

 

-- Determine the final database status

SELECT [Name], DBID, Status

FROM master.dbo.sysdatabases

GO

Step 2: Check for database corruption. This is very important step please execute it.

• DBCC CHECKDB - Validate the overall database integrity


• DBCC CHECKCATALOG - Validate the system catalog integrity


• DBCC CHECKTABLE - Validate the integrity for a single table

Step 3: To resolve the corruption issue, please execute below commands

• Drop and Recreate Index(es)


• Move the recoverable data from an existing table to a new table


• Update statistics


• DBCC UPDATEUSAGE


• sp_recompile

Step 4: Repeat Step 2 to validate all the corruption occurred

DRBD, Heartbeat and MySQL

The easiest solution to implement clustering in MySQL is DRBD and Heartbeat.

DRBD: The Distributed Replicated Block Device (DRBD) is a software-based, shared-nothing, replicated storage solution mirroring the content of block devices (hard disks, partitions, logical volumes etc.) between servers.

DRBD mirrors data

• In real time. Replication occurs continuously, while applications modify the data on the device.


• Transparently. The applications that store their data on the mirrored device are oblivious of the fact that the data is in fact stored on several computers.


• Synchronously or asynchronously. With synchronous mirroring, a writing application is notified of write completion only after the write has been carried out on both computer systems. Asynchronous mirroring means the writing application is notified of write completion when the write has completed locally, but before the write has propagated to the peer system

You can download DRDB from below site

http://www.drbd.org/download/packages/