Category: SQL

Database snapshots

At work, we do our Dynamics AX development on VMs, set up with a full install of AX (SQL Server, AOS, and AX client). Prior to our R3 upgrade, we were using local VMs, under VMWare Workstation. This worked out quite well. One of my favorite things about this setup was the ease with which I could take VM snapshots, allowing me to run destructive tests, then roll back, fix bugs, and rerun the tests without having to jump through hoops to reset my environment or set up new test orders, or whatever. It was all pretty clean and easy.

But, after we upgraded to R3, we set up new VMs on vSphere. There are a number of advantages to this, but one disadvantage is that I don’t have rights on vSphere to snapshot my own VM. (I’m sure I could ask an admin to snapshot my VM, but the typical testing cycle of snapshotting, rolling back, fixing code, snapshotting again, etc., would probably annoy the admins.) So I’ve been looking for an alternative way to manage testing destructive processes.

I’ve settled on using SQL database snapshots. AX 2012 R3 stores all data in one database, and all code in a separate model database. (Versions prior to R2 mixed code and data in one database.) I’ve worked out a process by which I can pretty quickly take a snapshot, run my tests, delete the snapshot, and start again.

Given a database called DAX12_PROD, here’s a quick run-down on how to execute this process.

(1) Stop the AOS server.

(2) Create a snapshot:

CREATE DATABASE DAX12_PROD_SS1 ON
 ( NAME = DAX12_PROD, FILENAME = 'E:\your_sql_data_folder\DAX12_PROD_SS1.ss'
 ) AS SNAPSHOT OF DAX12_PROD

(3) Start the AOS & run your tests.

(4) Stop the AOS.

(5) Restore from the snapshot.

ALTER DATABASE DAX12_PROD
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE DAX12_PROD
 FROM DATABASE_SNAPSHOT = 'DAX12_PROD_SS1'

(6) If you’re done, then drop the snapshot.

DROP DATABASE DAX12_PROD_SS1

(6) Start the AOS.

So that doesn’t take too much effort and is pretty quick to run. The snapshot file is a sparse file, created with the same size as the actual database file, but not actually taking that much space on disk. So you don’t need to have a ridiculous amount of free space on your VM (as long as your test isn’t changing a ridiculous amount of data).

And yes, I know that it would be so much better if I could just run unit tests that don’t touch actual data, but it’s nearly impossible to do that for a lot of the stuff I have to do in AX. There are some interesting things you can do, in certain scenarios, like getting creative with setTmp, but that’s too simplistic for a lot of the testing I need to do.

Classic ASP – SQL Injection

We still have a few old sites at work that are in classic ASP. One of the problems that tends to occur on these old sites is SQL injection attacks. A lot of old ASP code was written without taking SQL injection into account. It would be great if we could just rewrite all of these sites in ASP.NET, but sometimes the client isn’t interested in doing that.

Well, yesterday, we had a production ASP site get hit. This is a site for an old client that I’ve personally never worked on, so I really knew nothing about it, but I’m getting dragged in, now that we need to clean it up. Looking at the site, I’m actually surprised this hadn’t happened earlier.

Looking at the code, I see a few places where we could be doing a better job of input validation. And also a few places where we’re doing un-parameterized SQL, which is a big no-no if you want to avoid SQL injection. So, I’m going to try to clean some of that up.

I also want to use URLScan on the server, with some SQL injection rules, to try to get some of this stuff caught at the IIS level. I found this article on how to add some rules to the URLScan.ini file to mitigate SQL injection attacks. (I actually first started reading this article, but then remembered that this particular web server is still on IIS 6.)

When I started poking around on the server, I was surprised to see tha URLScan was already installed. However, it was not configured to do any SQL injection prevention. So, Monday morning, I’m going to try to add the SQL injection rules to the ini file, and see if that breaks anything. Then, I hope to have time to tighten up the code a bit and roll out a new version. I can’t say I’m excited to be working on nearly decade-old VBScript, but hey, it’ll be good for a few laughs, right?

found it

Following up on my post from a couple of days back, I found my invalid nulls today. There were null values where there shouldn’t have been on 20 records in a table of 100,000+. I fixed them. Tomorrow, I hope to zero in on whatever program bug is causing them to get in there in the first place.
A couple of quick observations:
First, SQL Profiler is my friend. There wasn’t really any way I could trace what the VB6 program was doing, and it wasn’t putting out enough error info for me to figure it out, but by following a SQL trace, I eventually isolated the problem records.
Second, I discovered Query ExPlus today, which turns out to be a pretty good tool if you need quick access to a SQL query tool on a machine where you can’t actually install the full SQL Management Studio.

fun with stored procedures

I had some fun today optimizing a stored procedure. It was taking about 10 minutes to run, and I got it down to running in two seconds with a pretty minor change.

