Shaun J Stuart

My co-worker showed me a SQL Server requirements document that he found online somewhere and modified at a previous company. He would give this document to vendors who said they needed a SQL Server and they would need to select the appropriate answers before he would let them install their product.  I think this is a great idea and thought I'd share it.

SQL Server Host Requirements (OS, minimum RAM, Number of CPUs, VM support, etc.)

Version of SQL Server Supported

• SQL 2012 SP2
• SQL 2012 SP1
• SQL 2012 RTM
• SQL 2008 R2 SP2
• SQL 2008 R2 SP1

Edition Of SQL Server Supported

• Enterprise
• Standard

32-bit vs. 64-bit

• 32-bit
• 64-bit

Exclusivity For The Hosted Database

• Shared SQL Server Environment
• Dedicated SQL Server required

Maximum Concurrent User Count

IOPS (I/O per second) Requirement

Initial Database Size - Please provide for each database, if multiple. Specify in MB or GB.

Projected Annual Database Growth Rate. Specify in MB or GB.

Special SAN Requirements, if any.

Dedicated Test, Development, and / or Training Environment Required (Include Details)

• Auxiliary environments to be delete / removed post-go live
• Auxiliary environments to remain accessible for duration of project use
• Auxiliary environments will need to be recreated for future product upgrades / enhancements

Database Security

• Single Windows (Active Directory) network login (connection pooling)
• Active Directory gorup login
• Individual or AD Group network login for each user
• Single SQL login ID and password (Login and password must be customizable and not identical to other product installations at other clients)
• Individual SQL login and password for each user

Maximum Level Of Permissions Required for Users / Administators / Service Account (Please provide explanation for any response greater than Database role, db_owner)

• Database role, less than db_owner
• Database role, db_owner
• Aliased as dbo for database
• Some level of server-level permissions less than system administrator (sa)
• SA role membership
• Local administrator rights on SQL Server host machine

Database Connection / Session Handling

• Application maintains persisted connections / sessions to database
• Application established and releases connections / sessions as requested during normal operation
• Other

Vendor Access Required To Support Database Issues

• Vendor requires local login rights to SQL Server
• Vendor requires remote-connection via the application
• Vendor requires VPN access to our network to connect to the database via SSMS
• No remote support capabilities. Database backup will need to be sent via secure FTP to vendor for analysis
• No remote support capabilities. Secure method of transferring database backup not available.

Application and Database Support Exists for the Following High Availability Architectures

• Failover clustering
• Mirroring
• Log shipping
• Replication

How Frequently Do You Certify SQL Server Patches, Service Packs, and Release Updates?

• As released by Microsoft
• Quarterly
• Annually
• Other

How Frequently Do You Issue Database Updates?

• Quarterly
• Annually
• Other

Performance Tuning Requirements

• Index tuning changes (adding or disabling indexes, etc.) must be submitted to vendor for approval
• Index tuning changes (adding or disabling indexes, etc.) do not require vendor approval
• Index changes by customer technical staff are not permitted

Data Retention / Archiving and Growth Management Support

• Vendor does not support database archiving
• Vendor has database archiving process in place or available
• Database archiving solutions created by customer must be submitted to vendor for approval
• Database archiving solutions created by customer do not require vendor approval
• Vendor has tested and supports SQL Server data compression

Database Installation Process

• TSQL scripts or database backup file to be submitted to customer DBA for installation
• Installation accomplished via executable or other method run by vendor / customer on non-SQL Server host (e.g., application server or client workstation). Any additional permission requirements needed for installation should be noted here.
• Other

Note any Non-Standard Collation, Server-Level, or Instance-Level Settings Required

Identify Tables with Sensitive Data (Personally Identifiable Information, Financial Data, Passwords, etc.)

Identify Any Custom SQL Agent Jobs, Proprietary Backup or Maintenance Processes, or Similar Items.

Vendor Technical Contact ____________________________
Vendor Technical Contact Email _______________________
Vendor Technical Contact Phone _______________________

