DROPping all Databases on an Instance
I’m doing a testing my database restores. Notice that I say I’m testing the restores. If the backups don’t work, neither will the restores.
Part of my process is to drop the existing databases in the restore target instance. It’s really the only place I can ever imagine this script seeing the light of day.
SET NOCOUNT ON
DECLARE @v_dbname nvarchar(100)
DECLARE @v_SQL nvarchar(1000)
DECLARE c_databases CURSOR FOR
SELECT [name]
FROM sysdatabases
WHERE name NOT IN ('tempdb', 'pubs', 'master', 'msdb', 'litespeedlocal', 'model')
ORDER BY [name]
OPEN c_databases FETCH NEXT FROM c_databases INTO @v_dbname WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN
select @v_SQL = 'DROP DATABASE [' + @v_dbname + ']' exec ( @v_sql )
END FETCH NEXT FROM c_databases INTO @v_dbname END
CLOSE c_databases DEALLOCATE c_databases
Top Ten Operational Mistakes to Avoid
I’m working on a presentation for SQL Saturday in South Florida. The more I look at this, the more I really like it.
- Not Setting Min and Max Memory
- MIN: ”Use this much so nobody else can have it.”
- MAX: “Save some for the Operating System” – Prevents Paging
- Schema Changes at Peak Times
- ALTERing TABLEs causes locks on the entire table
- High Risk
- Difficult to roll back at peak times
- Using your Server as a Workstation
- Avoid Remote Desktop
- Never use SSMS directly from your server
- Improper Use of Traces
- Avoid in Production
- Never store on your Database Server
- Enabling Auto Shrink
- You can’t control when it runs
- What you shrink will just grow again
- Fragments your indexes
- Allowing Data Files to Grow in 1 MB Increments
- Causes disk fragmentation
- Every growth will ALTER the database
- Not using a Domain Account for SQL Services
- Allows you to interact with other servers. Very helpful with backups over the network.
- Not Configuring SQLMail Alerts
- Allows you to know when something fails
- Allows you to be active instead of reactive
- Not Testing Restores
- The worst time to test a restore is when your production server fails
- It’s good practice for when something does fail
- Not Configuring Instant File Initialization
There are a lot of good people out there who don’t realize they’re doing things to kill their system’s performance. Obviously this is a work in progress, but I refine it more and more all the time.
Meme Monday: I got 99 problems but a disk ain’t one
Tom LaRock posted an interesting meme for today. Here are my nine problems that aren’t disk.
- Vendors who make bad recommendations to my user community for “optimizing” SQL server, such as using autoshrink
- Other system administrators who insist on using SSMS from the database server
- An application that keeps changing the recovery model every time we upgrade
- A business application owner who doesn’t understand why I hate to make schema changes in the middle of the day
- Applications that create databases in locations other than the default paths I’ve specified in my server’s configuration
- A security system that will only run as SA
- An application vulnerable to SQL injection attacks that the business won’t spend money to replace/fix/secure
- An impatient application that sends an alert if it hasn’t been backed up in exactly 24 hours.
- A vendor who is taking their time in certifying and supporting SQL 2008 R2
What I Do
Knowing that I’m putting myself on the job market, I was recently asked to describe what I do. As a DBA, I realize that every DBA’s job is different. So it was an interesting exercise to put together a few paragraphs explaining what my current role is.
My company is rapidly growing. We’re in the process of replacing our current ERP system with a shiny new Oracle-based one in the next 10-12 months. While that happens, the company will grow another 40 percent. Our mandate from management is “Make it scale!” We have existing applications that need to grow and new applications that need to be put in place.
A friend referred to me as a jack-of-all-trades DBA. I think he’s onto something. As the only DBA in the company, I do it all. Or at least most. That means everything from managing applications to performance tuning to server management to scaling applications that we’ve outgrown. I’m also working on a consolidated reporting instance as well as designing a short-term data warehouse solution.
Let me explain that our entire IT infrastructure team is four people, and that includes our Director of IT. It means we all wear several hats. We have a network/sever guy, security/network guy, and a DBA. If it touches a database or database server, I’m involved.
Part of this growth (and a looming data center move) required virtualizing everything. And I mean everything. The only database server that hasn’t been virtualized is more of an appliance that runs on vendor-provided hardware. Every other sql server is virtual. During our virtualization project, we consolidated 14 servers down to four primary servers.
Another thing I’m doing is making systems scale that were never meant to. We have a homegrown Access “application” used by our manufacturing organization. It was this little database that is used to capture data for hardware testing. Like most applications of this vein, it grew into a beast. And somehow, it grew into a business critical beast. It had become the authoritative data source for products shipped. Recently, while looking at the design of the thing, I realized that the DBA who upsized the thing didn’t use clustered indexes. It was a HEAPing mess.
Most recently, I’ve been tasked making this database talk to its little brothers at some of our business partners’ locations. These remote locations need to get updated information about customer orders, parts, and BOMs. Not only that, we need to remote locations to report back to the mother ship.Historically, someone established a VPN connection and did this manually. Considering our tight network policies, automating this is quite a challenge.
Our existing collaboration/content management tool just isn’t going to work. We’re implementing Sharepoint. And we’re estimating the content databases to reach 500 GB within the next 18 months. I’ve had to prepare the database servers accordingly. This means content databases have to span multiple data files and multiple disks.
On top of all of this, we’re publicly traded and bound by Sarbanes Oxley. That means a good chunk of my time is spent satisfying auditors.
Using RUNAS to Clear a Hurdle
Like most companies, mine has to contend with some form of regulatory compliance. In our particular case, it’s Sarbanes-Oxyley 404, better known as SOX. We have some network policies to enforce compliance with the implementation of SOX. One of those policies is that people with elevated access to key systems have a seperate account for administrative tasks. This means I have two Active Directory accounts, usermike and dbamike. Oh, and of course, I’m not supposed to be logged into my regular workstations with my administrative account. Just to make life a little more interesting, we have a policy that says we don’t do administrative tasks with service accounts, such as SA.
usermike doesn’t have access to crap, especially database servers. Let me put it this way, Claire in Marketing has more access on the network than usermike.
As a DBA, this situation can be hell. When I first started, I was launching a remote desktop session to my SQL servers in order to run SQL Management Studio. The only thing worse than running a remote desktop session to a SQL server is running SSMS in that remote desktop session. When we bought a new SQL tool, I realized that keeping RDC sessions open all day just wasn’t the solution. I went looking for a better solution.
One day, I stumbled accross the RUNAS command. Basically, from a command line, I can launch an application and state the user that should be used to run the application. In my case, dbamike runs the application. It will prompt me for that user’s password, and off we go. It looks something like this for SSMS.
runas /user:domain\dbamike "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
After putting this in a batch file on the desktop, life became a lot simpler. My productivity soared.
Like most things, there is a gotcha. When I attempt to save a file, it will save it in dbamike‘s My Documents folder. That’s because the application is running as dbamike. It also means I can’t open files directly from usermike‘s Outlook mailbox. I have to save it to c:\mike first. It’s an extra step, but the trade-off is wel worth it.
Exploring my Options
I’ve been presented with a rather unique problem. And the right solution had left me a little perplexed.
My company has a SQL server at a business partner’s manufacturing site. The server is in what our network guys would consider a less than secure environment. They’re in the middle of setting up a permanent VPN tunnel, but they’re being incredibly tight with what traffic they let through.
The parameters in which I have to work are pretty specific:
- Users need to run reports against data that’s on the remote server.
- The data does not need to be real-time. Reports only run once a day
- Any connections must originate on our side of the tunnel, not the remote
- The solution needs to be fairly bullet-proof because we may not have a full-time SQL DBA in the future
- The remote database is small, less than 2GB
- The remote server is running SQL Server 2005 Express
- The remote server is not on our AD domain
- The remote site’s server will most likely be eliminated when we migrate to our new ERP system
I’ve looked at several options.
- Mirroring is out because both need to be online at the same time.
- Log shipping is out because of the complexity and potential lack of DBA.
- Clustering won’t work without shared disk, nor will it work over a VPN
It finally hit me that I was making this much harder than it needed to be. a simple backup and restore will completely fit the bill. Once a day, we will back up the remote server (assuming it’s not being backed up now) and then have the server on my side initiate the connection for a restore. Hopefully we can get the remote server on the domain to simplify authentication.
It’s potentially the simplest solution and the easiest one for a non-DBA to fix when something goes bump in the night.
Transitions
My current employer has made the decision to move to an Oracle-based ERP system. And the decision has been made to outsource DBA services. The only major system remaining on SQL Server will be Sharepoint. So my boss and I have agreed that I will be leaving at some point in the next six to twelve months.
I think this is a smart business decision for the company, and I’ve declined my boss’ offer for Oracle training. The company’s technology path and my career are going in opposite directions. I don’t want to be an Oracle DBA, and I don’t want to be cutting my teeth on a new technology during an ERP implementation. It takes years of experience with Oracle to manage their ERP system, and that’s why I think it’s a good idea to outsource that role.
This is an exciting time for me. The potential for a new opportunity is a bit overwhelming. Knowing that I will be out of a job is scary. At the same time, this is really good for me because a lot of the work I’m doing right now is application management and not that of a senior DBA. I have the time to really get it right. To use a baseball analogy, I’m going to be cherry picking my opportunities.
Speaking at SQL Saturday last week gave me a new confidence. I’m good at what I do, and now I’m waiting for the right pitch to come across the plate. In the meantime, I need to get my resume in order. I also need to get my work in a place where a more junior DBA can pick it up. And I need to start doing some serious networking to help provide a good supply of opportunities.
The real issue for me to figure out now is just what I want to do next in my career. Being in Boston gives me access to a lot of financial companies, which gives me an opportunity to grow in high availability and recovery. At the same time, those companies tend to be incredibly conservative. Being in Boston also gives me access to the high tech corridor and tons of startup companies. I’m not sure I’m ready to take on the growing pains of a rapidly growing company again. The last two taught me a lot about scalability and managing growth.
Consulting is an option, but I need to make sure I have access to good benefits. That leads me thinking that going solo isn’t a very good choice for me. Working for a small “boutique” consulitng shop would be incredibly appealing. I’d love to go out and do the tech work yet have someone else manage the business development end of it.
The good news here is that I don’t have to make any decisions right away. I have time to figure it out.
My First SQL Saturday #sqlsat71
Yesterday was SQL Saturday 71 Boston. It was held at the Babson College Executive Conference center, and it was a pretty amazing experience. Not only were the venue and food fantastic, but the event itself was pretty amazing.
SQL Saturday actually started on Friday evening with the speaker’s dinner. We met at the Top of the Hub in Boston for cocktails. I got to meet a ton of really smart people. We socialized and just had a great time. The sheer brainpower was unbelievable.
Saturday’s event was great. All of the sessions I attended were fantastic. I got to hear Andrew Kelly, Aaron Bertrand, and Adam Machanic all speak. These are incredibly smart people who really know their stuff. And getting to hang out in the speaker’s room, was amazing. Just listening to some of these guys and gals talk was… well… I just can’t articulate it.
My session was in the afternoon, and it went well. I was speaking on virtualization of SQL Server. What I had expected was an audience who didn’t have a lot of experience with virtualization. What I found was that I had a group who had virtualized their test environments and were starting to virtualize their production environments. I walked in there pretty calm and then found myself incredibly nervous when I had a more seasoned audience than expected. After I calmed down and slowed down my speaking a bit, I got into it.
Because my session was the success story of how we virtualized SQL Server in my company’s environment, my presentation wasn’t very visual. Now I realize that it needed something more appealing. And I should have been prepared with more technical details of what we did and how the environment looks today.
I found myself wrapping up my presentation 30 minutes into a 60 minute session. What did I to? I took the opportunity to turn it into a roundtable/Q&A session. And you know what? It worked!
Still the feedback was pretty good. And it helped me realize that I know what I’m talking about. I think I want to keep doing this. I know some good stuff, and I want to continue to present it. Right now, I’m putting together my presentation proposals for two sessions for SQL Saturday 79 in South Florida.
And this has certainly cemented that I will be attending PASS in October.
SQL Saturday 71: My Slide Deck
I’m speaking at SQL Saturday today. Here is my slide deck.
SQL Sat 71 Virtualization Success Story
Missing Values in IDENTITY Column
Somebody in the tech support department of our ERP vendor has to be hating me right now. I’m kind of okay with that.
In a recent audit, the auditors wanted to demonstrate the integrity of our ERP system’s audit log and prove it hasn’t been tampered with. Considering the primary key on the table is an IDENTITY column, this should be really simple, right? Nope.
Considering that this is an IDENTITY column, the only way we should have a gap in the sequence is if someone is deleting records or perhaps a deadlock. If someone or something is deleting out of an audit table, I’m going to have a fit. I’m the only person in the company who has both the access to the database back end AND the skills to delete a record from a table.
So how did I find the gaps?
select id
from tablename
where CreateDate BETWEEN @p_startdate and @p_enddate
and id + 1 NOT IN
(select id from tablename
WHERE CreateDate BETWEEN @p_startdate and @p_enddate)
It may not be optimal code, but it served the purpose. With a little more tweaking, I was able to calculate the number of records in the gap, the time of the gap, and even provide a variable number of records before and after the gap. What I found is that it’s mostly the same four processes causing gaps in our audit log and these gaps are rarely more than one second.
Either I’m off my rocker, or somebody has some explaining to do.