Search results matching tags 'SQL Server' and 'Testing'

Attaching a Database

BuckWoody — Tue, 26 Jan 2010 15:03:39 GMT

I had someone ask me yesterday how they could get to a database used by a product that they owned, but that was installed using SQL Server Express. They didn’t have access to the database, and they didn’t know the password for the service that started Express, so they wanted to know if they could look at the data.

There are a few ways to do this, but the quickest, safest and easiest for me is to “attach” the database on another Instance of SQL Server. To understand how this works, let me quickly explain how SQL Server uses databases from a physical perspective.

SQL Server uses two types of files, sometimes lots of each type. The two basic types are Data files and Log files. SQL Server basically writes data to the Log file first, and then on to the Data files. Of course it’s more complicated than that; but this is the basic flow.

When SQL Server first starts up, it reads (among other things) the master system database. And that’s where the crux of this post comes into play. In that database are two things that we care about right now: the logical name of the databases SQL Server knows about and the physical locations for the files that database uses.

With that information in mind, here’s a simple way to transfer a database from an Instance you don’t have access to into one that you do.

There are a LOT of disclaimers here.
First: don’t do this on a database you don’t own, and own all the data for. That’s illegal.
Second, don’t do this with a large database – the possibility of multiple files in the filegroups on a complex database is high, and you might not get them all.
Third: you have to be running the same (or higher) version of SQL Server on the destination server, and the same (or higher) edition of SQL Server on the destination server.
Fourth: you’ll have to stop the source Instance. If you have users on that system, you don’t want to stop it without consulting everyone first.

OK – with all of that stated, you first need to stop the “source” instance. Let it shut down completely and normally. This will (theoretically) commit all of the transactions from the logs to the databases.

Now copy the .MDF and .LDF files for the database you want to a location on your test Instance. If you don’t know what these are, you can try and copy what you think are those files, but don’t copy any of the system databases from the source system to your test Instance!

Start the source Instance back up and let the users back into the application.

On the test system, write-down the location where you copied both of those files (assuming there was one of each – it get’s trickier when there are multiples).

Open SQL Server Management Studio.

Right-click the “Databases” node in Object Explorer and select “Attach…” from the menu that appears.

Click “Add” to find the MDF and LDF files, and then “OK” to name the database.

You have two other options as well. You can use the sp_attachdb stored procedure (detail here - deprecated) or the newer CREATE DATABASE…FOR ATTACH statement (article on that here).

In any case, you now have the database, and you now “own” it in that test Instance. The logins will all be incorrect, since the names in the database security don’t match the new Instance’s master database. But you do have that data.

Spit it out already!

BuckWoody — Wed, 06 Jan 2010 14:11:11 GMT

You’ve probably seen that commercial where the chewing-gum company van stalks the guy who has been chewing the same piece of gum too long, and they attack him and make him chew another piece.

I feel like that with SQL Server 2000. Almost every shop I go into has at least one primary application running on SQL Server 2000. Now, don’t get me wrong – SQL Server 2000 is a fine piece of software engineering. From over TEN YEARS AGO. In “software time”, that’s like a thousand years or something.

While it was great for its day, the newer versions are faster, more secure, and more robust. And every time it doesn’t get upgraded, SQL Server is perceived as “not as fast/strong/etc” as other platforms (which are upgraded, of course).

Now, I’m not suggesting that anyone upgrade for upgrade’s sake. We all have work to do, and the last thing we need to do is change out a platform when there’s no need.

But there is a need. SQL Server 2000 isn’t in mainline support any more. That means it can be attacked easier and so on. And it doesn’t scale like the new offerings, nor does it have any of the new features the latest versions have.

“Oh”, you might say, “I don’t use those features anyway.” Well of course you don’t – you can’t if you still have SQL Server 2000! How do you know the ways you could help your organization if you don’t experiment with the new stuff?

But it isn’t the DBA I would chase down and steal gum from. It’s the vendors.

Every time I raise my eyebrows when I hear about the SQL Server 2000 installs, the DBA shrugs and says “The vendor won’t certify SQL Server X, so we have to stay at SQL Server 2000 or 2005.” And I say, that’s just lazy. Unless the vendor codes specifically for deprecated features, a simple test run during their software development should allow them to move forward. I’m not saying that’s an easy task, but certainly they’ve tested their software releases once in the last ten years, no? If not, doesn’t that make you nervous?

Anyhoo, spit out the SQL Server 2000. Or I might have to fire up the company van.

After the Upgrade, it runs differently…

BuckWoody — Tue, 01 Dec 2009 16:06:55 GMT

I got a question yesterday in the mail that I thought I would just answer here in a broad context. While I can’t troubleshoot or do performance tuning from a distance, there are some interesting concepts and suggestions this e-mail brings up:

“I have recently seen a change from SQL Server from 2005 to 2008 in where it handles CASE statements differently. Previously we saw a tremendous improvement in performance by using CASE statements instead of OR statements. However when one of our client upgraded to 2008 they began to notice unusually long runtimes with a few of these queries (orders of magnitude larger runtimes). Swapping it to an OR statement allows it to run in 0.075 seconds... so my question to you is do you know of any changes to the exectution engine that would account for this and what is your recommendation?”

It’s a great question. Basically it boils down to “I changed versions, now something acts differently.” Before we talk about what might be the issue, let’s talk about some things you should do after you upgrade from one version of SQL Server to another.

First, open the database properties and change the compatibility level to 10.0, unless you know you shouldn’t. Next, update your statistics – all of them. Third, ensure you have all of the proper service packs applied to the operating system and SQL Server. And finally, check the code you have for deprecated statements, or for places where you could optimize the code to use new statements or formats. There are other steps to follow, but these basics will help.

Now, with all of that done, let’s move on to things that work differently. First, you need to find out what the code is doing – and the primary way to do that is to examine the Query Execution Plan. There are a lot of resources to teach you how to do that, but the general idea is that you turn that plan on (in the Query menu), run the query, look at the graphical plan and check three items: the overall plan, the icons that show the highest percentage of use, and the thickest arrows. Evaluating this on the “before” system and the “after” system, and that will show you what changed.

Maybe.

The point is, the query might take exactly the same path, but a different component may show stress because you might have a different box or configuration. Perhaps the drive layouts changed (or should), you have more memory (or better access to it) and so on. In that case, you simply follow standard performance tuning methodologies to locate what’s waiting, and what is showing pressure.

Now to the question at hand – does SQL Server (any version) handle a CASE statement differently than an OR statement? Well, once again, the execution plan will show you that answer, but the CASE statement is used for a different purpose than an OR statement – without having all of the code it’s difficult to say which to use in a given situation. The best thing to do is to evaluate the documentation on each and decide which fits the situation best.

CASE: http://msdn.microsoft.com/en-us/library/ms181765.aspx

OR: http://msdn.microsoft.com/en-us/library/ms188361.aspx

Did we make changes to CASE or OR? Not directly – but each change to the product may have orthogonal implications, which is where I point you back to the steps I mentioned for the “after upgrade” process.