Free Lunch – Without Lunch
The old adage is that there is no free lunch. Every once in a while, a free event comes up that’s a great use of time at no cost. Today was one such example.
My friend Brent Ozar presented a virtual training class sponsored by Quest Software. Today’s topic was “Performance Tuning & Troubleshooting with DMVs” and I found it pretty fantastic.
If you keep your eyes open, you can get some really great free training out there. They’re not always sit down training in a proper classroom. Sometimes they’re seminars or virtual events. My current boss seems to think that if it’s not led by an instructor in a classroom, it’s not worthwhile. I will say that I get more out of talking to and listening to other DBAs, learning from their experience.
Sure, these events are usually paid for by a sponsor, and you have to listen to the sales pitch. It’s well worth it. My only complaint is that Quest didn’t provide lunch today. They should have provided the bacon. Since I was sitting in my own office, I guess I can handle buying my own lunch.
Looking for a Security Solution
We have some pretty strict security policies in my company. That often means coming up with creative solutions to problems. Hopefully someone in the DBA community can help me.
We have a company that does some outside work for us. We have a database server at their location. We need to allow the database server to talk to our DB server in a network DMZ. We’d use a linked server from the remote site to talk to our DMZ server. I’m okay setting up the username/password and the firewall ports. The problem is that we need to have the traffic between the two servers encrypted.
To further complicate this, we don’t want all of the traffic on these servers encrypted, just the traffic that goes across the internet.
I’m completely befuddled on how to do this properly. There is a very good chance that I’m making this harder than it needs to be, and I’d be thrilled if someone gave me a simple solution.
Upgraded
I’ve just upgraded all three of my sites (this one, my personal site, and my Pug site) to the latest version of WordPress. Keeping current on your software is one more way to prevent getting hacked.
My Updated SQL Server 2005 Install Checklist
If you’ve been following me lately (either my blog or Twitter), you know that I’m in the process of building up some new servers on a new SAN for a big database consolidation.
After the first server, I had a pretty good checklist. Brent Ozar has a pretty fantastic checklist that I stole a few items from. This is my attempt at a more refined checklist. (of my own, not Brent’s)
In my new environment, each server is a VM. The physical box has 16 GB of RAM and the VM will get 8 GB. I have drives for the OS, SQL logs, SQL data, TempDB, and backups.
Here is my order of things:
- Install Windows 2003 (64 bit)
- Install Windows 2003 SP2
- Install our antivirus software
- Install SQL 2005
- Install SQL 2005 SP3
- Set permissions to permit Instant File Intialization
- Set permissions to permit locking pages in memory
- Install System Center Operations Manager Agent
- Install Quest LiteSpeed
- Attach to SAN/Add VMWare virtual disks
- Create Directory Structure
- SQL Logs
- User
- System
- Error
- SQL Data
- User
- System
- SQL Logs
- Move Master DB
- Move TempDB
- Move Model DB
- Move MSDB DB
- Resize TempDB (32 GB data, 16 GB logs), disable autogrowth
- Set default file locations
- Set CPU to boost SQL priority
- Set minimum memory to 6GB
- Set maximum memory to 7GB.
- Configure Database Mail
Disabling autogrowth on TempDB may be a little controversial. I’m okay with that. If TempDB is growing that much on our servers, which aren’t that big, I want to know what the heck is going on.
Okay, fellow DBAs, what did I forget, and what did I do wrong here? I think I did some good stuff here, but I doubt it’s perfect.
Moving System Files
Having built one database server in my new environment, the second one is an awful lot easier.
The following six lines of code saved me a ton of time this morning.
ALTER DATABASE [tempdb] MODIFY FILE (name = tempdev, FILENAME = ‘D:\TempDB\tempdb.mdf’)
ALTER DATABASE [tempdb] MODIFY FILE (name = templog, FILENAME = ‘D:\TempDB\templog.ldf’)
ALTER DATABASE [msdb] MODIFY FILE ( NAME = msdbdata , FILENAME = ‘G:\system\MSDBData.mdf’ )
ALTER DATABASE [msdb] MODIFY FILE ( NAME = msdblog , FILENAME = ‘e:\system\MSDBLog.ldf’ )
ALTER DATABASE [model] MODIFY FILE ( NAME = modeldev , FILENAME = ‘G:\system\model.mdf’ )
ALTER DATABASE [model] MODIFY FILE ( NAME = modellog , FILENAME = ‘e:\system\modellog.ldf’ )
I just can’t explain how big of a deal this is to me. In my current envrionment, every server was set up with a single C: drive. Some of these file systems are over a terabyte. Now we’re breaking things up, and I can only imagine the performance boost we’re going to see.
Server Builds
I’m building a new server. Actually, I’m building two. We just bought a new pair of SANS (one production, one DR) and are doing a consolidation project with our databases.
Each DB server will live on its own VMWare host. We’re virtualizing so that we can utilize VMWare’s clustering. It also gives us the flexibility to move things around. My two database servers will have identical configurations. One will server internal applications, and the other will host customer-facing applications.
Hardware wise, I’m using IBM HS22 blades with 32 GB of RAM connected to an EMC Clariion CX4-120SAN. Each server has five datastores, each corresponding a LUN on the SAN. Each virtual drive will live in its own storage group. I have a drive for:
- OS – Aps
- TempDB
- Log (LDF) Files
- Backups
- Data (MDF/NDF) files
My preference would have been to have the virtual machines use an iSCSI initiator to talk to the SAN, but I got overruled on that one. Still, this is a better configuration than I had before. (We had no SAN at all.)
Everything is 64 bit SQL 2005. Again, I wanted to use SQL 2008, but that’s the next project.
After installing Windows and SQL, I started putting together a checklist of items to do next. Brent Ozar has a great checklist and has been a great reference. Here is my list:
- System Center Operations Manager Agent
- Antivirus protection
- Quest’s LiteSpeed
- Service Packs, Service Packs, Service Packs.
- Set default file locations
- Move master DB
- Move TempDB
- Resize TempDB
- Move Model
- Move MSDB
- Load Testing
Why Database Names Matter
There is a reason users shouldn’t be allowed to name their own databases. Actually, there are probably several thousand reasons. I have a new one for you–It makes my life more difficult.
Lets say you work for a company called Acme. And lets say your manufacturing people created an in-house Microsoft Access application with a SQL database called AcmeDatabase. And lets say that your support organization created a database on a different server called Acme that’s part of their CRM system. On top of that, the ERP system (yet again on a different server) prefixes all four of its databases with Acme_. As the DBA, you’d never permit this to happen unless you inherit this mess.
Welcome to my world and the mess I inherited. I have my company name (or some form of it) in countless databases. Lets make this just a little more interesting, though. We’re going to be consolidating database servers in the near future and potentially putting all of these databases on the same server.
These are databases that could potentially end up living on the same server:
- AcmeDatabase
- Acme
- Acme_App
- Acme_Data
- Acme_Forms
Which one s are ERP, CRM, or Manufacturing?
When we consolidate database servers, we’re also going to be renaming some of these databases. My application owners won’t be too happy about this. They’re going to have to get over that. I inherited this mess, and I’m going to make it right.
Rookie Mistakes: Named Instance
I hate rookie mistakes. I hate them even more when I’m the one making them.
My coworker was installing Microsoft OCS Server, and he said that he needed a SQL instance, and he told me what the instance needed to be named. Since we already had a SQL server dedicated to this project, I created a named instance on said server.
In retrospect, I should have looked a little more closely instead of just assuming I needed to create a named instance. After I looked at the screen that asked for the SQL instance, I knew that we could use the default instance and not have to go down the named instance path.
Fortunately, we’re still in the test phase with virtual servers and the servers we’re using will be replaced with physical machines. That means I’ll be able to go back and get it right the next time around.
That’s why we have test environments, right? I’d much rather make a rookie mistake in a test environment than in production.
Rookie Mistakes: Installing Everything
Prior to my coming to my current company, somebody had a great love of installing all SQL services on every server where SQL was installed.
This is such a pet peeve of mine, and frankly, it’s a rookie mistake. In my environment, almost all of our applications are off-the-shelf apps with minimal customization. Today I was looking at a web-based app that is an intranet type of collaboration application. The server had Reporting Services, Integration Services, Analysis Services, and Notification Services installed. This particular application barely makes use of the database services and is database agnostic. We could run this thing on Oracle or MySQL. It’s not going to need Reporting Services, and it certainly won’t need Analysis Services.
Extra services mean extra CPU and memory utilization. While it’s not much, these little things add up. And in case of the rare bit of malware that hits SQL server, only software that’s installed can be exploited.
In a perfect world, we would have an understanding of what our servers will be doing before installing the database server. Then we would install only what’s needed. Better yet, in my environment, the vendor’s installation instructions would specify what services to install. If SSRS, SSAS, SSIS, or SSNS aren’t mentioned, there probably is no need for them.
My boss might say “We might need them some day.” He’s right. We might need them some day. And when that day comes, we can install them. Until then, we don’t.
Congrats to the Winner
As Brent Ozar mentioned in his blog post, the winner of the all-expense paid trip to PASS is Jen McCown, aka @MidnightDBA. I’m really happy for her. Don’t get me wrong, I’m jealous as hell. Jen wanted this as much as I did, perhaps more. She produced a ton of amazing content, and the community is better off for the work she did.
I have a lot of good stuff coming up professionally. We’re planning on consolidating our database servers, and this should give me a ton of good stuff to write about. I’m really enjoying my time with SQLServerpedia.com and look forward to having the opportunity to contribute.