Quick list of DBCC (Database Console Commands) in SQL Server

Quick list of DBCC (Database Console Commands) in SQL Server

Database consistency check is a part of one of the important duty of DBA. I used to restore my each and every full database backup in one of the development server and execute DBCC command in that newly restored database. Once DBCC stops execution, I suppose to get an email with the status of this DBCC execution.

I have done automated process for all these steps, I will give more details about how to practically implement this process sometime later but today I wanted to show the list of some DBCC commands which are my favorite to check consistency of database.

Some of the DBCC commands given in following list may affect performance of SQL Server until it gets completed. If your database is large then some of the commands may take few hours for complete execution so be caution before running those commands in live environment, especially in business hours. As I have explained above, I used to restore my database backup somewhere and used to run DBCC command, not directly on production database, which is recommended method.

Anyway, let us have a look at different DBCC commands for different purpose.

Note: don’t run all commands together. Each DBCC command had different purpose and it is recommended to run each one of them separately and see behavior.

USE AdventureWorks2012
GO

--CHECK THE DATABASE ALLOCATION and PAGE STRUCTURE integrity
--If running CHECKDB, no need to run CHECKALLOC separately
DBCC CHECKDB;
GO

-- Check the AdventureWorks2012 database without
--nonclustered indexes and extended logical checks.
DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH EXTENDED_LOGICAL_CHECKS;
GO

--Extended Logical Checks and Physical Only cannot be used together
DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH PHYSICAL_ONLY;
GO

--Checks the consistency of disk space allocation structures for a specified database.
--In our case, it is AdventureWorks2012
DBCC CHECKALLOC;
GO

--Just an estimation and no elaborated messages
DBCC CHECKALLOC WITH ESTIMATEONLY,NO_INFOMSGS

--Following command check only PRIMARY filegroup
DBCC CHECKFILEGROUP;
GO

--check specific filegroup and integrity of the physicalstructure of the page
DBCC CHECKFILEGROUP (1, NOINDEX) WITH PHYSICAL_ONLY;
GO

--Check data integirty for tables with different options for same table.
--execute each of the following three command separately and see the difference
DBCC CHECKTABLE ('sales.SalesOrderDetail') WITH ALL_ERRORMSGS
GO

DBCC CHECKTABLE ('sales.SalesOrderDetail') WITH ESTIMATEONLY
GO

DBCC CHECKTABLE ('sales.SalesOrderDetail', 1) WITH PHYSICAL_ONLY
GO

--Check for constraints violation on specified table or indexed view
DBCC CHECKCONSTRAINTS ('sales.SalesOrderDetail')
GO

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Copy Database wizard in SQL Server 2012

Copy Database wizard in SQL Server 2012

It is observed so many times that people used to use backup of database to restore in another instance or in the same instance with different database name. Few people used to detach database, copy .MDF / .NDF / .LDF file to another instance and attach it back in old and new instance also. I would prefer to use “Copy Database” wizard if I have good network between both instance. If it is a case of instance in same computer, I would definitely use “Copy Database” wizard.

This is not a new facility but this is one of the useful yet under appreciated facility. I am going to copy “AdventureWorks2012” database in the same instance with name “AdventureWorks2012_New”. Please follow the screen capture given below for more details.

Note: Please keep SQL Server Agent running as it will be needed to run “Copy Database” wizard smoothly.

1OpenCopyDatabaseWizard
Once you get “Copy Database” wizard in SQL Server, click on “Next” button from the following dialog box.

2ClickNext

 

Select the source SQL Server Instance in “Source Server” text box and give proper credential “Windows Authentication” or “SQL Server Authentication”
3SourceServer

 

Select the destination (where you want to copy database) SQL Server Instance in “Destination Server” text box and give proper credential “Windows Authentication” or “SQL Server Authentication”
4DestinationServer

 

Now, you have two choices here. Either go for first radio button which will detach database (database will be offline and won’t be used), copy it to destination server and Attach it in new server. If you wish to keep database online and available to use, go for second radio button which will use SMO method. This method will work slow as compare with first option but your database will remain usable during copy process.
5SMOorAttachDetach

 

You have to select database(s) which you want to copy. You can even use “Move” checkbox if you don’t want database in source server and want to move database to another server completely.
6DatabaseToCopyorMove

 

Here you have to give new name of database for destination server. If you are having different SQL Server Instance then source, you can have even same name of database, no need to give new name.
7FileNameForNewdatabase

