Rodney

High Jinks, Hi Jacks, Exceptional DBA Awards and PASS

2011-10-07T04:58:00Z

The countdown to PASS has counted down. The day after tomorrow I will board a plane, like many others, on my way for the 4th year in a row to SQL PASS Summit. The anticipation has been excruciating but luckily I have this little thing called a day job as a DBA that has kept me busy and not thinking too much about the event. Well that is not exactly true since my beautiful wife works for PASS so we get to talk about SQL from the time we wake up until late in the evening. I would not have it any other way and I feel very fortunate to be a part of this great event and to have been chosen as the Exceptional DBA Award judge also for the 4th year in a row. This year, I will have been again tasked with presenting the award to the winner, Mr. Jeff Moden and it will be a true honor to meet him in person as I have read many of his articles on SSC and have attended his session at PASS previously. The speech is all ready but one item remains, which will be a surprise to all who attend the party on Tuesday night in Seattle (see links below).

Let's face it, Exceptional DBAs everywhere work very hard protecting our data stores, tuning queries, mentoring, saving money, installing clusters, etc and once in a while there is time to be exceptionally non-professional and have a bit of fun.

Once incident that happened this year that falls under the High Jinks category was when my network admin asked if I could Telnet into a SQL instance and see if I could make the connection through the firewall that he had just configured. I was able to establish a connection on port 1433 and it occurred to me that it would be very interesting if I could actually run T-SQL queries via a Telnet session much like you might do with an SMTP server. With that thought, I proceeded to demonstrate this could be possible by convincing my senior DBA Shawn McGehee that I was able to do so. At first he did not believe me. It shook his world view. It was inconceivable. What I had done, behind the scenes, of course, was to copy and rename SQLCMD.exe to Telnet.exe and used it to connect and run a simple, "Select * from sys.databases" on the SQL instance. I think if it had been anyone other than Shawn I could have extended this ruse indefinitely but he caught on within 30 seconds. It was a fun thirty seconds though.

On the High Jacks side of the house, which is really merged to be SQL HACKS, I finally, after several years of struggling with how to connect to an untrusted domain like in a DMZ with a windows account in SSMS, I stumbled upon a solution that does away with the requirement to use SQL Authentication. While "Runas" is a great command to use to run an application with a higher privileged account, I had not previously been able to figure out how to connect to the remote domain with SSMS and "Runsas". It never connected and caused a login failure every time for the remote windows domain account. Then I ran across an option for "Runas", "/netonly". This option postpones the login until a connection is made and only then passes the remote login you supply when you first launch SSMS with the "Runas" command. So a typical shortcut would look like:

"C:\Windows\System32\runas.exe /netonly /user:remotedomain.com\rodlandrum "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

You will want to make sure the passwords are synced between the two domains, your local domain and the remote domain, otherwise you may have account lockout issues, but I have found in weeks of testing this is a stable solution.

Now it is time to get ready to head for Seattle. Please, if you see me (@SQLBeat) or my wife (@Karlakay22) please run up and high five me (wait..High Jinks.High Jacks.High Fives.Need to change the title) or give me a big bear hug if you are strong enough to lift me off the ground. And if you do actually do that, I will think you are awesome and will not embarrass you by crying out for help or complaining of a broken back or sciatic nerve damage.

And now the links to others who have all of the details.

First, for the MVP Deep Dives 2, of which, like John, I was lucky enough to be able to participate in this year.

http://www.simple-talk.com/community/blogs/johnm/archive/2011/09/29/103577.aspx

And the details of the SSC party where the Exceptional DBA of 2011, Jeff Moden, will be awarded.

http://www.simple-talk.com/community/blogs/rebecca_amos/archive/2011/10/05/103661.aspx

Cheers!

Rodney

Denali-runner: Do DBAs dream of readable replicas?

2011-07-21T13:50:37Z

Several years ago, I sat attentively in my new boss' office, as he described a constant and painful thorn in the side of our company. The problem was with one particular highly visible and critical OLTP system that was now firmly rooted in production; it was being beaten up, badly. It was a large database, pushing into the terabyte range, and it was being kicked hard by the OLTP application to which it was married, and incessantly battered by at least four different reporting tools, each with hundreds of users scanning through tens of millions of records.

We needed a quick resolution, and the only hope was to find a way to offload reporting from the production database. "You will be a hero if you come up with a solution", he said to me, in earnest, and it is not every day a new DBA, looking to establish his merit, hears that. I jumped in enthusiastically, gathering requirements and searching feverishly for an answer within the community.

The enthusiasm would be short lived. Firstly, the data in the reports needed to be real-time, or very close (no more than 5 minutes lag), and users needed constant access to the data. This ruled out log shipping, and database mirroring with snapshots. Secondly, our schema was locked down by the vendor and many of the 100+ tables did not have primary keys or unique clustered indexes; this ruled out transactional replication.

