Tag Archives: sql server 2012

Find highest latency in transactional replication in SQL Server 2012

Find highest latency in transactional replication in SQL Server 2012

Replication is one of the very handy and useful high availability features SQL Server has provided. It is very easy to setup but maintaining health and keeping eye on performance of replication is very crucial and mandatory.

We can find “MSTracer_History”, “MSDistribution_Agents” and “MSTracer_Tokens” tables into distribution database of replication and hence the query I am going to give you, would be used under distribution database only.

Let me give you brief introduction, from MSDN, of all three system tables of distribution database.

MSTracer_History: The MStracer_history table maintains a record of all tracer tokens that have been received at the Subscriber. This table is stored in the distribution database and is used by replication for performance monitoring.

MSDistribution_Agents: The MSdistribution_agents table has one row for each Distribution Agent running at the local Distributor. This table is stored in the distribution database.

MSTracer_Tokens:  The MStracer_tokens table maintains a record of tracer token records inserted into a publication. This table is stored in the distribution database and is used by replication for performance monitoring.

Now here is the query I use often to check the latency of replication.

 SELECT
                 SysServ.name AS SubscriberName,
                 MSDA.subscriber_db AS SubscriberDatabase,
                 MSDA.publication AS PublicationName,
                 MAX(DATEDIFF(SS,publisher_commit,distributor_commit)) AS LatencyPublisherToDistributor,
                 MAX(DATEDIFF(SS, distributor_commit,subscriber_commit)) AS LatencyDistributorToSubscriber,
                 MAX(DATEDIFF(SS,publisher_commit,distributor_commit)
                 + DATEDIFF(SS, distributor_commit,subscriber_commit)) AS TotalLatency
 FROM
                 MStracer_history MSTH
 INNER JOIN
                 MSdistribution_agents MSDA
 ON
                 MSTH.agent_id = MSDA.id
 INNER JOIN
                 sys.servers SysServ
 ON
                 MSDA.subscriber_id = SysServ.server_id
 INNER JOIN
                 MStracer_tokens
 ON
                 MSTH.parent_Tracer_id=MStracer_tokens.tracer_id
 WHERE
                 subscriber_commit >DATEADD(hh,-1,GETDATE())
 GROUP BY
                 SysServ.name,
                 MSDA.subscriber_db,
                 MSDA.publication
 GO

You can modify above given query for your customize need. I have filtered (in WHERE clause) records for last one hour so it will give you highest latency of last one hour. You can even filter records on TotalLatency field, If you want to see resultset only if it cross certain seconds/minutes while transferring records from publisher to subscriber.

Do let me know what technique do you use to keep your eye on replication health?

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.

Generate script to create and drop Primary Key and Foreign Key in SQL Server

Generate script to create and drop Primary Key and Foreign Key in SQL Server

Primary Key and Foreign key is two main pillars to enforce referential integrity in relational database management system.

I had requirement to drop few tables which has Primary Key and that Primary Key has been used as a reference (Foreign Key) in few other table. After deleting table, I had to create that table again in our beta database, create same Primary Key and Foreign Key. Manually generating script to create/drop foreign key script for few tables is little tedious task and there is a chance that we miss some of the referenced table so I finally decided to quickly write down a script which can give me create and drop statement of primary key as well as all related foreign key once I pass the table name of primary key in filter area (WHERE clause).

I have used following system views/tables to cater the need of this article:

  • INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  • Sys.Foreign_Keys

Here is the script I have generated with the help of above given system views and ran it in “AdventureWorks2012” database for “Department” table. You can change database name as well as table name as per your requirement.

 USE AdventureWorks2012
 GO
SELECT
*
,'ALTER TABLE ['+ FKTableSchema +'].[' + FKTableName + '] DROP CONSTRAINT ' + ForeignKeyName AS FKDrop
,'ALTER TABLE ['+ FKTableSchema +'].[' +FKTableName + '] ADD CONSTRAINT ' + ForeignKeyName + ' FOREIGN KEY(' +
 FKColumnList +') REFERENCES ['+ PKTableSchema+'].['+ PKTableName + '] ('+PKColumnList +')' AS FKCreate