SQL Server internally create SSIS package and deploy it to SQL Server and call that SSIS package from SQL Server JOB. This Job run either one time or at the scheduled time you specify. You can see package name and location in following dialog box, you don’t need to do anything.
8PackageName

 

You can run that SSIS package immediately to copy database one time or schedule it if you want copy of your database at some other server periodically, you can mention the schedule also.
9JobForDatabaseMove

 

Have a look at final summary of your selection so far and click on “Finish” button.
10ClickOnFinish

 

If you get “Success” status for all steps, you are done.
11ClickOnClose

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Synonyms in SQL Server to give short name to your fully qualified object name

Synonyms in SQL Server to give short name to your fully qualified object name

Synonyms is not a new enhancement in SQL Server 2012, it is long back introduced in SQL Server 2005 but I have seen many SQL Developer doesn’t know this feature so thought to give some light to this concept.

As per MSDN, A synonym is an alternative name for a schema-scoped object. In SMO, synonyms are represented by the Synonym object. The Synonym object is a child of the Database object. This means that synonyms are valid only within the scope of the database in which they are defined. However, the synonym can refer to objects on another database, or on a remote instance of SQL Server.

The object that is given an alternative name is known as the base object. The name property of the Synonym object is the alternative name given to the base object.

Synonym can be created on following:

Assembly (CLR) Stored Procedure

Assembly (CLR) Table-valued Function

Assembly (CLR) Scalar Function

Assembly Aggregate (CLR) Aggregate Functions

Replication-filter-procedure

Extended Stored Procedure

SQL Scalar Function

SQL Table-valued Function

SQL Inline-table-valued Function

SQL Stored Procedure

View

User Defined Table (Including local and global temporary tables)

You can alter data of object via synonyms but you can’t alter schema of object via synonym. For example I have table named “AdventureWorks2012.Sales.SalesPersonQuotaHistory” in Adventureworks2012 database and I give synonyms “SalesPersonQuota” then I can execute any DML commands (INSERT / UPDATE / DELETE) on “AdventureWorks2012.Sales.SalesPersonQuotaHistory” table but I can’t Add/Remove column or change datatype of column in “AdventureWorks2012.Sales.SalesPersonQuotaHistory” table.

Nowadays, we used to create proper schema for each object and detail oriented name to object which makes object name longer so it is good to give short but meaningful name to object to increase productivity (though we have intelisense now).

By providing synonyms name to developer for use in their script, you are also ensuring that they will not be able to change definition of the object via synonyms (though we can handle this with proper login/user permission). This is one more advantage as per me. Each business process and environment has their own rules and regulation to apply. We may have debate on whether this is useful or not as I am sure few people would appreciate the usage of synonyms whereas few will stand against it. Personally I would like to use synonyms for the benefit I just explained.

Let us see how we can create and use synonyms. I will use “Adventureworks2012” database for this demonstration and table would be “AdventureWorks2012.Sales.SalesPersonQuotaHistory”.

USE AdventureWorks2012
GO

CREATE SYNONYM SalesPersonQuota
FOR AdventureWorks2012.Sales.SalesPersonQuotaHistory
GO

Now if we query original table or query synonyms, we will get same results, let us confirm it.

SELECT TOP 10 * FROM AdventureWorks2012.Sales.SalesPersonQuotaHistory
GO

SELECT TOP 10 * FROM SalesPersonQuota
GO

I have received the same output. Have a look:

SynonymsOutput

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Keep watch on heavy software usage directly in production server

Keep watch on heavy software usage directly in production server

We might have SQL Server Management Studio (SSMS), Business Intelligence Development Studio (BIDS) or Full Visual Studio (VS) Installed in live production server but personally I prefer NOT TO USE it directly in production server especially in peak business hours because these are the heavy applications and uses lots of resources.

We might have given permission to few different people to access production server but If we don’t prefer anybody open these heavy applications in server, we have to have some kind of monitoring in server so that if anybody open these applications, we get an alert in form of email so that we can login to that server, find the person who is using it and notify him/her about not to use it.

I have one small .VBS script which I can run from the windows schedule task after every few minutes to check whether any among the restricted applications are running or not. If any of the applications, we have restricted, are running, that .VBS script send an alert message to me.

I have two servers to watch.

1. “Server1Name”

2. “Server2Name”

I am running this .VBS script for another server which is in same network of both above server. I always prefer to run these type of monitoring job, schedule task, packages from non-production server.