I would be surprised if many vendors knew the answer to all these questions right off the bat. However, this questionnaire will at least bring these issues to the forefront where they can start being addressed in a pro-active manner, before the product is installed. It will also raise any red flags. (The application needs sa access on a shared SQL Server? I don't think so! Your maintenance plan shrinks the database weekly? I don't think so!)

 

Pop Quiz, hotshot. A vendor installed a maintenance plan on one of your SQL Servers. It looks like this:

What do you do?

 

An article on Yahoo Education recently ranked Database Administrator as the second fastest growing career through 2020, with a projected job growth of 31%. The piece lists a couple of reasons for this, most of which I agree with. Then I read the last line of the article: "Companies with large databases may prefer those with an MBA."

Database Administrators need an MBA to work with large databases?

My phone rag at 4 AM last Sunday morning, which is never a good sign. It was a member of our IT team who was doing an upgrade to some software. He was concerned because a particular SQL statement had been running for 30 minutes so far and when this person performed the upgrade on the test system earlier in the week, this particular statement completed in a few minutes. Because this was a mission-critical system, he wanted me to take a look and see if I could tell why things were taking so long and to be sure everything was still ok.

My first step was to fire up SSMS and launch activity monitor. Sure enough, I saw the query being executed. There was no blocking going on, nor were there excessive waits. Occasionally, I would see an I/O wait, but they were on the order of 20 milliseconds and were somewhat rare. It looked to me like SQL was just chugging along.

My next step was to get the actual query being run. It was really, really basic:

DELETE  FROM amc_rd_ref_data_item
WHERE   reference_data_group_id = @rdg_id

I looked at the table, thinking about possible indexing issues, or maybe triggers. This is what I saw:

No triggers. The reference_data_group_id was column was the second field in the primary key, so that index couldn't be used. But each of the other two indexes had this as the first column, so the database engine could have used either one of them for this query. There were also no constraints. There were three foreign keys defined on this table, but the tables they linked too had a small number of rows. No cursors were involved. Data types were matched, so there were no implicit conversion issues. The table only had about 500,000 rows in it. In short, nothing was jumping out at me as to why this simple query should take so long to run.

I decided to look at the execution plan for the query. Bingo!

Wow. That's zoomed out so you can see the whole plan, but each of those vertical lines is a nested loop operator. It turns out there were more than 200 tables that referenced the table we were deleting from with foreign keys of their own.

SQL was working through this without issue, so I decided to just let the query continue. I made sure transaction log backups were being taken frequently and that the log drive had enough room for it to grow - I didn't want this thing to bomb out for any reason and have to start over. The query ended up taking just over an hour to complete and when it was done, the table was down to 9,000 rows, so there was a lot of deleting and looping going on.

If you have what looks to be a very simple query that seems to be taking an inordinately long time to run, in addition to checking for blocking, proper indexing, triggers, and the usual suspects, keep in mind foreign keys on other tables. View the execution plan to find out exactly what SQL Server is doing.

 

Sorry for the lack of posts lately. I took a 2 week cruise to Hawaii for my tenth wedding anniversary and am currently trying to catch up with everything that piled up while I was away. During the cruise, I also got a kidney stone and had to go to the hospital in Kauai for that, where they discovered not one, not two, not three, but a total of four stones in my right kidney. I'll be having surgery next week to get those removed, so I expect content here might be a bit slow in coming for a while.

But while going through my email, I did find out that SQL Saturday is returning to the Phoenix area. On Saturday, April 26th, it is returning to the Chandler-Gilbert Community College. This is really close to my home, so hopefully, I'll be able to make it. Here are the details, as provided by the organizers:

SQLSaturday#193 is a free one day training event for SQL Server professionals and those interested in SQL Server. The event will be held Apr 27, 2013 at Chandler-Gilbert Community College Pecos Campus, 2626 East Pecos Road, Chandler, AZ, 85225. Registration. We have another jammed packed schedule with 50+ sessions with great speakers!  Check out the schedule at schedule.  For more information please visit SQLSaturday.com or email us at sqlsaturday193@sqlsaturday.com.

Until then, let me share one of my favorite vacation photos with you: