Archive for April, 2010

Allowing effective developer access to SQL Server

When creating a new application, after going through the entire business analysis & requirements gathering process, normally you wind up with a datamodel that includes many tables and relationships.  By this time, depending on the size of the datamodel/system there has been considerable amounts of time invested on all sides.  We need a way of preserving this investment of time while still allowing developers to do their thing!


Most shops have policies in place for what level of access developers can have in each environment.  In many places I’ve seen, developers are allowed DBO access in development, and some lesser access in the higher environments (read only usually).

After you’ve deployed the datamodel to the physical database in a development environment, before you grant the developer group dbo access consider all of the time/effort that has been spent making the datamodel what it is.  In order to allow the developers to do their jobs but not allow them to modify the actual table/schema layout you can grant a combinations of privileges.

Grant Alter Schema on the schemas where the developers will need to modify database objects (for instance stored procedures and functions)

Grant db_datareader –to allow read access

Grant db_datawriter –to allow write access

Grant Create Procedure, Function, Default, Etc  — Allow developers to do whatever you are comfortable with

Deny Create Table in the database –This restricts all Table based DDL

Optional** Deny Create View, Function, Default,  in the database — Restrict any create/alter permissions as needed. 

Important** Alter Schema permissions will allow Alter of ANY object type in the schema that you havent explicitly used a Deny on

Principle of least privilege

This method has proven effective to allow developers to write Stored procs, Functions & Views while still keeping the actual datamodel (tables and relationships usually) in pristine shape.   You could also mix and match your own grants/denys on certain object types to allow for unlimited configuration without granting the almighty DBO.  Yes, you might say that I’m a paranoid DBA who restricts permissions even in DEV!  Of course my great developers would never change a modeled database thereby forcing my hand into figuring out this lockdown of privileges

PASS Program Update #3

Its been a while since I wrote an update about whats been happening in the PASS Program Committee.  I just havent had time to write about it with all of the work thats going on in addition to my regular day job.  Hopefully Ill have time now to do a better job at this!

The annual content survey was sent out and the results are in, I’d like to go on record now and say, Im not a BI user/admin/developer.  We took the BI questions from last year’s survey (which were obviously from 2008).  Unfortunately, while going through them and updating the questions I didnt reach out to a BI person and get a gut check for the BI questions.  So we wound up with some out of date info in that section.  I swear we like BI @ PASS, I just goofed, there’s not some secret conspiracy, and YES to the 1 of you who asked, I do read all of the comments .  The good news, for those that asked, the survey results will be released as soon as we can get them collated and readable (any day now)  **UPDATE** The survey responses are here there are definitely some very interesting tidbits to be mined from this.

We are making progress in working on several projects, from redoing the speaker resources, to developing a new system to house the speaker evaluation data.  As with all things volunteer driven, these tasks are taking time but thats not unexpected.It has to be better than this

The biggest project Ive been spending my time on is the call for speakers.  The call for speakers (and resulting abstract review site) is always a huge undertaking.  This year it seems to be even more magnified since we’re undertaking a new vendor (the same 1 that does tech-ed).  There have been quite a few bumps in the road along the way (I wont bore you to tears with all the details) A steady diet of 1-2 conference calls a week and about 50-100 emails a week and we’re closing in on a useable product in the call for speakers site.  The abstract review site, well that will be the subject of a whole other blog post in the future!  Ill just say that right now Im hoping to find some spare pixie dust or at least a few extra rolls of duct tape and bailing wire prior to the close of the call for abstracts

There have been many discussions about changing some of the SOP in the program committee, I have blogged about some of those previously so I wont rehash those here again.  Ill just add a few more ideas Ive been kicking around.

1 of the largest things that will effect the average attendee at the Summit is that we’re exploring ways to allow 2 new session types this year.

1) Community selection – The current thought is to allow the community to choose from (pre filtered) submitted abstracts to choose a session per track (or some similar method/amt)

2) Best of the Summit– The current thought is to take the top session(s) from the first 2 days of the summit and repeat them on day #3

Both of these ideas have execution issues to overcome but, I think they should be doable for the 2010 summit.

Runaway System Cache Increase Kills SQL

Ran into this a while back, and we finally found a root cause so, I thought Id put it out here in hopes that it saves at least 1 person the amount of head bashing I had with it