,'ALTER TABLE ['+ PKTableSchema +'].[' +PKTableName + '] DROP CONSTRAINT ' + PrimaryKeyName AS PKDrop
,'ALTER TABLE ['+PKTableSchema +'].[' +PKTableName + '] ADD CONSTRAINT ' + PrimaryKeyName + ' PRIMARY KEY('+PKColumnList+')' AS PKCreate
FROM
(
SELECT
 DISTINCT
 FKTableSchema=(SELECT DISTINCT table_schema FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE constraint_name=rc.constraint_Name),
 FKTableName=(SELECT DISTINCT table_Name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE constraint_name=rc.constraint_Name),
 rc.constraint_name AS ForeignKeyName,
 FKColumnList=(SELECT left(t.column_name,len(t.column_name)-1) AS 'ColumnList' FROM
 (
 SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE constraint_name=rc.constraint_name
 FOR XML PATH('')
 ) AS t(column_Name)),
 cu.table_schema AS PKTableSchema,
 cu.table_name AS PKTableName,
 cu.constraint_Name AS PrimaryKeyName,
 PKColumnList=(SELECT left(t.column_name,len(t.column_name)-1) AS 'ColumnList' FROM
 (
 SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE constraint_name=cu.constraint_name
 for xml path('')
 ) AS t(column_Name))
FROM
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
INNER JOIN
 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
ON
 rc.Unique_Constraint_name= cu.Constraint_name
WHERE
 table_name='department' --and table_schema='sales'
) AS tab

 GO

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.

Delete Article from Transactional Replication in SQL Server 2012

Delete Article from Transactional Replication in SQL Server 2012

I have already demonstrated how to established snapshot replication as well as transactional replication in SQL Server 2012. Apart from that we have already seen how to remove replication and how to add new article (database objects like table, views, functions, stored procedures etc.) in transactional replication along with few other tips and tricks of replication. Now it is time to remove some of the unwanted articles from transactional replication. I will use the same transactional replication which I have created in my earlier article.

Removing article from replication and removing objects from replicated (subscription) database is two different things. Replication will stop replicating the changes in schema/data from publisher to subscriber once you remove article from replication but it will not remove schema/data from the subscriber database, you have to do it manually after removing object from replication.

Let us now proceed towards removing article from transactional replication.

1.)  open the replication option under your SSMS and move to the publication, right click on publication and click on “Properties” from the popup menu.

2.) Publication Properties dialog box, go to “Articles” option from the menu in left hand side and deselect the checkbox of article you want to remove from replication. I have remove “Sales.Customer” table and click on “Ok” button.

3.) Once you will click on OK button from Publication Properties dialog box above, replication will stop populating new data change to the “Sales.Customer” table but now I want to update my snapshot of replication so right click on “Replication” option and click on “Launch Replication Monitor” option from the popup menu.

4.) From the replication monitor, you can find “Agent” tab under your publication, you have to right click on “Snapshot Agent” and click on “Start Agent” from popup menu. This action will update snapshot which takes time based on the size of database.

All these four steps will successfully remove articles from replication now if you wish; you can manually go to subscriber database and drop objects which you have removed from replication.

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.

Add new article in transactional replication in SQL Server 2012

Add new article in transactional replication in SQL Server 2012

Creating replication is one thing and maintaining it is a different yet equally important thing. Over the time, you might add or remove some objects in your primary database and hope the same for your replication database and this article comes into the picture when you need to add some more object from primary (Publication) database to replicated (subscriber) database.

I have already written few articles on replications which are listed below:

  • 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)

I will use the Publisher and Subscriber of Transaction Replication to demonstrate this article. You can create the same replication from here.

If you already having your replication ready to add article (Table, Stored Procedure, View, Index View, Functions etc.), follow the steps given below.

 

1.)  Connect to your publisher server and right click on publication name and click on “Property” from pop up menu.

 

2.) You will get Publication property dialog box and you have to move to “Article” page from the left hand menu to get a list of articles available to publish in publication database. Select the object you want to replication. Make sure the database table you select is having the primary key. Without primary key, table won’t be able to be a part of transaction replication.

 

 

3.) Once you select object and click on “Ok” button in above given dialog box,  right click on “Replication” option in objection explorer of SSMS and click on “Launch Replication Monitor”.

 

 

4.) You will see “Agent” tab in Replication Monitor dialog box, right click on “Snapshot Agent” and click on “Start Agent” option; it takes little time, based on the size of object and data, to complete the snapshot agent.

