Pythian Group

All over the web I’d read that DTS packages could be stored on SQL Server 2005 64-bit, but not executed on this server. Workarounds I’ve seen range from creating SSIS packages with Execute DTS tasks, migrations to SSIS using the wizard or third party tools, and running the DTS Packages from a 32-bit server against the 64-bit target.

Recently (and much to my embarrassment after making that statement), a colleague demonstrated that this is not correct. DTSRun.exe can be found on SQL Server 2005 64-bit installations (although it might not be supported by Microsoft).

Upon investigating and testing on my own, I found that on the servers I checked, DTSrun.exe is located in: Read the rest of this entry . . .

This is the first post in a series dedicated to exploring the backup and availability options in SQL Server 2005 and 2008. It is aimed at anyone unfamiliar with the database backup options in SQL Server 2005 and 2008. I’m not going to explore every single option or scenario, the goal is to give you the language and the tools to do deep dives where you need to.

SQL Server 2005 has several DBA-job-saving options available to the would-be administrator. Think of a Database Backup as the technology to save data and Database and Availability as the technology to keep it online and available to it’s consumers.

A very brief introduction to SQL Server databases

Its important to have a few SQL Server database basics in order to understand the backup options. If you know what a recovery model is, and the difference between an .ldf and .mdf file, you can skip this section. If this is as good as a foreign language to you, read on.

Read the rest of this entry . . .

Using OLE DB to get SQL Server to connect to Oracle servers can be done quite easily, but there are a few little tricks you should know to make it go smoothly. Once it’s working it seems to work quite well. I hope this blog post will save you a few headaches.

Recently a client asked me to create a simple SSIS package that would connect to Oracle, pick up some data with queries they provided, import it to SQL Server, and eventually export the data as flat, delimited text files.

With SSIS you can use the OLE DB provider that Oracle provides. If your SQL Server is 32-bit, you can install the 32-bit Oracle client and stop there.

If it’s 64-bit, there are a couple different ways to get the Oracle providers working. Read the rest of this entry . . .

Let’s say for you want to move a database from one environment (possibly production) to another (possibly development) on a semi-regular basis. After about the 10^th time doing it manually, you’re ready to automate the process. But how?

That’s what I intend to help with in this post. For this example, I’m running two Virtual servers, both running Windows Server 2003 and SQL Server 2005 SP2. We’ll use the AdventureWorks OLTP database, available for download from http://www.codeplex.com/SqlServerSamples. I’m running SQL Server and SQL Agents with local users that have permissions on both machines. This should work for SQL Server 2005 or 2008, and the Windows version level isn’t important.

There are a couple ways you could go about this task. One way would be to write stored procedures and call them with a batch file using osql.exe and SQL Agent job or your enterprise scheduler. There’s nothing wrong with that approach, but I think that for this task, SSIS packages give more options, flexibility, and simplicity for ongoing management.

At a high level, the SSIS package we are about to create will do the following:
Read the rest of this entry . . .

Have you ever used Perfmon to collect performance statistics about a particular Windows Server? Do you hate manually picking the counters out of the list? Do you trace basically the same counters every time?

If you answered “yes” to any of these questions, here’s a tip that can save you some time. Once you’ve created and saved a trace, you can save/export it as an HTML document. When you need to trace a new server, with a few quick Find/Replaces from your favorite text editor, you can be up and tracing in minutes on the new host, without using the interface to pick and choose each of your counter objects.

Here’s how to do create a Perfmon trace template:

1. If you already have a saved trace somewhere, open perfmon on that server, expand Performance Logs and Alerts and click on Counter Logs. If you don’t already have a saved trace, you’ll have to create one before getting started.
2. From the Performance Logs and Alerts screen, right-click on your trace and click on Save Settings As…
3. Save the file somewhere and give it a meaningful name. (The default location is My Documents.)
4. Copy the file somewhere onto your machine for later use.

Congratulations. You’ve created and saved a template. It’s that easy. Now let’s say you’re ready to use it somewhere else.

Read the rest of this entry . . .

Server virtualization is becoming a very hot topic out in the wild, and for good reason. This is a fantastic technology to bring to the x86 platform. When used right, it will solve a lot of problems and potentially reduce Total Cost of Ownership (TCO), while allowing many data centers to become greener by reducing power consumption, cooling requirements and waste (fewer servers = less hardware to dispose of later on). Not every server, however, is a candidate to be “virtualized.” When used in the wrong situation, virtualization can be a support nightmare.

I’m going to write a small series of posts on this topic because its something that will affect a lot of us even if you aren’t a DBA. I’m starting at the very beginning, so if you’ve already created virtual templates for all of your core applications, this post won’t have anything new for you. If you’re wondering what about “virtual” is virtual, this is the post for you to start at. Due to the number of different technologies and platforms and and lack of time on my part, I’m going to stick to Microsoft technologies (Microsoft Virtual Server 2005) and write from the perspective of an MS SQL Server DBA. Let’s get started.

What is server virtualization? Read the rest of this entry . . .