I have been managing DBAs for over ten years now and one question that always seems to come up, usually from management, is what do DBAs do anyway?  Hopefully, you have read my prior post, “Yes, Production DBAs Are Necessary!” and you know where I stand on this issue. 

 

I fully believe in the role of a production DBA and, as someone who has been in this role for well over a decade, would like to define, based on my experience, what I think that role should be.  The most effective way of doing this, I believe, is to define what a production DBA should and shouldn’t be expected to do.  Granted, this won’t work perfectly for every situation and this is not intended to be an exhaustive list, but the following should be a good guideline for defining what a production DBA does and why they are critical to ensuring that your data is safe and available.

 

User data

A production DBA should not have anything to do with data. There, I said it. This statement alone is going to generate tons of controversy, but let me explain and specify that I’m referring to user data in user databases. This confuses many people because, after all, DBA does stand for database administrator. Let me clear up that confusion right now by saying that database is one word and is a manageable entity just like a server. Data lives inside a database, much like an application lives on a server. Typically, there is a lot of data in a database, just like there can be many applications on a server, each of which may have their own application administrator. As in the case of the application analogy, we don’t expect the system/server administrator to necessarily manage all of the applications on the server, the production DBA should not be expected to manage the data in the database. The production DBA is responsible for the uptime of the database, the performance of that SQL Server instance and the safety (backups/DR) of the data in the database, not for the data itself. There should be other roles in the enterprise responsible for determining how the data gets into the database and how it is used from there, namely, data architects, database developers, etc.

 

Optimizing T-SQL

A production DBA should work with database developers to help them optimize T-SQL code. I emphasize work because production DBAs should not be writing code, outside of administration automation. This goes hand in hand with #1 above and, when you think about it in practical terms, makes sense. A production DBA may be responsible for managing hundreds or thousands of user databases all containing data for different uses. A production DBA can’t practically understand all of this data and be able to write code against it effectively. What the production DBA can do, though, is help the database developers optimize code by looking at available indexes and discussing with them how to best to arrange joins and where clauses to make the most effective use of indexes and/or create additional indexes. Additionally, if your organization falls under the Sarbanes-Oxley or PCI regulations, this segregation may be mandatory. Your developers should not have access to production databases. Their code should be promoted by the production DBA, who does have access to the production databases. This also means that you should have a development environment that reasonably approximates your production environment.

 

Managing Instances

The production DBA should manage the SQL Server instance(s). This is a big job and if your data is important and you have more than a handful of instances, it is a full time job. Let me breakdown what this actually includes to illustrate just how large a job it is:

  1. Install/patch SQL Server instances – The production DBA is responsible for installing any new instances of SQL Server and making sure that existing instances stay current on Microsoft patches. This isn’t just a matter of running through an install wizard. A number of things have to be considered by the production DBA when a new instance of SQL Server is installed. These include:
    • Collation settings. Questions have to be asked about how the application that uses this data expects the database to handle the case of words, accents on words, code pages that the application expects to be used (this gets into localization and language for those shops in other countries or that are responsible for servers that reside or are going to be accessed by users in other countries).
    • Drive/File Layout – To make the database instance run optimally, the production DBA has to consider what drive resources are going to be available and how the database files should be laid out across those resources. During this phase of the installation the production DBA has to consider whether only local storage is available or whether storage will be housed on a SAN. If it is going to be housed on a SAN, the production DBA needs to work with the SAN administrator to ensure that the LUNs are set up appropriately for optimal SQL Server access, which in and of itself requires a lot of knowledge and experience.
    • Scalability – The production DBA should be involved in developing the specifications for the hardware. Questions that the production DBA will be asking of the various parties include, how many concurrent users will be accessing the data, how many databases will there be, is the data static or changing and how does it change (i.e., batch load, transactional updates), etc. This will give the production DBA a better idea of what kind of resource utilization to expect and drive the hardware specification process. It will also help determine recovery model and backup strategies.
  2. Create, and periodically test, a backup and recovery scheme for your enterprise’s data. Things the DBA has to consider as part of this include:
    • Is the data development or production data? Yes, development data gets backed up and is considered in an effective backup/recovery scheme because, after all, you don’t want to lose any of your development effort; it just isn’t prioritized as highly as production data.
    • How often data is updated in databases and how is it updated (online transactions, batch, etc)? This determines the recovery model that should be used and leads the DBA to ask another question, what is the maximum acceptable data loss in terms of time? With the answers to these questions, the DBA can more effectively determine the type and the frequency of backups (full, differential, transaction log, etc).
  3. While somewhat tied with backup/recovery, the DBA is also tasked with helping to come up with a disaster recovery strategy for the SQL Server environment, within the constraints of the enterprise’s available resources.
  4. Uptime/performance – The DBA is tasked with managing/monitoring those things that would impact the availability of the databases. This includes CPU utilization, I/O performance, disk space, reviewing event and SQL Server logs,etc.
  5. Help design the security model for SQL Server instances. Within SQL Server, there are a number of built in security roles, both at the instance level and database level that users can made members of in addition to the ability to create custom roles. Additionally, SQL Server can operate in Windows Authentication mode, which integrates your domain security and uses the users’ Windows domain accounts or Mixed (or SQL Server and Windows Authentication Mode) Mode, which allows accounts to either be Windows domain or native SQL Server accounts. There are a number of factors to be considered here to make sure that your users can use your system while still protecting and safeguarding sensitive data and the SQL Server configuration.

 