I turned my attention to third party tool vendors. After many weeks of endless explanations of the requirements and painstaking testing, I finally uncovered a tool that met our needs, more or less. It wasn't cheap and it wasn't designed exclusively for SQL Server, so it came with foibles and frustrations. I reported my recommendation back to my boss, though it was a little disheartening to offer what was essentially an expensive compromise. I remember wishing fervently, usually on sleepless nights, that the day would come soon when Microsoft would include in SQL Server a solution that would give me the reliability and native support of log shipping and database mirroring coupled with the real time data availability of replication, without the schema requirements, administrative overhead and complexity.

Cue a huge silent drum roll and let's raise a glass to Microsoft, as it looks like they have finally come through, with the release of AlwaysOn and Availability Groups in SQL 11 (Denali). Together, these technologies extend the benefits of their High Availability/Disaster Recovery (HADR) offerings and also enable reading of the secondary or replica database, or groups of databases, on the secondary, tertiary and dare I say quaternary servers, so spreading the processing load across the local or remote networks.

There are those that will argue that their time and money might have been better spent developing a true data warehousing solution and maybe they have a point, but the fact is that the ability to offload reporting databases with this new technology offers me an immediate solution to a real world problem that we face every day. I am excited; in fact I haven't been this excited about new Microsoft technology releases since SharePoint, Reporting Services and Xbox. It's certainly a huge incentive for us to move to Denali sooner rather than later.

How much money it will save us depends on whether AlwaysOn and Availability Groups turn out to be Enterprise-only features, like snapshots in SQL Server 2005. If they do, well at least Microsoft has finally provided a very compelling reason to go down the Enterprise route. I hope, with baited breath, that it is only the primary server that is required to be Enterprise Edition, and all replica servers can be Standard Edition.

There I go dreaming again.

Going to the Score Cards - Exceptional DBA Awards 2011

2011-06-16T00:51:00Z

This year marks my 4th year as a judge for the Exceptional DBA Awards, founded by Red Gate in 2008 to "recognize the essential but often overlooked contributions of DBAs, the unsung heroes of the IT community." As a professional DBA myself I have been honored to participate as a judge. It is not an easy job because there is a voluminous amount of nominees from all over the world. Each judge has to read through every word of the nominee's answers, deciding what makes each person special and stand out amongst their peers. What drives them? What single element of their submission will shine above all others? It is my hope that what I am about to divulge to you as a judge will prompt you to think about yourself or someone you know and decide that you may be the exceptional DBA who can take home the gold at this year's award ceremony in Seattle. We are more than a few weeks into the nomination process and there are quite a number of submissions already. I can not tell you how many as that would not be fair. I can say it is not 1 million or more. I can also say that it is not 100,000. But that is all I can say about that. However, I can tell you that it is enough this year that we are breaking records on the number of people who have been influenced, inspired or intrigued by the awards in the past. I remember them all like it were yesterday.

fuzzy thought cloud here.

It was a rainy day in Seattle (all memories for each award ceremony will start thusly) and I was in the hotel going over my notes on what I wanted to say about the winner of the 2008 Red Gate Exceptional DBA Award. The notes were on index cards that I had either bought or stolen from my wife, I do not recall, but I was nervous which was unlike me. This was, after all, a big night for the winner. Of course, we, the judges and the SQL community, had already decided the winner and now all that remained was to present the award. The room was packed. It was Casino night, sponsored by sqlservercentral.com. Money (fake), drinks (not fake) and camaraderie flowed through the room. Dan McClain won the award that year. He worked for Anheuser-Busch at the time. I promise that did not influence my decision. We presented Dan with the award. He was very proud of this achievement, rightfully so, as was the SQL community for him. I spoke with Dan throughout the conference and realized how huge this award was for him, not just personally but professionally.

It was a rainy day in Seattle in 2009 and I was nervous. I was asked to speak to a group of people again as a judge for the Exceptional DBA Awards. This year, Josef Richberg would be the recipient of the award, but he would not be able to attend. We all prayed for him as he fought through an illness and congratulated him for his accomplishments as a DBA for his company. He got better and sallied forth and continued to give back to the SQL community that he saw as one big family. In 2010, and I am getting ahead of myself, he was asked to be a judge himself for the very award he had just received the year before.

It was a sunny day in Seattle and I missed it, because it was in July and I was not there.

It was a rainy day in Seattle and it is 2010 and Tracy Hamlin enters a submission that blows this judge away. She is managing a 50 Terabyte distributed database ("50 Gigabytes! Are you kidding me!!!", Rodney jokes.) and loves her daily job as a DBA working with developers, mentoring them and teaching them best practices with kindness and patience. She is a people person who just happens to have 10+ years experience with RDBMS'. She wins the award and goes on to be recognized as famous at PASS.