You have to replace server names in below given script and then you can set the schedule task for below given .VBS script.

Sub SendMail(Sender, Recipient, Subject, Message)
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = Subject
objMessage.From = Sender
objMessage.To = Recipient
objMessage.htmlbody = Message
objMessage.Send
End Sub

Dim objWMIService, objProcess, colProcess
Dim strMangementStudio
Dim strVisualStudio
Dim Count
Dim arrylist(1)
arrylist(0) = ("Server1Name")
arrylist(1) = ("Server2Name")
Count = 0

For Each strComputer in arrylist
On Error Resume Next

If strComputer = "Server1Name" Then
strMangementStudio = "ssms"
strVisualStudio = "devenv"
Else
strMangementStudio = "ssms"
strVisualStudio = "devenv"
End If

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

txt = "<table cellspacing='1' cellpadding='0' border='0' bordercolor='black' id='shell'>"
txt = txt & ""
txt = txt & "
left: black thin solid; border-bottom: black thin solid'>Server</th>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid'>Process ID</th>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid'>Process Name</th>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid'>Memory Usage(KB)</th>"
txt = txt & ""

Set colProcess = objWMIService.ExecQuery _
("SELECT * FROM Win32_Process WHERE name LIKE '%" & strMangementStudio & "%' or name LIKE '%" & strVisualStudio &
"%'")

For Each objProcess In colProcess

txt = txt & ""
txt = txt & "
left: black thin solid; border-bottom: black thin solid'><font size='2'>" & strComputer & "</Font></td>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid'><font size='2'>" & objProcess.ProcessId & "</Font></td>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid'><font size='2'>"& objProcess.Name &"</Font></td>"
txt = txt & "
left: black thin solid; border-bottom: black thin solid'><font size='2'>"& (objProcess.WorkingSetSize/1024)
&"</Font></td>"
txt = txt & ""

Count = 1
Next

txt = txt & ""
objWMIService = Nothing
colItems = Nothing
Next

If Count = 1 Then
SendMail "Info@SQLHub.com","ritesh@SQLHub.com","Microsoft Applications found opened at " & Now(), txt
End If

Open notepad, save above given code in note pad with file name “Watch Heavy Software.VBS” and call this .VBS file from schedule task.

Do reply me back whether this script is useful or not. I would also like to know if there is any other way then this .VBS script or powershell script.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Setup service broker between two databases in SQL Server.

Setup service broker between two databases in SQL Server.

My earlier article “Service Broker implementation between two tables in one database”  explained how to setup service broker between two table in one database. Today I am explaining how to setup service broker between two tables resides in two different databases in one SQL Server instance.

I will be using the same table, queues, contracts, services and message types given in “Service Broker implementation between two tables in one database” article so if you have not read this article, I would recommend to take a look there once.

Creating two different databases in one SQL Server 2012 instance.

USE MASTER;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBMain')
DROP DATABASE ExtremeAdviceSBMain;
GO

CREATE DATABASE ExtremeAdviceSBMain;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBReceiver')
DROP DATABASE ExtremeAdviceSBReceiver;
GO

CREATE DATABASE ExtremeAdviceSBReceiver;
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBMain  SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBMain  SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBMain TO [sa];
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBReceiver SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBReceiver  SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBReceiver TO [sa];
GO

Let us now create basic architecture of service broker like message type, contract, queue and services in both databases ExtremeAdviceSBMain & ExtremeAdviceSBReceiver (target and initiator). Please note that I have used the same service broker object I have used in earlier article. We may eliminate few things but I will cover it up later on.

USE ExtremeAdviceSBReceiver
GO

--Create Message Type in target database
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract in target database
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO

--Create Target Queue
CREATE QUEUE OrderTargetQueue
WITH STATUS = ON
GO