So as you can see, the production DBA has plenty of things to contend with in order to effectively and efficiently maintain a SQL Server environment.  The bottom line comes down to how valuable is your data to you and how much are you willing to risk by not allowing your DBA to dedicate themselves to maintaining and safeguarding it.

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.

Being a helper

28 September 2009
They looked a little happer than this

They looked a little happer than this

Last Sunday, Tim and I started a four week session helping out the 4th and 5th grade Sunday School class at our church.  Our church prefers that there is more than one adult in each class and we’re switching off every four weeks with another member of the congregation.  Helping out in a Sunday School class is something that we had talked about doing for quite awhile.

 

During the class, the main instructor was in her groove.  She’s been teaching this class for some time and the kids are used to her and the curriculum.  While I know that it was helpful that we were there (at least in the kid wrangling department), I wasn’t sure that we were making that much of a difference.  We sat with the kids, played games with them, sang with them, but pretty much followed the teacher like they did.  I wondered (and I think that Tim did as well), if our being there mattered.

 

Before I go on, let me be clear on something.  We didn’t volunteer to help for the fame or glory or high-fives or whatever else volunteering for a Sunday School class might get you.  We know that it’s our responsibility and our honor to serve.  We’re also fallible humans…

 

In any case, after Sunday School, we went to the church service.  While we were there, I saw a couple of the students from class and they gave me huge, beaming smiles.  That’s when I remembered – the kids don’t measure what you did or how you did it, but that you took the time to be with them.  I thought back to when I went to Sunday School and, although I can’t remember who did what, I do remember the ‘grown-ups’ that participated.  I remembered thinking that it was great that they wanted to help us to learn. 

 

I’m glad that we’re taking part in this class and working with these kids.  The smiles from the kids are perks that you never get at the workplace.

 | Posted by tledwards | Categories: Miscellaneous, Personal | Tagged: , , |

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.

Replication tidbit

17 September 2009

I had set up replication a few times with SQL Server 2000 and there were times that I didn’t want to use a snapshot or a backup – had the schema and data already available and I just wanted to push new data over there.  Setting up the subscription was pretty easy.  In the Initialize Subscription dialog box, I just checked ‘No, the Subscriber already has the schema and data’.  Simple, eh?

initialize_2000

Recently I ran into the same situation – couldn’t use a snapshot or a backup (don’t ask).  The subscription database was already in place and I just wanted to push any new transactions from the publication over there.  When I went through the handy dandy replication wizard, though, I didn’t see the option that I had remembered from SQL Server 2000.  After an initial panic attack, I did some research.  The option is still there, it just looks different.