It will be a rainy day in Seattle this year when I sit amongst my old constituent judges and friends, Brad McGehee, http://www.simple-talk.com/books/sql-books/how-to-become-an-exceptional-dba,-2nd-edition/, Steve Jones, whom we all know and love at http://www.sqlservercentral.com and a young upstart to the SQL Community, this cat named Brent Ozar to announce the 2011 winner. I personally have not heard of Brent but I am told I have interviewed him for a DBA position several years ago and turned him down, http://www.brentozar.com/archive/2011/05/exceptional-dba-contest/ . I hope that did not jeopardize his future in the SQL world. I am a big hearted oaf and would feel horrible. Hopefully I will meet him at PASS and we can work this all out and I can help him get a DBA job.

The rain has stopped and a new year is upon us.

The stakes are high...the competition is fierce...the rewards are incredible.

The entry form awaits you. http://www.exceptionaldba.com/

I very much look forward to meeting you and presenting the award to you in front of hundreds of your envious but proud peers as the new Exceptional DBA for 2011 at the PASS Summit. Here is what you could win:

The Exceptional DBA of the Year receives full conference registration for the 2011 PSS Summit in Seattle, where the awards ceremony will take place, four nights' hotel accommodation, and $300 towards travel expenses. They will also be featured on Simple-Talk.

Are you ready?

Are you nervous?

MVP Trading Cards and War Games

2011-03-21T12:37:00Z

I had planned to blog tonight about the MVP Summit and all of the great new friends I met, mostly XNA and Direct X MVPs for some reason, but I could not wrap my brain around how I would deliver the idea I had without offending nearly every SQL MVP I know, plus a few I don’t. The idea struck me as humorous at first and I had quickly written a whole page on it. However, eventually I stopped and decided to throw it all out.

Suffice it to say in Seattle all of the MVPs were treated to a night at Safeco field to hit home runs, walk the bases and enjoy the company of other wet-whistled MVPs. Seeing all of the MVPs on the field of play, I thought, wouldn’t it be a great idea if the SQL MVPs had trading cards, like baseball trading cards. The stats and prestige rolled through my mind and I picked my fantasy team…quietly to myself, of course. And with that thought, I closed the blog without saving.

So, what else to blog about after such an interesting and worthwhile week? I had decided to dive deep into a technical issue and provide code, much like Jonathan Allen’s post, but then I thought, I need to get blogging and I really have nothing technical this week.

So…there you go…movies. I decided to write tonight about the movies that bring on motivation for us technical souls. The one and only movie that I can watch over and over again that always makes me want to stay up all night working on a technical problem is War Games. There is something about all the text hacking that harkens me back to the 80’s, where 300 baud modems were the DVRs and smart phones of my era. I miss those days. Then there is Swordfish, Anti-Trust and Tron. Classics. Matrix? Ok, but only in a pinch. And I can’t forget Hackers. I actually wrote a screenplay in college called “Hackers” about a year before the movie was released.

I am sure everyone has a techie film that inspires them, whether it is farcical computer drama where the concept of Gigabytes of information is so astounding that the actors have to shake their heads in utter bafflement, or a comedy like the 1984 Electric Dreams, also a favorite. I would like to hear about them. Now I need to watch WarGames again, as I have work to do.

Power of 2 - Installing SQL Server 2005 on an odd number of processors.

2010-09-07T23:51:00Z

Today I received an email from a buddy of mine who happens to be a MVP for System Center Configuration Manager (SCCM). I had asked him for some info a few weeks back about SCCM and he responded with some great insight as I would have expected. His email today was a payback for his generosity in that he asked me a SQL question, knowing that I was a SQL MVP. I could not let him down, right? His mail was very generic about a failing SQL Server 2005 installation. His exact words were :

"Some of my co-workers are trying to install SQL 2005 on a 2003 server. The install gets to the place where the services would start and it fails. He said from the logs he thinks it has something to do with the just in time debugger. He is only selecting two components to install, the database and workstation components."

And then he had asked if I knew what could be the issue.

Two questions jumped to my mind, 1.) was it a cluster install on the active node and 2.) how many processors are on the system, are there more that 16?

I called him and tried to diagnose the issue, starting with my questions. He said he thought it was not an issue with a cluster as it was a virtual system and that the number of processors was not excessive so it could not be the issue I thought it was.

As it turned out, even with only vague idea of what the failure could be, I was able to quickly pinpoint the issue, having to do with an inability to install SQL Server 2005 on a system with an odd number of procs, such as might be the case with a VM assigning 3 processors or a Tri-Core proc where the ratio of logical to physical processors does not produce a number that is a power of 2. All that is explained in the following link. And ironically, I knew of this problem because it happened to my team not more than 3 weeks back when we had a new system with an odd number of cores.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;954835