As soon as Snapshot Agent builds completely, you can check your subscriber database, you will find the entire selected object in subscriber database.

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.

Setup Transaction Replication in SQL Server 2012

Setup Transaction Replication in SQL Server 2012

Before you move ahead for setting up Transaction Replication, You have to understand few definitions which are heavily used in replication, for example Publisher, Distributor and subscriber etc. You can get an idea of all these terminology from my earlier article “Understanding replication with Implementation of Snapshot Replication in SQL Server 2012”.

Transaction Replication: Transaction Replication is used when DML or DDL schema changes performed on an object of one database on one SQL Server Instance/ server needs to be replicated immediately on the database residing on another SQL Server Instance/server.

Change in Schema as well as in data will be reflected in replicated database immediately but this is depends on the network/internet setup you have between both SQL Server Instance/Server.

You can select any number of articles (object) as a candidate of replication no matter whether it is function, stored procedure or table. Just keep one thing in mind that any table you choose as a candidate of transaction replication, supposed to have Primary Key. Table without primary key will not be a part of transaction replication.

You can select two different SQL Server installation located in two different Server may or may not be in same premises but in same network. I have had two different instance of SQL Server 2012 in one server while writing this article.

Instance 1: “WIN-9H6QATRKY81\SQL2K12DEV”, I will call this instance as ServerA in this article.

Instance 2: “WIN-9H6QATRKY81\SQL2K12DEVTest”, I will call this instance as ServerB in this article.

If you are ready with AdventureWorks2012 database or any other database of your choice in one server and have another server available for subscriber, let us start creating replication.

1.) Create Distributor by login to ServerB (Subscriber) as I want to create Distributor in Subscriber server. If budget permits, it is good to have third dedicated server for distributor.

2.) Select or add the server where you wanted to create distributor database, since we are already logged into to ServerB, we can directly select first radio button from following screen capture.

3.) If your SQL Server Agent not set to start automatically at startup, it will ask you to start it as SQL Server Agent is responsible to run the job which will transmit data.

4.) Give shared network path where replication can create snapshot and use that location as a primary source for data. Note that both or all three server should have proper access to this shared folder as we are going to save all script file along with distributor database in this path.

5.) Give path where SQL Server can create “Distributor” database which is responsible to receive data from publisher and send it to subscriber.

6.) Select Publisher server for distribution database. By default it has selected ServerB but we want ServerA to be the publisher so add ServerA instance here.

7.) login to publisher database and it will be selected.

8.) Now deselect ServerB and Select ServerA where we have just logged in from above screen.

9.) I have provided password for my SA login password so that distributor can use this password while connecting to publisher. You can use any other login with sufficient permission.

10.) Select “Configure Distribution” checkbox along with “Generate Script” check box

11.) Give the path for saving a script file. I have used the same shared path I have used before.

Here is the script I have received after clicking on the “Finish” button from above screen.


/****** Scripting replication configuration. Script Date: 11/25/2012 3:49:05 PM ******/

/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 11/25/2012 3:49:05 PM ******/

use master

exec sp_adddistributor @distributor = N'WIN-9H6QATRKY81\SQL2K12DEVTEST', @password = N''

GO

exec sp_adddistributiondb @database = N'distribution', @data_folder = N'E:\ReplicationShare', @log_folder = N'E:\ReplicationShare', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1

GO

use [distribution]

if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))

create table UIProperties(id int)

if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))

EXEC sp_updateextendedproperty N'SnapshotFolder', N'E:\ReplicationShare', 'user', dbo, 'table', 'UIProperties'

else

EXEC sp_addextendedproperty N'SnapshotFolder', N'E:\ReplicationShare', 'user', dbo, 'table', 'UIProperties'

GO

exec sp_adddistpublisher @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'E:\ReplicationShare', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'

GO

12.)  Now manually confirm that you have received “Distributor” database under “System Databases” in the ServerB or not.

13.) Go to ServerA, this is the time to create publication now.

14.) Select ServerA as a pubcation.

15.) Give the same shared path to the replication to save and keep snapshot of publication.

16.) Select the database you wanted to replicate. I have selected “AdventureWorks2012” from ServerA.

17.) Select “Transactional Replication” from following screen.

18.) Select Table, SP, Functions etc. which you want to replicate. Confirm the table you select for replication, supposed to have primary key. Select other supported objects of table too. For example you have one computed column populated from UDF in your table so you have to select that function along with table.