initialize_2005

All that you need to do now is uncheck the Initialize checkbox in the Initalize Subscription dialog box and away you go.  

Live, learn and replicate.

You can query everything…

16 September 2009
The possibilities are endless

The possibilities are endless

Well, probably not everything, but there are some times that you might want to execute a query against a server other than the one that you’re currently on. Let’s go further and say that you don’t want to use the servername.dbname.schema.table model.

EXECUTE is used to pass commands to linked servers (http://msdn.microsoft.com/en-us/library/ms188332.aspx ). When executing a query, the syntax is:

EXEC(’some random query’) at servername – note that the query within the parenthesis needs to be quoted.

Now here is the part that I found cool (okay, I said it. It’s geeky and yet still cool). The server that you’re querying against can be passed as a variable. For example, I want to know how many databases are on MyServer.

declare @server varchar(30), @cmd varchar(100)
set @server = ‘MyServer’
set @cmd = ‘EXEC (”SELECT count(name) from sys.databases”) AT ‘+@server+
exec(@cmd)

Because I’m easily amused, finding that out pretty much made my day. In my thirst for knowledge, though, I thought I’d dig deeper. Maybe I have a table (server_inv – id identity, name varchar) that holds all of my servers and I want to figure how how many tables I have on each of them AND I want the server names to be returned as well. I’m never satisfied. So here’s how I’d do that:

SET NOCOUNT ON
declare @next_one int,
@this_one int,
@counter int,
@server varchar(20)

SELECT @counter = 1

SELECT @next_one = MIN(id)
FROM server_inv

SELECT @this_one = id,
@server = name
FROM server_inv
WHERE id = @next_one

WHILE @counter = 1
BEGIN
declare @sqlcmd varchar(1000)
set @sqlcmd = ‘EXEC (”SELECT ””’+@server+””’, count(name) from sys.databases”) AT ‘+@server+
exec(@sqlcmd)

SELECT @next_one = NULL

SELECT @next_one = MIN(id)
FROM server_inv
WHERE id > @this_one

IF ISNULL(@next_one,0) = 0
BEGIN
BREAK
END

SELECT @this_one = id,
@server = name
FROM server_inv
WHERE id = @next_one
END
RETURN

Now, I understand that this is a pretty simplistic example, but hopefully it’s an example of what can be done. I’ll admit that I haven’t fully played with this EXECUTE command and there may be limitations to its functionality.

It’s probably far easier to put together a Central Management Server if you’re on SQL Server 2008, but that’s a post for another day.

 | Posted by tledwards | Categories: SQLServerPedia, T-SQL | Tagged: , , |

Getting started

11 May 2009
Any stalkers out there better watch out!

Any stalkers out there better watch out!

I was in the car a couple of weeks ago and the Beach Boys song “All Summer Long” came on.  The first verse is “Sittin in my car outside your house” – inspiring my 11 year old son to ask “Is this a song about a stalker?”.  That got me thinking about how much things have changed over the last couple of decades.

It used to be that you learned a skill, found a job and then used those skills to work until you retired.  That’s not the case now.  According to a Department of Labor study , people born in the later baby boom years have an average of 10.8 jobs between the ages of 18-42.  That breaks down to folks putting themselves out into the job market about every 2 1/2 years.  Doing that means keeping your skills up to date and relevant.

This is especially vital in the IT world.  If this is the profession that you choose – then you’ve set yourself up for a lifetime of learning.  As DBAs, we’re fortunate in that we have so many different sources to learn from.  Blogs, forums, newsletters, Twitter, etc. provide more information than any normal person could assimilate.

And that brings us to our blog.  Our hope is that we’ll be able to provide some useful information, bad humor or some combination of the two to other DBAs.   Enjoy and we’ll see you soon

 | Posted by tledwards | Categories: Miscellaneous | Tagged: , , , , |