My buddy asked if we could slipstream the SP that fixed this issue (SP3) into the RTM SQL Server 2005 inststall. I told him "No", and that slipstreaming was only available in SQL Server 2008. But the workaround and full discussion of the problem exists. It can be tricky and frustrating if you have not experienced this before.

SQL Alphabet and Friday Nights

2010-08-27T22:42:00Z

So, it is Friday night in Lake Mary, Florida, our new home. I get off work at 3:30 on Friday and after a soothing cold Hazed and Confused IPA from our new favorite watering hole, it is time to play some SQL Alphabet. If you have ever been the unwitting participant at a SQL Saturday or PASS after party, where I force you to play, then you already know the rules. If not, here are the bullets that you can use to play for fun and profit. Well, fun anyway.

1.) Start with the letter "A" and name a SQL keyword (ABS or AS..that's all you get)

2.) Everyone playing gets a turn and must name a keyword that is SQL-related and starts with "A". If they can not then they drink or bow out.

3.) B,C,D.. and on until Z. (There are some Z's) but by this time they are literal ZZZZ's.

4.) Start over with a new group of people and win big because now you have lots of new words, unless you have had too much to drink and have forgotten them all.

You will be amazed at the fun and laughs and jovial good pats on the back..."Good One!" And if SQL is exhausted, try IT categories in general. What is an "AND" gate by the way?

New SQL Code Deployment Book and Damn I Need to Blog More

2010-03-24T20:33:00Z

Select datediff(d,'02/19/2009',getdate())

This value returned from the above SELECT statement is 398 and that is the number of days since my last blog post. As I was formulating my apology for this hiatus from blogging, it dawned on me that I also do not twitter (sorry tweet) and as apologies beget apologies, I then realized that instead of catching up on the backlog of blogs, I should write a book about what I have been most focused on in the past year, one month and 3 days.

That focus is my day job, which of course, most of us have. And that day job we share is why most of us read blogs, tweets, articles and even books in the first place. So my focus for the past year has been SQL code deployments and all of that entails. I am fortunate that Redgate has agreed to entertain my crazy notion of writing an entire book about this subject, which I have tentatively titled, "The Sound and the Fury". Wait..that is not right. Oh yes, a title more befiting a techical tome but with as much profundity, "Standardizing SQL Server Code Deployements - A Redgate Guide". The great American novel must wait a few more years.

As I begin this journey, I am inviting you to assist me in the discovery process and even be interviewed and included in the book itself. How do you do deployments in your company? Do you have a documented process or no process? Do you do code review or cross your fingers? Do you work for a small company or a Fortune 100 company? Government regulations or garage? It does not matter to me. I am not here to judge. I worked for both companies myself and have seen many things that you can relate to. If you would like to participate and are one of the 3 people still reading this blog after 398 days, please fill out my survey and let's get started.

http://www.surveymonkey.com/s/RRG86RH

Competitive Communities: Ring Generalship and SQL

2009-02-12T19:15:00Z

I was invited to a press conference a few days ago where Roy Jones Jr., Pensacola, Florida native and at one time considered to be the pound for pound best boxer on the planet, talked about his upcoming fight with Omar Sheika on March 21^st. Throughout the 90's I avidly followed Roy’s career, paid to view every fight, and cheered when he won. He grew in stature (figuratively and literally) and eventually became a multi-millionaire from his talents. After every fight, in front a worldwide audience, he always made sure to mention Pensacola. "Pensacola's in the house!" and I filled with pride for my hometown, for what we as a community could rally behind and come together in partnership and praise of our own hometown hero. We followed Roy through his career, ups and downs, wins and losses and never gave up hope. He had achieved everything he had wanted including moving up from middle weight to heavy weight and defeating John Ruiz, WBA champion at the time.

Fighting in Pensacola, possibly for the last time, Roy is giving back to the community who has followed him all of these years and I for one, a mild mannered DBA and author in Pensacola, cheer him for his efforts.

Now the segue to SQL…

We started a SQL Server users group in Pensacola last year in June. By “we”, I mean myself and my beloved fiancée, Karla. Our first meeting brought in about 12 people. It was at an airport conference room, a room overlooking a huge poster of Roy Jones Jr. coincidentally that people see when entering or leaving Pensacola. The next meeting doubled the initial numbers and continued on in this vein until our Christmas party which brought in close to 60 people, who by this time were all acquainted with one another. We did not know what to expect when we started. We knew only that we wanted to be part of something that brought people with common interests together for an evening of camaraderie and sharing our knowledge on SQL Server. It could have been a knitting group, rock collecting, paranormal research, what mattered is that we had an interest and we found that others did as well. We began to learn what companies in Pensacola hired and sustained IT professionals, developers, and DBAs. People came from surrounding areas like Alabama who were running their own users group, like LANUG (Lower Alabama .Net Users group) and Gulf Coast .Net Users Group. The world expanded rapidly. We were now visible. Attention was being given to Pensacola...what we wanted all along. It was not personal. It was for all of our user group members...our friends...our hometown. It was our tiny life raft we set adrift in a coastal community long forgotten as a potential IT market.

