I know that, for many, this is a controversial topic.  There are those that believe that there really is no such thing as a SQL Server production DBA and that DBAs should be jacks of all trades doing anything from database development to OLAP/BI development to .NET programming to being a webmaster or network/server/system administrator.  It seems that everywhere I turn anymore, job postings, sharing horror stories with colleagues, and even blogs from members of the SQL Server community, I see references to production DBAs being more than just a DBA.  It as if, somewhere, managers are thinking that they need to have someone manage their SQL Server databases, but they don’t know what that means, so the first thought is “let’s just make it part of someone’s duties that we already have on staff.”  The question is constantly asked, “Why can’t the database developer handle that, he/she already has to use SQL Server?” or the common mistake, “let’s just let the server administrator handle it.”  There has even been a term coined for this, “the reluctant DBA.”  I have actually heard SQL Server compared to Access since it has wizards and, because Access doesn’t require a production DBA, why the heck should SQL Server?  In my experience, this perception is especially prevalent in the small and medium-size (SMB) market, but there are large companies and managers that have grown up in medium-size companies that seem to reinforce this misconception.

 

Microsoft hasn’t really done anything to correct this misconception.  From a marketing perspective, I guess it is in their best interest for prospective SQL Server customers to think that it doesn’t take much to manage a production SQL Server environment.  When companies purchase Oracle or move their applications to Oracle databases, it is a foregone conclusion that dedicated production DBAs are necessary and so, these companies typically build that into their cost calculations.  I guess Microsoft feels that if customers don’t know a DBA is required, it makes their product look that much less expensive.

 

Now don’t get me wrong, I am not saying that database developers, server administrators, network administrators, etc. can’t become DBAs, they just can’t do it without training and experience, just like they didn’t fall into their current jobs without training and experience and the job of production DBA certainly can’t be done (at least not effectively) in someone’s “spare time.”  As SQL Server has matured, it has become extremely flexible and full featured, but these features come with a cost: complexity.  SQL Server can be used and configured in a myriad of different ways and for many different scenarios, but who is going to manage that and recommend how it should be configured and used for a particular purpose if you don’t have a production DBA?

 

In my next post, I will discuss what a production DBA does and what they shouldn’t do.

This makes perfect sense!

This makes perfect sense!

Today’s blog post is more of a question to generate some discussion and maybe, collectively, some ideas around how to track system dependencies.  When I say “system dependencies”, I mean the components that are dependent on each other in your enterprise data infrastructure.  For example, how do you keep track of the fact that Database A is dependent on SQL Server instance A, which in turn is dependent Server A, which may or not be a physical server and, if not, then is dependent on Virtual Host A.  And then going from the database out, what application(s) are dependent on Database A, what web servers or application servers are they dependent on and what is the criticality of those applications to the enterprise.  Additionally, reports and/or cubes could be dependent on Database A and those all have their own dependencies as well and so on.

 

I would be very interested to hear your thoughts on this subject and how you are tackling this issue within your organization.

Ever been frustrated when working with the way that SQL Server stores run dates and times from jobs scheduled and run using the SQL Server agent in the sysjobs, sysjobhistory, etc tables?  SQL Server stores the date and the time in separate columns in these tables and stores them as data type int, which can be a real pain to deal with if you need to compare them to an actual date.  In SQL Server 2005, there is a little known and sparsely documented function called msdb.dbo.agent_datetime that you can use to create a real date out of these columns.  To use it is pretty simple.  Say you want to see if the run_date and run_time from the sysjobhistory table is greater than yesterday’s date.  The syntax for that would be:
 
select a.name, (msdb.dbo.agent_datetime(b.run_date, b.run_time)) as RunTime
from sysjobs a inner join sysjobhistory b
on a.job_id = b.job_id
where (msdb.dbo.agent_datetime(b.run_date, b.run_time) > getdate()-1)

It is pretty simple to use, but an extremely powerful tool.  Unfortunately, SQL Server 2000 stores the agent dates the same way, but it doesn’t come with this function.  No worries, below is the code should you ever need to create this function on a SQL Server 2000 instance:
CREATE FUNCTION agent_datetime(@date int, @time int)
RETURNS DATETIME
AS
BEGIN

RETURN
(
CONVERT(DATETIME,
CONVERT(NVARCHAR(4),@date / 10000) + N’-’ +
CONVERT(NVARCHAR(2),(@date % 10000)/100) + N’-’ +
CONVERT(NVARCHAR(2),@date % 100) + N’ ‘ +
CONVERT(NVARCHAR(2),@time / 10000) + N’:’ +
CONVERT(NVARCHAR(2),(@time % 10000)/100) + N’:’ +
CONVERT(NVARCHAR(2),@time % 100),
120)
)
END

I have run into issues several times when I have had Remote Desktop Sessions (RDP) open to servers and my computer has had to reboot unexpectedly (either because of WSUS updates or because it has hung) and RDP won’t let me get to my existing session, it just creates a new one when I go to login.  This uses both available administrative RDP sessions on the server (see picture below) and makes it inaccessible to anyone else trying to access it through RDP, as well as keeping me from getting to the work I had in process in the previous session and any windows that I had open.

rdp_img1

So, in the case of this example, I already had an RDP connection established to CORPI at the time my computer rebooted (ID #1).  When my computer came back up, I went to login via RDP to CORPI and it created another session (ID #2).

After much research, I found a way that Windows will let you access an RDP session that you have already established.  It is a command run from the command line on the server, so you would go ahead and login as Session ID #2 and once logged in, do the following:

  1.  Click Start, Run and type in “cmd” to get to a command prompt

 rdp_img2

  1. At the command prompt, type in the following command, tscon 1 /v
    1. The tscon command is the Terminal Services connection command.
    2. The argument, 1, is the Session ID.  This should be changed to whatever Session ID that you want to connect to.  In this case, it was Session ID #1.
    3. The /v parameter tells the tscon command to display information about the commands that are being performed.

Once you hit enter, your current connection to the server will be disconnected (in the case of my example, above, that would be Session ID #2) and you are immediately reconnected to the Session ID# that you specified in the tscon command.

Once you hit enter, your current connection to the server will be disconnected (in the case of my example, above, that would be Session ID #2) and you are immediately reconnected to the Session ID# that you specified in the tscon command.

 

I was also going to add that if you want to RDP to a server and both connections are in use, you connect by typing the server name followed by a space /admin and connect to the console session.