19.) Click on next from following screen as I don’t want to specify any criteria for data. I want to transfer all data from my table.

20.) Select checkbox for “Create a snapshot immediately”.

21.)Click on “Security Settings” from following screen shot.

22.) provide sufficient credential here so that replication works smooth without any security issues.

23.) Once you provide security credential and click on “Ok” button, you will get screen something like this:

24.) select both check box from following screen.

25.) give proper path to save script for your publication.

Here is the script generated for publication:


/****** Scripting replication configuration. Script Date: 11/25/2012 4:01:52 PM ******/

/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 11/25/2012 4:01:52 PM ******/

use master

exec sp_adddistributor @distributor = N'WIN-9H6QATRKY81\SQL2K12DEV', @password = N''

GO

exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1

GO

use [distribution]

if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))

create table UIProperties(id int)

if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))

EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\win-9H6QATRKY81\ReplicationShare', 'user', dbo, 'table', 'UIProperties'

else

EXEC sp_addextendedproperty N'SnapshotFolder', N'\\win-9H6QATRKY81\ReplicationShare', 'user', dbo, 'table', 'UIProperties'

GO

exec sp_adddistpublisher @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'\\win-9H6QATRKY81\ReplicationShare', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'

GO

use [AdventureWorks2012]

exec sp_replicationdboption @dbname = N'AdventureWorks2012', @optname = N'publish', @value = N'true'

GO

use [AdventureWorks2012]

exec [AdventureWorks2012].sys.sp_addlogreader_agent @job_login = N'win-9H6QATRKY81\Administrator', @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N' ', @job_name = null

GO

-- Adding the transactional publication

use [AdventureWorks2012]

exec sp_addpublication @publication = N'AdventureWorks2012', @description = N'Transactional publication of database ''AdventureWorks2012'' from Publisher ''WIN-9H6QATRKY81\SQL2K12DEV''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

GO

exec sp_addpublication_snapshot @publication = N'AdventureWorks2012', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'win-9H6QATRKY81\Administrator', @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N''

use [AdventureWorks2012]

exec sp_addarticle @publication = N'AdventureWorks2012', @article = N'Customer', @source_owner = N'Sales', @source_object = N'Customer', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Customer', @destination_owner = N'Sales', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_SalesCustomer', @del_cmd = N'CALL sp_MSdel_SalesCustomer', @upd_cmd = N'SCALL sp_MSupd_SalesCustomer'

GO

26.) Provide the name for your publisher. I have provided “AdventureWorks2012”

27.)  now connect to ServerB and add “New Subscription”

28.) Subscriber needs publisher to get data so select out publisher we have created.

29.) our publication name was “AdventureWorks2012” which I have selected from following screen:

30.) select “Pull Subscription” as I want to run all replication related job in ServerB and pull the data from ServerA. In Push method, replication related job runs from my ServerA which is primary server for my application and I doesn’t want to add overhead of replication job.

31.) If you already have Subscription database then select it otherwise create a new one. I have created new database named “AdventureWorks2012Subscriber”

32.) my newly created subscription database “AdventureWorks2012Subscriber” is selected now.

33.) SQL Server Agent would need proper credential to connect with Distrubtor database and subscription database. Give proper credential to keep your replication smooth.

34.) select “Run Continuously” for agent.

35.) select “Immediately” so that subscription gets populated immediately.

36.)  select both the checkbox as usual.

37.)  Give the path for script of subscription.

Here is the script generated for subscription.


-----------------BEGIN: Script to be run at Publisher 'WIN-9H6QATRKY81\SQL2K12DEV'-----------------

use [AdventureWorks2012]

exec sp_addsubscription @publication = N'AdventureWorks2012', @subscriber = N'WIN-9H6QATRKY81\SQL2K12DEVTEST', @destination_db = N'AdventureWork2012Subscriber', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'

GO

-----------------END: Script to be run at Publisher 'WIN-9H6QATRKY81\SQL2K12DEV'-----------------

 

-----------------BEGIN: Script to be run at Subscriber 'WIN-9H6QATRKY81\SQL2K12DEVTEST'-----------------

use [AdventureWork2012Subscriber]

exec sp_addpullsubscription @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @publication = N'AdventureWorks2012', @publisher_db = N'AdventureWorks2012', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1

 