CREATE SERVICE [EXTREME://EX/Service/Order/Target]  ON QUEUE [OrderTargetQueue] ([EXTREME://EX/Contract/Order])
GO

USE ExtremeAdviceSBMain
GO

--Create Message Type in initiator database
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract in initiator database
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO
--Create initiator Queue

CREATE QUEUE OrderInitiatorQueue
WITH STATUS = ON
GO

CREATE SERVICE [EXTREME://EX/Service/Order/Initiator]  ON QUEUE [OrderInitiatorQueue] ([EXTREME://EX/Contract/Order])
GO

Now we will create two tables. One table in initiator database which is OrderDetail. Sales person will generate sales order and data will falls under that table. If sales person inserting duplicate order, it will caught by trigger and will be inserted in OrderHistory table. For more detail about this concept, have a look at my earlier article.

USE ExtremeAdviceSBMain
GO

--two sample table for demonstration
CREATE TABLE OrderDetail
(
OrderID INT IDENTITY(1,1),
ClientID VARCHAR(10),
ProjectID VARCHAR(50),
OrderDate DATETIME DEFAULT GETDATE()
)
GO

USE ExtremeAdviceSBReceiver
GO

CREATE TABLE OrderHistory
(
ID INT IDENTITY(1,1),
OrderID INT,
DuplicateWith INT,
CheckDate DATETIME DEFAULT GETDATE()
)
GO

Now we will create main logic which will send and process original message along with trigger on OrderDetail table which will activate service broker as soon as new record inserted in OrderDetail table.

USE ExtremeAdviceSBMain
GO
--Create this SP in Initiator database
--Create SP which will be called from app
--or trigger or by any other way
--and activate service broker
CREATE PROCEDURE [SendOrderRequestMessage]
(
@data AS XML
)
AS
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION

DECLARE @DialogHandle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE [EXTREME://EX/Service/Order/Initiator]
TO SERVICE N'EXTREME://EX/Service/Order/Target'
ON CONTRACT [EXTREME://EX/Contract/Order]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Request](@data);
--END CONVERSATION @DialogHandle
COMMIT ;

END TRY

BEGIN CATCH
ROLLBACK TRAN;
DECLARE @msg VARCHAR(MAX);
SELECT @msg = ERROR_MESSAGE();
PRINT @msg
END CATCH;
GO

USE ExtremeAdviceSBReceiver
GO

--Create this SP in target database
-- Create actual business logic SP
CREATE PROCEDURE [CheckOrderMessage]
(
@data XML
)
AS
BEGIN TRY

DECLARE @MessageType VARCHAR(50)
,@OrderID INT
,@ClientID VARCHAR(10)
,@ProjectID VARCHAR(50)

SELECT  @MessageType = @Data.value('(OrderMessage/Header/@MessageType)[1]','VARCHAR(50)'),
@OrderID = @data.value('(OrderMessage/Header/@OrderID)[1]','INT'),
@ClientID = @Data.value('(OrderMessage/Header/@ClientID)[1]','VARCHAR(10)'),
@ProjectID = @data.value('(OrderMessage/Header/@ProjectID)[1]','VARCHAR(50)')

SELECT @MessageType = ISNULL(@MessageType,'')

IF @MessageType = 'OrderHistory' AND @OrderID IS NOT NULL BEGIN
DECLARE @OrderIDOld INT
,@ClientIDOld VARCHAR(10)
,@ProjectIDOld VARCHAR(50)

SELECT @ClientIDOld=ClientID, @ProjectIDOld=ProjectID, @OrderIDOld=OrderID
FROM   ExtremeAdviceSBMain.dbo.OrderDetail
WHERE ClientID=@ClientID AND ProjectID=@ProjectID AND OrderID<>@OrderID

IF(@OrderIDOld IS NOT NULL)
BEGIN
INSERT INTO OrderHistory (OrderID,DuplicateWith)
SELECT @OrderIDOld,@OrderID
END
RETURN
END
END TRY

BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
END CATCH
RETURN
GO

--Create Target queue reader SP
CREATE PROC [OrderTargetQueueReader]
AS
SET NOCOUNT ON;

DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderTargetQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0)
BEGIN
BREAK
END
SET @XML = CAST(@MessageBody AS XML);
IF @MessageTypeName = 'EXTREME://EX/Message/Order/Request'
BEGIN
--PRINT 'executing CheckOrderMessage @XML'

EXECUTE CheckOrderMessage @XML

--PRINT 'sending response'

SELECT @xml = 'OK';

SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Response](@xml);
--PRINT 'ending cnversation'
END CONVERSATION @ConversationHandle;
END
END
END TRY
BEGIN CATCH

DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()

SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
SELECT @msg
END CATCH
GO

USE ExtremeAdviceSBMain
GO

--create following SP to handle response of target in initiatordatabase
--Create Initiator queue reader SP
CREATE PROC [OrderInitiatorQueueReader]
AS
SET NOCOUNT ON;
DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderInitiatorQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0 AND @@TRANCOUNT>0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
END CONVERSATION @ConversationHandle;
END
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
END CATCH
GO

-------service broker call from trigger.
CREATE TRIGGER trg_OrderDetail_Ins
ON  OrderDetail
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Data XML
SELECT @Data =
(
SELECT
'OrderHistory' AS 'Header/@MessageType',
OrderID AS 'Header/@OrderID',
ClientID AS 'Header/@ClientID',
ProjectID AS 'Header/@ProjectID'
FROM
(SELECT * FROM inserted) AS T
FOR XML PATH(''), ROOT('OrderMessage'), TYPE
)

EXEC [SendOrderRequestMessage] @Data
END
GO

Once you are ready with this structure, you are all set to go. Let us insert one record in OrderDetail table.

USE ExtremeAdviceSBMain
GO

--Testing SB by inserting one record in OrderDetail Table
--which will execute Trigger and trigger will activate SB
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record which we have inserted above.
SELECT * FROM OrderDetail
GO

--you will get no record in OrderHistory table
--as we have not inserted duplicate record yet.
SELECT * FROM  ExtremeAdviceSBReceiver.dbo.OrderHistory
GO

Since we have not inserted any duplicate records yet, we won’t get any record in OrderHistory table. Let us inserting duplicate record in OrderDetail table.

--inserting same record again so SB will detect it as duplicate order
--that will be inserted in ORderHistory table
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record in OrderHistory table
--as we have inserted duplicate record
SELECT * FROM ExtremeAdviceSBReceiver.dbo.OrderHistory
GO

We, now, suppose to get record in OrderHistory table but unfortunately we won’t get it. There are two possibilities that either our initiator queue doesn’t have sent message to target or target have received the record but it has not processed it. Right???? Let us check it.

SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue
GO

You can see that our “OrderTargetQueue” has record to process. Then question is why it has not processed?

Well, we have had Trigger on OrderDetail table which has activated our initiator queue and hence record reached to target queue but target queue has not been activated and hence it has not processed target message.

We can process message with following SP either manually or by sql server job as we have not activate it automatically.

 USE ExtremeAdviceSBReceiver
 GO

EXEC [OrderTargetQueueReader]
GO

Now let us see whether record is processed or not.

SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue
GO

You can see “OrderTargetQueue”has not record now as our message is processed and you can also see the entry in “OrderHistory” table also as we had one duplicate record.

Still “OrderInitiatorQueue” has record to process. You can see that it is just a blank message for end dialog with “OK” status which has sent back by target queue after processing messages.

We can run following SP to process end dialog in initiator queue.

USE ExtremeAdviceSBMain
GO

EXEC [OrderInitiatorQueueReader]
GO

I will shortly come up with article which will run full service broker cycle by its own without manual interaction.

If you wish to refer my other Service Broker related article, have a look at following list:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)
  • Service Broker Queue Activation and Thread in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Service Broker Queue Activation and Thread in SQL Server

Service Broker Queue Activation and Thread in SQL Server

I have recently written one article “Service Broker implementation between two tables in one database” where I have created two queues “Target Queue” and “Initiator Queue”.  Following is the code of one of the queue.

 CREATE QUEUE OrderTargetQueue
 WITH STATUS = ON,
 ACTIVATION(
 STATUS=ON,
 PROCEDURE_NAME = OrderTargetQueueReader,
 --You can change queue reader number based on your queue load
 MAX_QUEUE_READERS = 1,
 EXECUTE AS OWNER
 );
 GO
 

After reading and successfully implementing that example, one of the readers had difficulty to understand the script I have given for the queue so thought to give some insight about important part of this script.

Second line of this script is “WITH STATUS = ON” right below the “CREATE QUEUE” line indicates that the queue we are creating is enabled. Use “OFF” status either in “CREATE QUEUE” or in “ALTER QUEUE” to disable the queue. If you disable the queue, queue will stop accepting messages.

If queue is enable and active, it supposes to have some property setup to define its behavior.  Third line of the code “ACTIVATION (” defines those property.

Fourth line “STATUS=ON,” right below “ACTIVATION (” indicates that Queue is active and hence it processes the message it receives.

Fifth line “PROCEDURE_NAME=OrderTargetQueueReader,” defines the stored procedure which reads the message of target queue (in our case) and process it.

Sixth and most important is “MAX_QUEUE_READERS=1,” indicates that only one execution of “OrderTargetQueueReader” stored procedure keeps running, no matter whether there is one message or more than one. Sometime it happens that we have hundreds or thousands of messages to process and we need multiple execution of stored procedure as parallel execution to process message faster and clear the queue.  You have to ALTER the queue and increase the thread of QUEUE based on your requirement, later on you can decrease it back.

We can understand this by one example. Suppose we go to super market to buy monthly grocery stuff. If there are very few customer, mall manager keeps one payment window open. Whenever customer increases, mall manager decides to open multiple payment windows to process payment faster so that customer doesn’t need to wait for long time in queue.

Though we had only one queue in mall so all customer stands in one row only, as soon as multiple windows get opens, customer moves to another queue to finish their payment early and this reshuffle of queue has some overhead. This same concept applies the Service Broker too and Microsoft has handled this issue very efficiently, I will come up with detail knowledge of this concept very soon.

BTW, let me show you how to ALTER the QUEUE to increase the thread.

 ALTER QUEUE [OrderTargetQueue]
 WITH STATUS = ON ,
 ACTIVATION
 (
 STATUS = ON ,
 PROCEDURE_NAME = [dbo].[OrderTargetQueueReader] ,
 MAX_QUEUE_READERS = 5 ,
 EXECUTE AS OWNER  )
 GO
 

Till then stay tuned!!!!!!!!!!

If you wish to refer my other Service Broker related article, have a look at following list:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Generate script to add multiple table in replication as article

Generate script to add multiple table in replication as article

I have already explained about “How to add article in replication from GUI” and “How to add article in replication from TSQL”. I am a script buddy and as long as possible I wouldn’t like to go for GUI options.

If there would be two or four objects only, we could create add article script manually but what about creating a script for many tables or objects?

Generally I used to select only one small table while setting up replication and once replication is ready with publisher, distributor and subscription, I add remaining objects (mainly tables) via script. So after setting up replication, I may need to add hundreds of table and creating add article script for all is little hectic. I have created one small script to overcome this.


SELECT

DISTINCT 'DECLARE @tableName' + Constraints.table_name+' VARCHAR(255)'

+ ' DECLARE @SchemeName' + Constraints.table_name+' VARCHAR(255)'

+ ' SET @tableName' + Constraints.table_name+'='+ '''' + Constraints.table_name + ''''

+ ' SET @SchemeName' + Constraints.table_name+'='+ '''' + Constraints.constraint_schema + ''''

+ ' exec sp_addarticle'

+ ' @publication = N''AdventureWork2012Pub'', '

+ ' @article =@tableName' + Constraints.table_name+','

+ ' @source_owner =@SchemeName' + Constraints.table_name+','

+ ' @source_object =@tableName' + Constraints.table_name+','

+ ' @type = N''logbased'', '

+ ' @description = null, '

+ ' @creation_script = null, '

+ ' @pre_creation_cmd = N''drop'', '

+ ' @schema_option = 0x000000000803509F,'

+ ' @identityrangemanagementoption = N''manual'', '

+ ' @destination_table =@tableName' + Constraints.table_name+','

+ ' @destination_owner =@SchemeName' + Constraints.table_name+','

+ ' @force_invalidate_snapshot=1 '

FROM

information_schema.table_constraints AS Constraints

JOIN

information_schema.key_column_usage AS KeyColumn

ON

Constraints.table_name=KeyColumn.table_name

AND

Constraints.constraint_name=KeyColumn.constraint_name

WHERE

Constraints.constraint_type='Primary Key'

GO

This script find all tables which has primary key and create add article script for them. Transactional Replication needs to have primary key on table to make it part of replication.

My publication name was “AdventureWork2012Pub” so I have used it in the script, you can have your own publication name there.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Insert Tracer Token to see replication latency

Insert Tracer Token to see replication latency

I have written article to find replication latency at “Find latency in transactional replication”. After reading this article, one of the blog reader put comment in article that they have transactional replication setup and if he runs the query given in my blog, he doesn’t get anything.

Well, let me tell you that replication latency is being maintained neither in publisher database nor in subscriber database. Latency is being maintained in “Distribution” database so the query I have provided at “Find latency in transactional replication”, should be run in “Distribution” database only.

Query given in article respond only if “Tracer Token” is inserted for the publication. You can insert “Tracer Token” from “Replication Monitor” but it won’t give you current status. You have to keep inserting tracer token and I used to do it from a SQL Server job which executes at every 5 minutes and insert new tracer token so that I can get latest latency.

Here is the script which inserts tracer token from the script and that needs to call from SQL Server Job.


DECLARE @PublicationName VARCHAR(50)

--give your own publication name here.

--my publication name is "AdventureWorks2012"

SET @PublicationName='AdventureWorks2012'

exec sys.sp_posttracertoken @publication =@PublicationName

GO

Execute this TSQL code in your publication database and enjoy the script given in “Find latency in transactional replication”.

If you are interested to read other replication related articles, have a look at following list:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Service Broker implementation between two tables in one database

Service Broker implementation between two tables in one database

SBDemoI have already provided small demonstration of how service broker works at “Small demonstration of Service broker for beginners” along with list of articles related to service broker from the concept of service broker to its implementation and definition of important part of Service Broker so I will not those concept here in this article, If you wish, you can have a look at those articles, here is the list:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)

Let us now start implementing Service Broker for two tables in one database.

Let me explain the business logic which will be used in this article.

Sales people in Company “Extreme Trading” (ET) goes to client site and accept the order from client. Sales people may use iPad, iPhone or any other smart phone, tablet, laptop or other portable device to insert order of client.

There is a rule in company that any order shouldn’t have same client id and project id. If same client give order for same project, it should be added in original order. Original order should be edited. It shouldn’t insert new order id for same client, project combination.

Well, we can define composite primary key or put some validation or check constraint etc. but I don’t want to debate on this rule.

If any duplicate order comes, it should be accepted from the sales people but later on it should insert original order id along with new order id in one table so back office team can handle it and sales person could be free immediately without even waiting for validation gets completed.

Demonstrating this exercise, we will need one database along with two sample table before we start implementing actual service broker.

--create database.
CREATE DATABASE ExtremeAdviceSBDemo
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBDemo SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBDemo SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBDemo TO [sa];
GO

USE ExtremeAdviceSBDemo
GO

--two sample table for demonstration
CREATE TABLE OrderDetail
(
OrderID INT IDENTITY(1,1),
ClientID VARCHAR(10),
ProjectID VARCHAR(50),
OrderDate DATETIME DEFAULT GETDATE()
)
GO

CREATE TABLE OrderHistory
(
ID INT IDENTITY(1,1),
OrderID INT,
DuplicateWith INT,
CheckDate DATETIME DEFAULT GETDATE()
)
GO

Now, let us start service broker code. I will not explain each block of code here, if you wish, you can refer my earlier articles given in first paragraph here.

--Create Message Type
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO

--Create Initiator queue reader SP
CREATE PROC [OrderInitiatorQueueReader]
AS
SET NOCOUNT ON;
DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderInitiatorQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0 AND @@TRANCOUNT>0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
END CONVERSATION @ConversationHandle;
END
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg

END CATCH
GO

--Create Initiator Queue
CREATE QUEUE OrderInitiatorQueue
WITH STATUS = ON,
ACTIVATION(
PROCEDURE_NAME = OrderInitiatorQueueReader,
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER
);
GO

-- Create actual business logic SP
CREATE PROCEDURE [CheckOrderMessage]
(
@data XML
)
AS
BEGIN TRY

DECLARE @MessageType VARCHAR(50)
,@OrderID INT
,@ClientID VARCHAR(10)
,@ProjectID VARCHAR(50)

SELECT  @MessageType = @Data.value('(OrderMessage/Header/@MessageType)[1]','VARCHAR(50)'),
@OrderID = @data.value('(OrderMessage/Header/@OrderID)[1]','INT'),
@ClientID = @Data.value('(OrderMessage/Header/@ClientID)[1]','VARCHAR(10)'),
@ProjectID = @data.value('(OrderMessage/Header/@ProjectID)[1]','VARCHAR(50)')

SELECT @MessageType = ISNULL(@MessageType,'')

IF @MessageType = 'OrderHistory' AND @OrderID IS NOT NULL BEGIN
DECLARE @OrderIDOld INT
,@ClientIDOld VARCHAR(10)
,@ProjectIDOld VARCHAR(50)

SELECT @ClientIDOld=ClientID, @ProjectIDOld=ProjectID, @OrderIDOld=OrderID
FROM OrderDetail
WHERE ClientID=@ClientID AND ProjectID=@ProjectID AND OrderID<>@OrderID

IF(@OrderIDOld IS NOT NULL)
BEGIN
INSERT INTO OrderHistory (OrderID,DuplicateWith)
SELECT @OrderIDOld,@OrderID
END
RETURN
END
END TRY

BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
END CATCH
RETURN
GO

--Create Target queue reader SP
CREATE PROC [OrderTargetQueueReader]
AS
SET NOCOUNT ON;

DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderTargetQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0)
BEGIN
BREAK
END
SET @XML = CAST(@MessageBody AS XML);
IF @MessageTypeName = 'EXTREME://EX/Message/Order/Request'
BEGIN
--PRINT 'executing CheckOrderMessage @XML'
EXECUTE CheckOrderMessage @XML

--PRINT 'sending response'
SELECT @xml = 'OK';
SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Response](@xml);
--PRINT 'ending cnversation'
END CONVERSATION @ConversationHandle;
END
END
END TRY
BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
SELECT @msg
END CATCH
GO