Without getting into too many gory details, it was basically doing a somewhat complicated PIVOT. It was using a SUM() call to generate a value that it was really just ignoring, just checking to see if it was null or not. I replaced the SUM() with a COUNT(), and checked for zero instead of null, and bam, 10 minutes down to 2 seconds.

This is the kind of stuff I really like doing — figuring out how to make a minor change in a bit of code that doesn’t affect the output, but results in a measurable improvement in performance. The only thing that’s still a little frustrating about this is that I don’t completely understand why the difference is so great. I have a general idea of why this worked, but something weird must be going on internally with the PIVOT option in MS SQL for this to have made such a huge difference.

stored procedures

I had to debug a problem with a third-party e-commerce system today at work. It took a while to isolate the problem, and when I did, it led me to a certain stored procedure, which turned out to be 7400 lines long. I don’t think I’d ever seen a single stored proc quite that big before. Is that normal? I figured out what it was doing by running SQL Profiler, with the “TSQL_SPs” template that shows all the statements being executed within the procedure. With all the if/then logic in the proc, it was really only executing about 1000 lines of SQL for any given run. Still, that’s a lot to comb through just to figure out why the logic was returning wholesale prices instead of retail.

I also found a number of lengthy queries in the proc that ended with “and 1=2”, which would of course prevent that query from returning any results. I’m wondering why someone would do that rather than comment out the query. It made it fairly difficult to find the queries that were actually executing and returning results.

more SQL silliness

Well, after thinking I had my SQL problem licked, it came back. I finally got a callback from a support guy at Microsoft, and he had me update statistics on all the tables involved. That fixed things, and everything has been working now for the last couple of days. I’m pretty sure I’d updated statistics on all these tables at least once since the original problem occurred, but maybe I didn’t, or maybe I just needed to do it two or three times to get everything back to normal.

I’ve always had a maintenance plan that updates statistics on all my tables once a week, and I don’t think I’ve ever had problems with statistics before.

I’ve been reading up on this whole statistics thing now, which I admit I hadn’t paid much attention to, until this little debacle. Here’s a blog entry by Kim Tripp that explains a few things about the auto-update mechanism, and another one by someone at Microsoft that explains a good bit about the way statistics work in SQL 2005.

SQL patching fun

After all the patching I did over the weekend, it seemed like everything was fine on my main SQL server. However, on Monday, we discovered that a couple of oddball SELECT statements weren’t working anymore, returning the error “Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.”

To make a very long story short, I eventually figured out that I could get these queries working again by deleting all of last year’s data from one of our bigger tables. The table in question had about 32 million rows in it. I cut it down to 13 million after getting rid of the 2007 data. The moral of this story, apparently, is that you shouldn’t try to store 30 million rows in a single SQL Server table. I say this with tongue in cheek, since, obviously, there are other people out there with very large tables in SQL Server. Apparently, I can’t be one of them, though.

I put a call in the Microsoft support this morning, but they never called me back. Maybe tomorrow. If they do call back, and I get a support guy who seems to be a bit less clueless than usual, maybe I’ll try to see if we can figure out why my server chokes on this table. If I get the usual clueless support dude, I guess I’ll just tell him I figured it out on my own while I was waiting for a callback. I wonder if I get my support incident credited back if I solve my own problem while I’m waiting for the callback?

reference vs. value types, and LINQ

One of the people I work with was having some trouble with reference vs. value types (in C#) this week. This article seems to give a pretty clear explanation of how these things work. Always good to have a little refresher on the fundamentals.

And, in poking around on this guy’s web site, I found LINQPad, a tool for executing LINQ queries. I have to admit that I haven’t had any time to play around with LINQ at all, but I’m really curious about it. I like his idea of trying to do all your ad-hoc SQL queries in LINQ for a week, to force yourself to start getting used to it. Some of the stuff I’m doing this week is requiring me to do a *lot* of ad-hoc SQL, though. My brain is so completely wired for T-SQL at this point that I think I’d get really frustrated really quickly trying to use anything else.

VS.NET 2005

I just got done installing VS.NET 2005 on my new desktop machine at home. I’m still working my way through ASP.NET 2.0: A Developer’s Notebook. I’m just starting the Data Access chapter. The limited version of SQL Server that comes with VS.NET does not have the pubs or Northwind databases installed by default. If you want to install them, you can find setup scripts for them under “C:Program FilesMicrosoft Visual Studio 8SDKv2.0SamplesSetup”, named InstPubs.sql and InstNwnd.sql. To run the scripts, do this:

sqlcmd -S .SQLEXPRESS -i InstPubs.sql
sqlcmd -S .SQLEXPRESS -i InstNwnd.sql

That seemed to be worth writing down, just in case I need to do it again…