exec sp_addpullsubscription_agent @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @publisher_db = N'AdventureWorks2012', @publication = N'AdventureWorks2012', @distributor = N'WIN-9H6QATRKY81\SQL2K12DEV', @distributor_security_mode = 0, @distributor_login = N'sa', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20121125, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'win-9H6QATRKY81\Administrator', @job_password = null, @publication_type = 0

GO

-----------------END: Script to be run at Subscriber 'WIN-9H6QATRKY81\SQL2K12DEVTEST'-----------------

So finally we have implemented Transaction Replication. You can confirm whether replication is working or not by inserting few records in one of the table in ServerA which supposes to take effect immediately in ServerB’s database.

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.

New Logical Function IIF in SQL Server 2012

New Logical Function IIF in SQL Server 2012

IIF is one of the new logical functions in SQL Server 2012 which Returns one of two values, depending on whether the Boolean expression evaluates to true or false.

Generally we had to use IF condition or CASE….WHEN….THEN statement to achieve the functionality being offered by IIF. IIF is shorter function to use as against IF condition and CASE….WHEN….THEN statement. If you write IIF function compiler actually rewrites the IIF function as a CASE statement so the behavior of IIF and the simple CASE statement are identical though you will save some key stroke as compare with CASE…WHEN.

Let us see one small example which demonstrates the use of IIF function in SQL Server 2012.

IF OBJECT_ID('tempdb..#FunctionTesting') IS NOT NULL

DROP TABLE #FunctionTesting

CREATE TABLE #FunctionTesting
  (
  ID INT IDENTITY(1,1)
  ,EmpName VARCHAR(50)
  ,NightShiftDay INT
  )

INSERT INTO #FunctionTesting VALUES
  ('Ritesh Shah',1)
  ,('Rajan Shah',2)
  ,('Bihag Thakar',3)
  ,('Kavan Dhruv',4)
  ,('Paresh Prajapati',5)
  ,('Kalu Bhuva',6)
  ,('Prapa Acharya',7)
  ,('Bhushan Shah',1)

--Use of IIF
 SELECT
  IIF(NightShiftDay=1 or NightShiftDay=7,'WeekendNight','RegularDayNight' ) AS ShiftDetail
  ,EmpName
  FROM
  #FunctionTesting

--used to achieve the same with CASE....WHEN since long
  SELECT
  CASE WHEN NightShiftDay=1 or NightShiftDay=7 THEN 'WeekendNight' ELSE 'RegulardayNight' END AS ShiftDetail
  ,EmpName
  FROM
  #FunctionTesting

Here is the output of both SELECT statements

I have earlier written few articles to demonstrate few different SQL Server function, you can refer those articles from the table given below:

Function Link to learn
LEAD Click Here
LEG Click Here
CUME_DIST Click Here
CHOOSE Click Here
PERCENT_RANK Click Here
LAST_VALUE Click Here
FIRST_VALUE Click Here
EOMONTH Click Here
DATEADD Click Here
DATEFROMPARTS Click Here
DATETIMEFROMPARTS Click Here
TIMEFROMPARTS Click Here
PARSE Click Here
TRY_PARSE Click Here
TRY_CONVERT Click Here
ISNULL Click Here
COALESCE Click Here
CONCAT_NULL_YIELDS_NULL 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.

sys.dm_server_registry – New Dynamic Management View (DMV) in SQL Server 2012

sys.dm_server_registry – New Dynamic Management View (DMV) in SQL Server 2012

As I said earlier either that SQL Server giving more & more power to the administrator of SQL Server by making SQL Server related information available in SQL Server Management Studio directly. Information is two side bladed swords, if you don’t use it wisely than you will be in trouble.

Note: Registry is very sensitive part and change without actually know what exactly you are doing, you will be in trouble.

So far, if you wanted to get some registry level information for SQL Server, you had, mostly, two options.

1.)    Go to registry from Windows

2.)    Use “xp_RegRead” extended stored procedure

SQL Server 2012 is giving you better way to handle this by providing sys.dm_server_registry. You can simply query this new dynamic management view and find all SQL Server related registry with its name and value right from your SQL Server Management Studio. Isn’t it fantastic?

You can set alert email by querying some important value in SQL Server JOB. For example you would like to get an email whenever port of SQL Server changes.

Let us now query this DMV and see its result:

 SELECT * FROM sys.dm_server_registry 

Here is the output of above query :

You can read my earlier article I written for “XP_RegRead” from 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.

Sys.dm_server_services – new DMV in SQL Server 2012

Sys.dm_server_services – new DMV in SQL Server 2012

Microsoft is giving more power to the user of Microsoft SQL Server with each release of SQL Server. SQL Server 2012 is no exception and came up with lots of new features, tools, DMVs, system stored procedure, functions and much more. Today we are going to see one of the new DMV in SQL Server 2012, named “Sys.dm_server_services” which returns information about the SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server. You can use this dynamic management view to report status information about these services.

Generally we used to use WMI script or Services.msc under control panel -> Administrative tools to see the installed services of SQL Server and the status of those services. In SQL Server 2012, you can login to SQL Server Management Studio (SSMS), if SQL Server Services is running, and execute simple DMV query to see all SQL Server related services with its status. Isn’t it useful?

Currently I am using WMI script to keep a watch on status of services which used to send me an email if any of the services found stopped in my production server and SQL Server 2012 made my life easy by providing “Sys.dm_server_services”.

Let us execute simple SELECT query on “Sys.dm_server_services”.

SELECT * FROM Sys.dm_server_services 

Here is the screen capture of above query:

You can get important information about SQL Server related services like:

  1. Service Name
  2. Service Description whether it is configured to run Automatic or manual etc.
  3. Current status like 1(stopped), 4(running), 7 (Paused) etc.
  4. Last_Startup_time
  5. Service_Account

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.

Error Fix – Cannot execute as the database principal because the principal dbo does not exist this type of principal cannot be impersonated or you do not have permission

Error Fix – Cannot execute as the database principal because the principal dbo does not exist this type of principal cannot be impersonated or you do not have permission

 

Earlier I have written one article to setup replication in SQL Server 2012 and remove replication from SQL Server 2012. One of the colleagues in my previous company (Yes, I do keep relation with my ex. colleagues :) ) sent me an email to help.

While he was trying to remove publication by following the instruction given in my article “remove replication from SQL Server 2012”,  Error seems interesting and looks big but solution to this error is very easy and short. he comes across the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not delete publication 'AdvertureWorks2012AddressObjects'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.0.2100.60&EvtSc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeletePublication&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. Changed database context to 'AdventureWorks2012'. (Microsoft SQL Server,Error: 15517)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2218&EvtSrc=MSSQLServer&EvtID=15517&LinkId=20476

Error would looks like this screen capture:

If you are trying removing the publication with the login which has insufficient privileges, you might come across this error.  There is one small command to execute to give proper permission to your login. Execute following command in your SQL Server Instance.

ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [LoginName];

As soon as you will have proper permission, you will be able to remove publication.

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.

Remove Replication from SQL Server 2012

Remove Replication from SQL Server 2012 (Part 2)

Removing replication needs some steps to be performed in proper sequence otherwise either replication will not be removed or not completely removed which leads some issues in future especially log file size related issues.

For removing replication, it is mandatory that we have one replication setup to remove. You can consider this as pre-requisite of this article. If you don’t have any replication setup to remove, you can set up one replication by following step given in my earlier article “Understanding replication with Implementation of Snapshot Replication in SQL Server 2012 (Part 1)

If you are ready with replication, let us move ahead. Follow the steps given here to remove the replication.

1.)    Delete the subscriber from Replication -> Local Publication -> “Subscriber Name”. look at following screen capture for more detail

2.)    Delete the publication from Replication -> Local Publications -> “Publication Name”

3.)    Delete the distribution database by right click on Replication and click on “Disable publishing and Distribution …”, follow the screen capture for more detail:

Once you are done with removing of Publication, Distributor and Subscriber, you can confirm whether distributor database is there or not manually. For more confirmation, you can execute the following command in the SQL Server instance where you have had your distribution database.

exec master..sp_dropdistributor

As soon as you execute above given command in the instance where you have had your distributor database, you will come across following error as it can’t find distributor database because we have already removed it.

 Msg 21043, Level 16, State 1, Procedure sp_dropdistributor, Line 50
 The Distributor is not installed.
 

Now let us execute following SELECT statement in principal database to confirm whether any article still having replication status or not.

SELECT
*
FROM
sys.sysobjects
WHERE
replinfo=1

You should get no result from above SELECT statement as we have removed replication completely.

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.