Is My Table Part of Replication?

Recently, I had someone approach me asking if a table was being utilized for replication. The person didn’t posses the knowledge of the different methodologies to track this information down.

While there are several ways to accomplish this goal one of the quickest is to run a query. The below query will provide you the publication to which your table is found in. This is extremely helpful when you have multiple publications for a database and one is unsure which one it is located in.

USE [DBNAME]
GO

SELECT sps.name
FROM syspublications sps WITH ( NOLOCK )
JOIN sysarticles sas WITH ( NOLOCK ) ON sps.pubid = sas.pubid
WHERE sas.name = ‘TableName’

Replace the [DBNAME] with the database that your table is found in, and replace the ‘TableName’ in the where clause with your table.

How Much Longer?

There have been instances in my career where I’ve had to perform backups off a routine schedule; I mean it’s inevitable, right? It’s nice to look at job history to see about how long the backup job normally takes; can give an estimate or an ideal of what to expect.

However, if you are like me I like to monitor and keep an eye on how much longer my backup will take. Below is a T-SQL script that I’ve picked up and tweaked over the years to help determine an estimated time on how much longer my backup will take.

SELECT r.session_id ,
r.command ,
CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete] ,
CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time,
GETDATE()), 20) AS [ETA Completion Time] ,
CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0
/ 60.0) AS [ETA Hours] ,
CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text,
r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset = -1
THEN 1000
ELSE ( r.statement_end_offset
– r.statement_start_offset )
/ 2
END)
FROM sys.dm_exec_sql_text(sql_handle)
))
FROM sys.dm_exec_requests r
WHERE command IN ( ‘RESTORE DATABASE’, ‘BACKUP DATABASE’ )

I apologize beforehand of the code being so choppy; my plugin editor is not working properly at the moment. The result set of this code set will provide you the following columns:

  • Session_id
  • Command
  • Percent Complete
  • ETA Completion Time
  • Elapsed Min
  • ETA Min
  • ETA Hours

There you have it; pretty straight forward and when a backup is kicked off you can execute this query to determine how much longer the backup will take. If my memory serves me correct I’ve ran this on SQL 2005 or greater.

24 Hours of PASS

SQL Training……..to a SQL DBA I’m always up for any training to learn new techniques, new features, or new processes. You throw in the word FREE and how can you help not be excited about what PASS has to offer.

PASS has recently released their lineup for the free 24 hr SQL training with some stellar presenters and some great topics. To check out the sessions and register for any of the online classes you want you can click below:

24 hours of PASS SQL Training

Enjoy!

Linked Server Set-Up

At times I take for granted some of the features that SQL has to offer. One of the features that I was asked about from some fellow colleagues is setting up a Linked Server. First, what is a Linked Server? A linked server basically allows a SQL Server Database engine to execute commands against OLE DB data sources outside the instance of SQL Server. There are some advantages in having a

Linked Server a few just to mention are:

  1. The ability to access data from outside of SQL Server
  2. The ability to issue distributed queries, updates, commands, and transactions on data sources
  3. The ability to address diverse data sources

CONFIGURING A LINKED SERVER

One of the ways that you can set up a linked server is through SSMS; you can perform the following tasks:

  • Connect to a server in SSMS
  • Expand the Server Objects
  • Right click Linked Servers
  • Select a new linked server

The first box allows you to select a specific server type.

The security section will allow you to define the logins.

Under server options ensure the data access value is set to true

From there you can test the connection and ensure the validity of the setup has been completed correctly.

For further research you can review some of the documentation in the below links from Microsoft (some are from prior versions of SQL but the gist is still the same):

Linked Server Database Engine

Creation of Linked Server

Orphaned SQL Instances

It has been some time since I have ran into this issue; however yesterday brought back some old memories of my early years as a developer and then DBA – orphaned instances. Currently, I run 3 SQL instances on my machine. I kept SQL Server 2008 R2 installed as well as SQL Server 2012 as I have some needs for both that I won’t necessarily go into in this post.

I had some instance name issues that I had to work through when I installed 2012 on my machine that I knew I was going to have to go back and clean up at a later time. Well, that time came yesterday. As I uninstalled 2008 R2, I must have performed a misstep and in essence ended up with two orphaned instances on my box. Most people would not be thrilled with this scenario however I was pretty stoked as I knew that I could use this to brush up on how to get rid of them.

I wanted to have a fresh install of 2012 only on my machine so I uninstalled 2012 and then it was time to deal with my orphaned instances.

Before I share this I want to iterate that this is how “I” handled this situation and  it requires manual intervention. I re-tested this out on another box as well just to provide a second test. I would not recommend doing anything on a prod environment until you test it out; no matter what blog or article you get your information from. One, it helps you to understand what you are doing and two provides security in that you can perform the tasks.

Okay so below are my steps I took to manually remove my orphaned instances:

  1. Click on the Start menu, choose Run and type in Regedit (nervous yet?)
  2. Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server
  3. Remove all sub-directories and Instance name folder
  4. Delete the folder from % Program Files%\Microsoft SQL Server\%name%

Once completed I like to do a fresh reboot; then when you perform your installation the old orphaned instances are no longer available.

I did not share my instance names as they may vary across the board but this is a quick way to manually clean up orphaned instances. I do implore you to use extreme caution when dealing with Regedit. If you do not know what you are doing you can do some major damage to your box.

DBA Deployments

There are many aspects a DBA must handle on a daily, monthly, yearly basis. One that has been sticking out to me in recent weeks is deployment methodologies and the various ones that other people might have in place.

For example:

My current process utilizes TFS and RedGate tools. Below is a representation of what a deployment of mine might look like.

  • Code is checked in to source control by developer
  • A release is created consisting of bugs and tasks within TFS by a release manager
  • Once the release is put into place in TFS a comparison with in TFS is completed on the branch or by a label to see what was deployed out to our first landing zone ( a QA environment) from the last deployment
  • The latest code is gotten from Red Gate SQL Source Control
  • Using Red Gate SQL Compare differences are compared and pushed based on what was scheduled on the release ticket

Now this is just a very high level view without all the intricate workings that are not defined out in this post. I am always looking for ways to improve processes and be as productive as can be. I am curious as to what others might be using or the different methodologies and processes that others in the community use to deploy out to their respective environments.

Fill free to drop me a line or comment.