--Create Target Queue
CREATE QUEUE OrderTargetQueue
WITH STATUS = ON,
ACTIVATION(
PROCEDURE_NAME = OrderTargetQueueReader,
--You can change queue reader number based on your queue load
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER
);
GO

--------------Create SP which will be called from app
--or trigger or by any other way
--and activate service broker
CREATE PROCEDURE [SendOrderRequestMessage]
(
@data AS XML
)
AS
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION

DECLARE @DialogHandle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE [EXTREME://EX/Service/Order/Initiator]
TO SERVICE N'EXTREME://EX/Service/Order/Target'
ON CONTRACT [EXTREME://EX/Contract/Order]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Request](@data);
--END CONVERSATION @DialogHandle
COMMIT ;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
DECLARE @msg VARCHAR(MAX);
SELECT @msg = ERROR_MESSAGE();
PRINT @msg
END CATCH;
GO

--Create Initiator Service
CREATE SERVICE [EXTREME://EX/Service/Order/Initiator]  ON QUEUE [OrderInitiatorQueue] ([EXTREME://EX/Contract/Order])
GO

--Create Target Service
CREATE SERVICE [EXTREME://EX/Service/Order/Target]  ON QUEUE [OrderTargetQueue] ([EXTREME://EX/Contract/Order])
GO

Now, we will define one trigger which will call SB to check whether order is duplicate or not every time we insert new record (not calling trigger for Update, you can do it if it is your business need)

-------service broker call from trigger.
CREATE TRIGGER trg_OrderDetail_Ins
ON  OrderDetail
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Data XML
SELECT @Data =
(
SELECT
'OrderHistory' AS 'Header/@MessageType',
OrderID AS 'Header/@OrderID',
ClientID AS 'Header/@ClientID',
ProjectID AS 'Header/@ProjectID'
FROM
(SELECT * FROM inserted) AS T
FOR XML PATH(''), ROOT('OrderMessage'), TYPE
)

EXEC [SendOrderRequestMessage] @Data
END
GO

Now, we are ready with service broker architecture and ready to test whether SB is working or not.

--Testing SB by inserting one record in OrderDetail Table
--which will execute Trigger and trigger will activate SB
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record which we have inserted above.
SELECT * FROM OrderDetail
GO

--you will get no record in OrderHistory table
--as we have not inserted duplicate record yet.
SELECT * FROM OrderHistory
GO

--inserting same record again so SB will detect it as duplicate order
--that will be inserted in ORderHistory table
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record in OrderHistory table
--as we have inserted duplicate record
SELECT * FROM OrderHistory
GO

I have not handled Error properly in CATCH block of Stored Procedure but just printed error message on screen because error handling is out of scope of this article, If you want to handle Error properly in Stored Procedure, have a look at my earlier article here.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Deal with size of Transmission Queue table sysxmitqueue in MSDB database

Deal with size of Transmission Queue table sysxmitqueue in MSDB database

I have explained earlier that how to purge transmission queue of service broker, Today I come up with one more solution related to transmission queue. I have received one question sometime back on Facebook page of my blog.

Reader has asked me that his MSDB database size is huge (87 GB) and even after shrinking, he wasn’t able to decrease the size.

I have sent him one query (find that query here) to execute in his MSDB database which returns the information about the size of table so he did that and I came to know “sysxmitqueue” table in MSDB database is consuming approx 85GB space.

Sysxmitqueue system table is used for transmission queue. As I have explained in my earlier article at “how to purge transmission queue of service broker”, close the open conversation with that script or execute following command in database.

ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO

Please note that, the solution I am giving here is based on my personal experience, before executing this command in your live production environment, think twice whether this is feasible for your environment or not.

BTW, I have written some other articles related to service broker, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.