During this time, I traveled throughout Florida speaking at SQL Saturday events and I met many SQL MVPs and other professionals living right here in our fine state. These free, one day, multi-track events brought in 200 to 300 people. These were in bigger cities like Jacksonville, Orlando and Tampa. We HAD to have our own SQL Saturday! Can we get 200 to 300 people here in our little hometown of 50,000 people? Sure we can. The point is that SQL Saturday is the championship of SQL events, like a heavy weight fight. We are bringing it to Pensacola with the help of our friends we have met this past year. We spend many waking hours discussing, planning, e-mailing, and pulling resources to make sure this is going to be a success. Why?

Because now more than ever with the economy and some our new friends and user group members losing their jobs, it is important for us to maintain a point of contact, to keep the community alive. One of the most supportive sponsors for our group is an IT recruiting company (Tek Systems…shameless plug) and has helped some of our members be placed in other positions. We also post information on our various websites about other opportunities where we can.

We know our efforts will not have the huge impact of a Roy Jones Jr. bout. We will not ever fill an 8000 seat arena of people wanting to watch two DBAs go toe to toe on who can bulk load data the fastest. But, I have been working out lately. I think this year I will challenge another Florida SQL MVP to a showdown on DBA trivia right here in my hometown and charge admission via pay per view. I am not naming names, but I know who I want! And you know who you are. See you at our Pensacola SQL Saturday in June. Bring your Pumas and your Power Point! "Pensacola's in the house!"

The Holy Scrolling NO WAY Gold Nugget (Script Data) SSMS 2008

2008-12-08T20:21:00Z

Tonight, I set about blogging about something I thought was interesting, funny and marginally useful (IFmU)...or wait...funny, useful and marginally interesting (FUmI). Part of the blog post I had envisioned would be to create a database, it's objects and data to reproduce the FUmI that I wanted to impart. However...as I was creating said database I stumbled upon a new feature in SQL Server Management Studio that caused a gasping pause followed by a "No FUmIng WAY!" So, apparently no one told me that SSMS now includes an option when scripting databases to script the data too. I had to make sure so naturally I searched and despite a few obscure blog posts, there was little mention. Fair to say though, that several have already discovered this gold nugget...but I wanted to post it here for the ones who may not know.

In SSMS 2008, right click a database and select Tasks\Generate Scripts to initiate the Script Wizard. On the third page of the wizard, there it is if you scroll down..."Script Data". I scrolled thinking, 'Wow, it would be so...um...yeah cool if I saw something like ‘script data’”. And boom, there it was just like my first ever use of single quotes in double quotes like at the end of the last sentence.

So, yes, it does exist. And here is a sample of the INSERT statements created. For the record this works with 2008 and 2005 databases. I have not tried 2000 but I am hopeful. Also, it is smart enough to batch the transactions each several thousand rows.

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AVG ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'ABS ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AND ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BACKUP ', NULL)

print 'Processed 2800 total records'

I saw several articles lately talking about a new feature in SQL Server 2008 about how we can now declare and assign variables in line without a separate SET assignment. That was in the marginally interesting (mI) bracket for me prior. That feature now falls into the "whoopie freakin do" category comparatively.

And those of you wondering about these crazy inserts, please read the subsequent blog post.

Keywords, Brackets, Identifiers and Ridiculous.

2008-12-08T16:07:00Z

One of my DBAs recently reported to me that our indexing job was failing because a database included in the re-indexing code was actually named after a T-SQL keyword. I think I actually guffawed, not because it was impossible, but just because this "actually" happened. So, being inquisitive, I took this whole idea that a database could be named a keyword to the next few levels. How about tables, fields and the data itself? What follows is the query to create the "SELECT" database and a couple of keyword-named tables. It is an excercise in the ridiculous for sure, but makes one wonder. What if you failed to identify your objects with brackets or quoted identifiers?

Query to build SELECT database. Also, this script was created from the new "Script Data" section of the SSMS 2008 "Generate Scripts" wizard to save a few steps (see previous blog).

--BEGIN SCRIPT

USE [master]

/****** Object: Database [SELECT] Script Date: 12/08/2008 22:15:22 ******/

CREATE DATABASE [SELECT] ON PRIMARY