Windows 2003 Enterprise R2 SP2 w/32GB RAM

SQL Server 2005 standard ed SP3 64bit active/passive cluster

We started seeing this glorious message in the SQL Server Error log.

A significant part of sql server process memory has been paged out. This may result in performance degradation. Duration XX seconds. Working set (KB) XXX, committed (KB) XXX, memory utilization 0%

The message varied slightly but the essence was always the same.

This error message can be too common on systems where SQL memory is misconfigured or where something is unduly pressuring SQL for memory.  In this case a quick verification of the settings showed that everything was in order.  The first 2 times this happened it was the middle of the night during backups(in the SLA window), so no one really noticed a performance degradation.  We didn’t think much of it at the time but in hindsight, we should have.

The Failure

Monday morning 8 AM, developer makes a bad update to the database, No problem I say, Litespeed can rollback the transaction, So I start to copy the full db backup+tran logs off the server (~25gb) this is the way we process litespeed recoveries through the log reader.  About 3 minutes later the server became totally unresponsive, and the error about paging the SQL process memory was logged.  At the time I didn’t put 2 and 2 together as this particular server runs a varied workload of about 1500 batches/sec and has anywhere from 1200-2500 connections open at a time, so It could have been anything!  After some further digging I figured out that the file copies were causing the sql memory to get paged out.  At the time I had never heard of a file copy causing an issue in SQL Server!

The experts weigh in

While looking at the issue 2 perfmon counters stuck out–> Memory\Cached Bytes and Memory\Avail MBytes.  While file copies were happening the cache bytes counter would increase very quickly while the avail bytes counter would drop, once the available mbytes dropped to 0 sql server started to page memory out.  After a bit of paging, the errors were logged that SQL had its memory paged out and SQL  became unresponsive.  Since this was a high priority system, I did what any good SQL Server DBA would do, I contacted a few people in my network who may have seen this before.  Interestingly enough I got the exact same response from every one of them, “use lock pages in memory” and don’t use windows explorer to do huge file copies as this is a known “problem”.

Even though I trusted my sources of info, I had a hard time believing that file copies of sizes all the way down to 1GB would cause this sort of havoc without this being something Bing+Google would know about (different file sizes mattered, some sizes worked fine, some would cause the problem).

Workarounds not welcome

I had a valid workaround with lock pages in memory and not using explorer for file copies but, I don’t normally like workarounds such as this on systems as important as this one is to us. After a few server rebuilds we finally figured out that we could reproduce this issue on any win2k3 R2 ent ed 64bit server, this would be the clue we finally needed to make a breakthrough.  After rebuilding the systems from scratch and loading no drivers except SAN we noticed that we couldn’t cause the error!  So, after painstakingly adding each and every piece of our standard server build we realized that Symantec AV ( was the cause.  Yes, another file system filter driver was misbehaving.

In looking back through the change communication, we ID’d where a new version of AV was pushed out and we just didn’t hit the error soon enough after the installation to put 2 & 2 together.  Since disabling AV wasn’t an option we started trying to find a setting that specifically caused the problem and happened across a change that could be made and allow AV to run and SQL to not get paged out.  By unchecking the network scanning options, the windows cache no longer increases during a (network) file copy.  problem solved!!


Some time in the future vendors are going to figure out how to write good file system filter drivers, or they are going to stop trying to use them!  After fighting this issue for a few weeks (or was it months) I can only hope this happens sooner rather than later

April Houston SQL Server Users group meeting

In Houston?  Hungry for lunch?  Want to learn SQL Server from an expert?  Head over to the Microsoft office and learn the top SQL mistakes and how to avoid them from Kevin Kline

Not in Houston?  or hungry?  there’s always the live meeting option!

Here are the details from the HASSUG Site:

Houston Area SQL Server User Group Monthly Meeting Reminder 

When: Tuesday, April 13, 2010 – 11:30am-1:00pm 

Where: Microsoft Houston Office

2000 W. Sam Houston Pkwy. S. #350

Houston, Texas  77042-3615

Pizza and drinks provided by Idera.

LiveMeeting Link:

Conference Call for audio – 1-888-320-3585 (passcode 76027128)

Topic: Top 10 Mistakes on SQL Server

Speaker: Kevin Kline, Technical Strategy Manager, Quest; Immediate Past President of PASS (blog | twitter)

Go to Top