( NAME = N'SELECT', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SELECT.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'SELECT_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SELECT_1.ldf' , SIZE = 9216KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

ALTER DATABASE [SELECT] SET COMPATIBILITY_LEVEL = 90

USE [SELECT]

/****** Object: Table [dbo].[IN] Script Date: 12/08/2008 22:15:22 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[IN](

[BETWEEN] [nchar](25) NULL,

[LIKE] [nchar](10) NULL

) ON [PRIMARY]

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AVG ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'ABS ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AND ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BACKUP ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BEGIN ', N'END ')

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BETWEEN ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'CHECKPOINT ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'HAVING ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'EXIT ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'IF ', N' ')

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'LIKE ', N'LIKE ')

/****** Object: Table [dbo].[FROM] Script Date: 12/08/2008 22:15:22 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[FROM](

[Where] [int] NULL

) ON [PRIMARY]

INSERT [dbo].[FROM] ([Where]) VALUES (1)

INSERT [dbo].[FROM] ([Where]) VALUES (2)

INSERT [dbo].[FROM] ([Where]) VALUES (3)

INSERT [dbo].[FROM] ([Where]) VALUES (4)

--END Script

And what do you do with a database named SELECT with tables named FROM and IN? Well of course, you populate them with other keywords and query those values. Without explanation we delve deeper with a couple of interesting queries that actually work.

The first:

select * from [Select]..[FROM] WHERE [WHERE] > 1

And finally (my favorite):

Select *

from [IN]

WHERE ( [BETWEEN] BETWEEN 'AND' AND 'BETWEEN'

OR [LIKE] LIKE 'LIKE%'

) OR [BETWEEN] IN ('BETWEEN')

So I could have continued on, adding stored procedures named "BEGIN" or "EXEC"...but stopped with well enough. Plus my lunch break was over. Tomorrow's lunch: sp_who_knows?

DBA Decision #1 - New Hardware, Index Tuning or Application

2008-10-16T19:37:00Z

So I am sitting here doctoring (not nursing) a beer listening to Jimmy Eat World and Nerf Herder and thinking about a huge decision I had to make this week. The more I thought about it, the more I wanted to pose this scenario, this quote unquote real-world scenario to get some feedback on how other DBAs or managers might handle the same situation. I know from doing this for so long and the people I have met, that this happens quite a bit to DBAs.

The scenario:

You have a slow running front end application. It has been getting progressively slower over the past few months, however, it has always had issues with certain processes. You, as the DBA, know you are dealing with millions of records in some tables. These tables have a tossed salad approach to indexes, one clustered index and up to 30 or more non-clustered indexes. You discover that a former DBA has added non-clustered indexes based on the sound advise of the index tuning wizard or database tuning advisor (dta). When things run slow you analyze and see thousands of sustained shared locks on one of the dta indexes; no blocking but users complain of slowness. You also know that developers are working on a solution to address a potential bottleneck with code you can not see.

Business is...well...pissed. A decision is made to move to a new, bigger server. This always fixes problems with code, right? The other variable is that since we are moving to a new server, why not go ahead and go from a 32-bit SQL 2000 Enterprise install to a 64-bit Standard SQL 2005 install? It would be WAY easier to manage and should gain a 20 to 30% performance increase OOTB.

Okay, so User Acceptance Testing (UAT) has been performed on SQL 2005 but for functionality only.

You also learn late in the game that the server you want to move to is a single RAID 5 array of local disks, not SAN attached.

Wait...you also just learn the vendor does not officially support SQL Server 2005. They have customers there but do not know how it will work for us.

Two days prior to moving to this new server with double the processors with the same version of SQL (2000) the vendor responds and says they now will support 2005 and beckon us to go there.

Now you have to decide:

1.) Do you stall the upset users and work to address the slow running code by working with the developers and tuning/deleting the dta indexes in hopes that this will buy enough time to test SQL Server 2005 for a future upgrade (within two weeks)

2.) Do you go ahead with the SQL Server 2000 migration to the new server over the next weekend in hopes that it will address the users slowness and loss of productivity, though the problem may still exist on the new faster server.

3.) Do you move forward with SQL Server 2005 64 bit Standard, knowing, wow, knowing there is no easy way back if there are problems.

4.) Do you spend the next two days testing with the developers, delete the ferel index from dta, re-create all of the large clustered indexes late on a non-maintenance weeknight knowing that SQL 2000 does not have an online rebuild functionatliy and will require whatever is not completed to be rolled back and could impact production?

5.) Add you own decision here.

I will relay what I did (ed - will have to do) with this scenario in a reply.

Spiderlings and Kiting in a Hurricane

2008-09-02T19:16:00Z

So it has been several months since my last blog entry. And this one is going to ramble a bit after a long day of vigilantly overseeing many SQL Servers come back up after a hard shutdown in the middle of the night when UPSs and generators failed when one uncontrollable SPF (single point of failure) actually failed.

I moved this weekend in Pensacola, Florida. I moved 3 miles up the road. All of our belongings were placed load by load in an open trailer and carted to the new place. We were done before the rain of Hurricane Gustav hit. I watched as the house we were in emptied two years worth of accumulation. I also had to stop several times at the new house to deal with disk space issues because I also had the fortune of being on call due to...well...that is another rambling blog entry.

During the move, I kept stopping to watch our spider. I say it is "our" spider, but since I was the only one who did not want to kill it violently, I should call it my spider. It was and at the time of this writing still is an orb weaver or the argiope variety. It looks exactly like the pictures in the following link and is just as ominous. I am sure it could take down a wily raccoon.

http://home.att.net/~larvalbugrex/argiope.html

This particular spider had laid two eggs in the two weeks prior to the move. Again, like large Southern pecans, just like the pictures. It built a brand new large web every evening. I would check it out every morning with amazement. I had hoped against all odds that the spiderlings would hatch just before our departure or shortly thereafter. I do not know why. I just could not bring myself to kill something so beautiful that posed no direct harm. It is kind of like snakes...but again...another blog.

So, in my mind, knowing that all of my servers came back online with only 1 suspect DB out of 1400, I am thinking that this spiders eggs after two days have hatched and have spun their kiting webs to flutter off by the thousands to new places in the woods out back.

Hope.....

I know that will not be the case. I know that the landlord will most likely blow torch them all. I know that a BB gun or target archery set would be required to extinguish this beast and its prodigy. But I also knew, while waiting for the servers to come back online today (the SAN to fire up clean and SQL recovery of each individual database) that we would be dealing with corruption, suspect databases, backups that were incomplete, stress, "DB HELL". But that did not happen. All was good. Everything was fine.

So I will check the web tomorrow, the long-lived yellow bodied survivor with its two large eggs, before my landlord arrives with a blow torch to see if the hurricane wind has blown them to safe haven.

SQL Saturday Thoughts and SQL Alphabet

2008-05-05T21:52:00Z

With gas prices here in the panhandle of Florida where I comfortably reside soaring to over $3.60 per gallon, I knew I would spend at least $110 toting myself and my beautiful fiancee from Pensacola to Jacksonville, host city of the most recent SQL Saturday event this past weekend. Another $89.00 per night for 2 nights, not to mention food and beer (the biggest cost) all so I could present my session on a DBA repository that I have put together over the past year using SSIS and SSRS. In hindsight, it was certainly money well spent for a chance to participate in a most worthwhile event. Imagine..a free day of training with regional presenters, authors and developers most of whom have MVP tagged to their illustrious careers.

Cheers to Brian Knight, who obviously worked very hard to organise the event with the help of many SQL PASS volunteers from Jacksonville. Chris Rock, Andy Warren, Tim Mitchell and Joe Healy are a few other that I met who were most cordial. It is not always easy to tolerate a 6 foot 5, 270 lb obnoxcious oaf like myself who has pounded down 5 strong micro brew IPAs and wants to play SQL alphabet and win at pool.

By the way, I highly recommend SQL alphabet. Simply start with "A" and everyone has to choose a keyword or term from SQL services before continuing to "B".

Generally it moves quickly. "ABS()", "AND", "AVG", etc. It gets more difficult with more than 4 participants and more fun after each beer. By the time you get to "P" (pun intended) it is a stretch. I thought I had everyone out matched with "Publication" but the last person on line came up with "Partition" without hardly thinking.

I know there will be a convergence on Orlando next month for TechEd. I hope to see everyone there. Maybe we can finally get past "X". Extended stored procedures do count.

Weekly Status Report and How DBA Managers Lose Their Edge...

2008-04-24T14:03:00Z

So each week, I am required to turn in a weekly status report that demonstrates in summary fashion what I have accomplished the previous week. In a smoothly running SQL infrastructure of even 90+ servers, DBA managers may find themselves in a repetitive rut of similarity week to week. And for the DBA who reports to a network engineer, it really boils down to "SQL Good" or "SQL bad". When "SQL bad", which should happen less than 3% of the time heroic action is required.

It just so happened recently, reported fully in my WSR, that I noticed that an MSDB database had grown to over 8G and was growing by at least 100M per day. There was about 1G left on the C: drive where SQL was installed (not recommended) so I did not panic but I had to find out. I could have asked one of my proficient DBAs to investigate, but with the side-sagging over glut of non-grunt work, I was feeling like I would take this...I got it, seriously. First...find out where the 8G was coming from. Not as easy as it should have been. sp_msforeachtable to get row sizes did not work on the system database. I could have investigated. However, why not a quick cursor to get row counts. Still nothing revealed but my own skin-scraped embarassment at spending more than 20 minutes on this. I eventually came by and by to the sys.sysxmitqueue table. Being that it is a sys. table, it was not easily accessible, nay, it was unobservable by standard means. With a quick bit of research, I found the view that exposes this sys table and that is: sys.transmission_queue. There were close to a million records there. This all happened because someone long ago wanted to understand Service Broker..a noble idea, but without the understanding of how to turn it off, even after disabling Service Broker itself, or the endpoint that was created.

The database, msdb, was still enabled for the Broker and was apparently trying to send many many messages that it could not send.

SQL Bad.

Surely it would only be a matter of minutes to clean all of this up. Well, kind of. Because the data file was so full, shrinking was not going to be useful. I had to remove these failing messages. The way I discovered to do that was to create a new broker, which would flush out all messages, supposedly.

Alter database msdb Set New_BROKER

This did nothing that I saw but get blocked behind other processes (BRKR_Task as I recall but this was the beginning of the week and I do not keep good notes for my WSR)

So, next I disabled it.

Alter database msdb Set Disable_BROKER

This worked but did not flush anything but my face.

So I tried to end the conversation, which should have definitely flushed everthing.

END CONVERSATION '{1F9B06DB-7B4B-DC11-B999-005056A249D6}' WITH CLEANUP ;

And it began surely to remove data. But..unfortunately, it must have used delete statements because in about 10 minutes the log file filled to almost a gig. Remembering that I only had a Gig left to begin with, this was obviously not good. With 7.94Megs left on the drive, I now panicked.

Kill process...roll back...shrink log...turn in report.

At least, I thought, it will not try to send any more messages. The next day I discovered that I had 3G available...a small unexpected gift. So, it did work.

When I was in the trenches and doing this everyday, I would have wanted to know exactly what happened and I still do..the difference now is that I do not have to stay up until 3:00AM duplicating the issue just so I can sleep.

I AM losing the edge.

Diagnosis....More Diagnostics.

2007-10-01T19:42:00Z

Sometimes in my career as a DBA I feel like a doctor. Actually, this started before my stint as DBA...way back in my help desk days. Someone on the other end of the phone begins to impart their symptoms and like an ER intern, I listen intently to every detail. Often, like most good practioners, I interjected my questions at the appropriate pause, all the while formulating my conclusions that I would unveil when the person had exhausted themselves with putting together sentence after sentence. A recent case study went something like this (and as I parenthetically do in these little snippets, I will "sign post" my point - I ultimately would resolve how multiple Windows group membership in SQL worked and pretend all along to have known this while diagnosing the disease of the ODBConomia Braingonemisis):

"Several users can not connect to our new budget application. They have the same rights as all of the other users who can connect. We have tried for 3 days to figure this out so while you are in town, we wanted to see if you could help," the conversation began.

I pondered for a moment and finally asked, "So other users can connect?"

"Yes," came the reply.

"And do the users who can not connect get an error message?"

I knew the response would be affirmative, but lacking the crucual details. It always happens that way. "They can not connect" is generally as far as it gets.

"So what did it say?"

"It is an ODBC error. I did not write it down."

"Is there a user we can test this on?"

"Yes, but she is at lunch."

"Ok.." I wanted desperately to dispatch the interrogator to the corner office to sit an wait for said user while I moved on to other interesting issues. But I was caught in the web of mystery. That is how it goes. I can not give up until I know.

"Who is the user?" I asked finally. I will check out the database.

I got the name. I got the database. I got the server. I got it all. I was onsite with a group of people whom I had met only once and I had all of the information I needed to troubleshoot and damn I troubleshooted (just wanted a past tense there) until I had the answer. I am skipping ahead swiftly because I am bored with the dialogue. In reality I waited for the user to return from lunch and go through the rote task of making an ODBC connection from her computer and recreated the problem myself...on and on..until all at one I was satisfied. Here it is with no more Quotes.

In SQL, this user had her Windows account assigned to two groups...Group 1) Anatomically Correct and Group 2) Bipolar Dissonance. (Surely these are made up...but we can call them Group A and B..the point being alphabetically sequential.)

In Group A she was assigned to a default database to which she had no access. In Group B she had been granted all required access to the budget database to which she was trying to connect.

I determiend that her group membership alphabetically in Group A, having no access, was preventing Group B from allowing access. (and the other users having identical problems - two birds two birds two birds)

Simply changing the default database for Group A to "Master" to which everyone has access (publicly) allowed her to fall through like Marshall, Will and Holly to Group B and finally get past the ODBC error that had been driving everyone mad for 3 days prior to my arrival.

I explained this in detail to everyone, again as if I had known all along that group membership in SQL Server is apparently alphabetical. And I then quietly finished this blog waiting for all of the readers to dispute this with long treatises on how it really works and making me feel worse and worse until finally I break down and write another book on Reporting Services....Sans dialogue.