Recently we renamed a server (VM Guest) that was hosting a stand-alone SQL Server, default instance.  There always seems to be something I forget when this happens and this was no exception.

It seems this time I forgot about a linked server; in our environment we create a linked server called LocalHost, which loops back to itself (this is for a monitoring program that monitors long running jobs); when the server was renamed we started seeing lots of NT Authority\Anonymous Login failures in the SQL Error Log, and the process that monitors long running jobs was failing.

A prayer never hurts when renaming a box hosting SQL Server, and I'm the kind of guy who can be inspired by Britney even with the added weight!

So I decided to put this little blurp out there so I'd have a checklist to verify things.

It seems most of this is a rehash of something I found here: http://msdn.microsoft.com/en-us/library/ms143799.aspx , But I was too lazy too read down to the linked servers portion !  Sometimes articles on MSDN and other peoples blog posts I save the the links to have a way of disappearing, so I wanted to create my own blog post, just for selfish reason that I need my own library of "checklists" and "how to".

Checklist for renaming a sql server:

1.  Rename the server (this is the sql instance, the server I will defer to the proper operational teams to handle the necessary rename, active directory, dns stuff).

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

sp_dropserver <'old_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO

2.  Fix Linked Servers

I just drop them and readd them

3.  Fix connections.

4.  Verify SQL Agent and SSIS Jobs.

One Item I'm looking for help on is the groups that SQL Server 2005 creates, these still exist with the old computer name in them, it definetly looks odd, but appears functional.

Yes you can have sql server 2005 and sql server 2008 on the same cluster, this came up while turning an active / passive cluster into an active / active cluster to make better use of resources.  [They say not to use the terms active / passive any more, but it'll take me until sql 2010 to get correct my jargon].

See the section on ‘Multiple SQL Server Versions Within the Same Windows Server Cluster (SQL Server 2000 and 2005 with SQL Server 2008)’ in the link below:

http://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/SQLServer2008FailoverCluster.docx

I've uploaded the document here,SQLServer2008FailoverCluster.docx (3.75 MB), as I have a tendency to need to support things longer than Microsoft makes them available.

I did run into an issue with a necessary patch for the operating system that the installer did not catch until I'd filled out all the information, it then through a warning on the missing patch and would not let me proceed, of course the damned patch requires a reboot so I had to start all over.

This patch was for windows 2003, filestream support... of course I did not select to enable filestreaming, but no matter, it insisted on it.   At least it (the installer) found the issue before it started, as I really hate bombing or failed installations, but it was still a pia to go back, down load the patch, install it, reboot it, and then restart setup....i thought the whole point of the prerequisite checks was to find pre-reqs !  http://support.microsoft.com/kb/937444 

You know Microsoft SQL Server is considered an enterprise database when Management tells you it costs too much !  Micro$oft has finally done it, and management has declared they don't want it.  I don't necessarily agree, but I do follow orders well.

The cost increase of 25% and virtualization licensing changes in R2 are unacceptable.

We're now reviewing Open Source databases and conversion costs, we're also contacting all our vendors and will be insisting on supporting some other DBMS than Microsoft SQL Server either now or by the end of the current contracts.

Microsoft has done a great job with sql 2008 r2, most features we don't need  [BI / MDM], didn't ask for, don't want and a cost increase to boot.  There are good features in SQL 2008 R2, but not at this cost; especially when we won't be utilizing most of the new features.

I'm not looking forward to Open Source, but I might be impressed, 10 years ago IBM DB2 pricing was reaching the tipping point and I began to learn a little respected database called Microsoft SQL Server, now it's time to begin learning something new.

I'd love to hear from some shops that began supporting open source databases, as obviously it will take some time to convert; but all new implementations on sql will most likely cease.

Hard to believe, but it's true !

I've had a nice machine that came with windows vista, 64 bit; but it locked up constantly.  Over the months i've tried many things, even went with installing windows xp 32 bit, same behavior.  Finally I had concluded it must be a bad peice of hardware causing this mystery lock-ups...and let the thing become a paper weight.

Finally decided to try windows 7 last week, popped the dvd in, formatted the hard drive (no sense upgrading a non-working os) and installed windows 7.

Wow, windows 7, right out of the box, my machine finally works.

It's been on for a week without a single "lock-up", amazing !  Micro$oft may have gotten one right.

I always thought the definition of getting screwed twice was this; A German student who auctioned her virginity and ended up paying 50% in taxes.

But it turns out that identity values in SQL Server can also screw you twice!

I used to really like identity values and problems have been rare, but this week, inside of a few days I've been screwed twice by identity values.  This is also the year of embracing merge replication, time to meet your new friend the guid!

No need to rehash what happenned with identity values, but altering a table from int to bigint with 30 billion+ rows is not possible.  Found two very helpful posts, i include their links here as they were most helpful:

To resolve (albeit temporarily) identity values running out for an int, do not forget you have the negative values.

http://sqlfool.com/2008/11/max-int-identity-value-reached-dbcc-checkident/
http://dbwhisperer.blogspot.com/2009/04/which-identity-column-is-running-out-of.html

Definetly we'll add a new automated health check, will probably fit in nicely with our script that check row counts occasionally to also start checking identity columns and raising alerts on certain thresholds.

I'll also be pushing back much harder on development teams that want identity columns with an int data type.

Well, it continues to be a pain in my ass, the inability to grant truncate table permissions on a table to specific users without giving them excessive rights.  SQL 2K5, no solution, SQL 2K8 no solution, well we did get a bunch of other stuff, so we'll continue to suffer.  I originally posted an article on Granting Truncate Table permissions in 2008, here is that link.  Recently the need has arisen to have the ability log failures for the solution.  The code was modified by Holland Humphrey and I'm posting the solution again with his updates.

The solution basically uses a schema, dba, to hold 3 tables for a stored procedure which is used to truncate tables.  The 3 tables, Truncate_List, Truncate_Audit and Truncate_Fail.  A user is granted permissions to a stored procedure in the dbo schema that performs the work and verifies that the table is authorized for truncate, and logs the success to truncate_audit and the failure to truncate_fail.  One could argue that truncate_audit could hold both success and failure, but I'll leave any possible enhancements up to those that desire to do so.

The original inspiration for this actually came from the Oracle group where I work.  Oracle has the same problem as sql "Grant Truncate on {table} to {User}"...so simple....but it's not there in SQL or Oracle.  I basically translated the Oracle solution into SQL Server.

Every upgrade we do from SQL 2000 to 2K5 or 2K8 uses this solution and allows us to reduce our permissions set to the lowest possible necessary.

Here are the scripts for the solution.

1.  Create a Schema Called DBA {if you prefer something else, adjust the tables and proc}.

2.  TruncateSolution_CreateTables.txt (4.67 KB)

3.  TruncateSolution_CreateProc.txt (4.34 KB)

Starting in December I purchased the following books.  Most of these were related to new projects where I work and knew I'd need to sharpen a few skills, Encryption, SQL Express, Merge Replication and security.

• Beginning SQL Server 2008 Express for Developers: From Novice to Professional - Robin Dewson
• Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5
• Mobile Merge Replication - Rob Tiffany
• SQL Server MVP Deep Dives
• Expert SQL Server 2008 Encryption - Michael Coles, Rodney Landrum
• SQL Server Forensic Analysis - Kevvie Fowler [bought but not reviewed]
  

Reviews:

Beginning SQL Server 2008 Express for Developers: From Novice to Professional - Robin Dewson

This book was a disappointment, but I knew when purchasing it that it was probably not going to be of much help; but I had to start somewhere.  We're preparing to migrate from MSDE to SQL Express 2K8 in 10,000 locations; I was involved in the intial deployment (and patchings) of MSDE and I really need to figure out how to avoid the gotchas with SQL Express; this book wasn't it.  Over-all it's a good book, but it's not meant for an existing SQL Server DBA, I would expect even a Jr. DBA to already be familiar with the content.  It truly is meant for an access developer who wants to learn SQL Express; I gave it to our local access programmer at work {though it's actually a she and she's not in IT or a programmer, seems that is where most access db's begin life}, maybe she'll develop a new career path, we seriously need more women in IT, I digress.

Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5 Mobile Merge

This book was great.  We're deploying Merge replication with up to possibly 10,000 differnt locations to SQL Express 2k5 and that is why I purchased this book.  While not an exact guide or road map for what we're doing [we're not using SQL Server compact edition], most of the concepts apply.  It has really been helpful, we're still in the planning and proof of concept stages, so it remains to be seen if we'll continue with this design at work, but I feel this book helped us greatly.

SQL Server MVP Deep Dives

This book was great.  I kind of avoided it at first, as I don't like books that too many people jump on as good, but this one lived up to that promise.  Very few parts of the book were lame ! [there's a glowing recommendation].  I put a sticky next to each new thing I learned and when I was done, i'd say there were at least 15-20 stickeys across the book, that's worth the money I paid for it.  It's also nice as you can read this book in quick "snippets" flipping to what subject interests you and what you have time to read.

Expert SQL Server 2008 Encryption - Michael Coles, Rodney Landrum

This book is good, though a bit dry, I'm still not all the way through it...reading about encryption is not what I'd consider an exciting topic, but it is a skill i'm going to need and it was seriously lacking.  We're preparing to bring in a 3rd party hardware device to provide consistent encryption and key management across the entire enterprise, which is why I needed this book.  So far it's been a great help, as I don't feel like a complete moron as I attend the endless meetings on implementing enterprise encryption where i work.

SQL Server Forensic Analysis - Kevvie Fowler

I haven't got to reading this one yet.  I'm looking forward to it, as security has become a major focus in ways I couldn't even imagine 2 years ago.  I thought the description of this book is interesting, as it approaches security from the aspect of determine what was compromised, from that I hope to learn better practices in security data....I'll update on that later.

January and February are peak times for my company's business; now I can start to focus on new things, here is what I learned during peak and where 2010 will focus:

• Capacity management is the 2nd most important thing dba's do.
• Performance tuning and monitoring is the most important thing (during peak)
• Locking and Blocking is the biggest scale issue i regularly see.
• Mirroring on SQL Server standard edition sucks (especially for geographically disperse sites).
• Mirroring on SQL Server enterprise edition rocks (though the cost factor is prohibitive).
• Informatica is no better an ETL Tool than SSIS
• Oracle and SQL Server are a pain in the ass to make work together (Oracle DBAs are'nt much help either)
• 1 terrabyte of disk space is almost enough !
• Re-indexing and defragging is analogous to politics and religion and DBA's should be barred from discussing the topic
• Encryption is a pain in the ass.
• Surprise - Merge replication actually works and is not near the PIA I thought
• 6gb of RAM is never enough
• Polyserve is not worth the effort
• VM Ware with VMotion is awesome
• VM Ware is a good alternative to Polyserve
• Hyper-V is not there yet
• SQL 2008 R2 licensing sucks
• SQL 2000 is finally no longer meeting the business requirements and we may finally get funding to upgrade the remaining instances !

Migrating from one cluster technology to another or even with-in the same technology is fairly easy.  Recently I'm run into an issue where we need to migrate a SQL Server Instance from a HP Polyserve Cluster to a Microsoft Cluster.

There were two issues I found in setting this up:

• Installing SQL Server on a Microsoft Cluster requires a virtual name
• Keeping the exact same Port Number

The reason for the above two issues was to keep the down time to an absolute minimum and ensure there were no changes necessary to the application or infrastructure (firewalls).

Fortunately both HP Polyserve and Microsoft Clustering use virtual names, this is what makes this possible.

I found the following two links helpful:

How to: Rename a SQL Server 2005 Virtual Server
How to change the network IP addresses of SQL Server failover cluster instances

The key to making this happen is to install SQL Server using a temporary Virtual name and IP Address and ensure to use the EXACT Same instance name.  Instance names can not be changed with SQL Server 2005 (or at least it's not supported to change them).  Changing the port number is pretty standard stuff.

Now you can pre-test your migration of databases and user logins, and load test the new hardware. 

At the designated change time we performed the following.

• Take the Microsoft Cluster Off line
• Take the Instance on HP Polyserve and delete the Instance and virtual name (binaries and data files will be kept as a backout plan)
• Using the SQL Server Configuration editor, change the IP address on all nodes in the Microsoft Cluster:
• Using the Cluster Administrator change the SQL Server IP Address
• Using the Cluster Administrator change the SQL Server network name
• Bring the Cluster on-line
• Test

The one issue we ran into was with logical networks and VLANS, I don't have a complete understanding of network topology, but only certain logical networks with-in our environment can host different ranges of IP Addresses.  Initiall we built the new cluster on a logical network that was unable to host the existing virtual name and the switch failed, ensure to talk to your network, windows and DNS engineers about exactly what your wanting to do so they can build things properly the first time, as they don't like switching and changing things twice any more than DBA's do !

SQL Server security, logins, auditing and reporting still not where it needs to be ?  Maybe someone can help me with ideas, if so email, me or leave me some comments.

I've been through a lot of requirements and implementing everything the information security group wants is like Macgyver with duct tape.  Currently this is for SQL 2005, but I'd like to know how to implement it for SQL 2008 as well.

Currently I'm meeting the following Information Security requirements:

• Limit Logins by specific IP or IP Ranges in combination with Time of Day [login trigger]
• Limit Logins by Time of Day [login trigger]
• Disconnect logins that are connected past there authorized time. [job running every 5 minutes]
• Keep Record of All Logins (success or Failures) [daily job to scrape error log]
• Keep Counts of Login Failures [daily job to scrape error log]
• Keep track of Date and Time Password was changed (sql authenticated only) [Tracing]
• Provide reporting and alerting that shows counts of failure, by month, by id etc. [Reporting Services and SQL Agent]
• Limit number of concurrent connections by ID and by Global Count [login trigger], so the max number of connections for an instance may be 1000, and the max for a user maybe 5, and the max for the application id may be 300.

Now they want me to selectively enforce password length and expiration based on the following requirements for SQL Server Authenticated:

• Service accounts  28 characters, expire yearly  (accounts that are not, or should not be used for general sign on’s and limited by what machine(s) they can login from)
• Admin accounts 15 characters, expire yearly (accounts that are used by DBA’s)
• User accounts 8 characters, expire every 90 days (normal user accounts) [this is easy as our Active Directory can enforce this]

It just seems that meeting all these requirements in SQL becomes very difficult to administer; we're running a combination of:

• Dedicated database for reporting, configuration and capture of statistics
• Login Trigger (difficult to administer and one little issue everyone is locked out)
• Tracing (to capture when a user changes their password)
• SQL Agent jobs (to clean up, scrape and roll over reporting tables)
• Reporting Services (for reporting)
• Enabling the DAC
• Enabling Login Auditing
• Enabling CCC (Common Criteria Compliance)

My counter-parts in Oracle seem to have an easier time meeting all these requirements, with less overhead, easier to administer and keep running, and less danger of locking everyone out.

SQL Server continues to frustrate me with this, anyone have suggestions or an easier time with SQL Server 2005 or 2008?

All these things should be available out of the box, duct taping all that stuff together in sql server is absolutely brutal, documenting and training someone on it is even worse.

It does provide good job security, as let the suits out source the DBA Staff after implementing all of these requirements and they'd sure have a tough time finding someone to run this stuff easily!

The "Suits" and "Information Security"!

Don't expect your queries to be in order without a select statement, guess what same goes for SSIS!

Today I had to trouble-shoot a package (not developed by me or our SQL DBA Group), but regardless, it becomes our problem !

SSIS was exporting a table to a flat file, turns out this flat file is really an XML File.  The table is populated with XML from a stored procedure prior to the export.  The data pump ole db source was a table, turns out it's not always in order, resulting in malformed XML.  Change the source from table to a select statement with an order by clause and all is working well, fortunately there was a rowid (identity) column in the table already.

Frustruating to the end user, as it was a package that had been working fine for months.

Of course there was no primary key, making this a heap table; but either way, if the requirement is to have the data ordered, than you need an order by statement, even if the data is "in the table that way", and even for SSIS.  [Be nice if Microsoft put a warning on there that the data access mode may not be in order.]

This seems to be more of a problem on heap tables (no primay key) and Enterprise edition sql (which supports Merry-go-round scans [i think that's what it's called]).

The patching game.

Better give me a whole lotta lumps.....

Last week Microsoft released a patch for a SQL Server Security vulnerability.  This week Microsoft released SQL Server 2005 CU 6 (which includes the security patch and some other fixes).

Test those "other fixes" carefully.

We just ran into a change in behavior for how SSIS evaluated variables that are passed into Execute SQL Tasks as parameters, Not sure exactly which patch introduced the change as they are cumulative and we were running CU 2 previously, so anything from CU 3 on up could have been the cause.

We were running on version 9.00.4211, which is SQL 2K5, SP3 CU2.  We upgraded to the Security Fix (9.00.4262), and the SSIS package broke.  Hoping not to have to trouble shoot the issue and needing to be on CU 6 anyway, we applied it, hoping it would fix the issue...no luck, time to trouble shoot.

The issue is really one of poor coding practice.

There are some variables passed into an execute sql task to dynamically create a where clause. 

The variable in question was declared as an Integer

The variable was than passed into the execute sql task, but it was of a varchar type, below is the corrected parameter mapped as a numeric.

The execute sql task is shown here, the parameter is the first one evaluated with the if "? <> 2".

This execute sql task passed the parameter in is as '', blank, if the parameter mapping was not set properly.  I confirmed this behavior behind the scenes with a profiler trace as well.  Interestingly even with the parameter mapped properly as numeric, it is still passed into sql server as a varchar !  But with the parameter mapped as numeric, the varchar is properly populated with value of the variable being passed in and the statement completes successfully.

Test those SQL Server patches carefully, there is always the off chance of unintended behavior change.  Ultimately it was "sloppy coding" to be passing an integer variable in ssis, to a parameter mapped as varchar.  But it has been running that way for 14-16 months without issue.

Test carefully.

9.00.4211.00 - 2005 SP3 CU 2
9.00.4262.00 - QFE Security Fix
9.00.4266.00 - 2005 sp3 cu 6

In response on where to get sp3 cu 6:

I'm in the middle of an all-nighter for a SAN Migration.  Lots of down time, here's what I found worth reading:

The unspoken truth about managing geeks.

Understanding Service Level Agreements for Database Development.

Is SQL Server's latest security hole a real threat?

Very interesting syntax of the in-clause I didn't know existed:

SELECT*FROM person.contact

From the I did not know this would work department

Big competitors spark Netezza’s partner-friendly strategy

Keeley Hazell looks hot while she's shilling for something.

Don't ask me how I'd go from Keeley to Jeremiah Johnson, but all nighters due that to you, than and i've never been normal, it's boring.

"where ya headed?"
"Same place you are Jeremiah, Hell, in the end."

Link to download sound track to the movie, Crow Corner., a long time ago I had it on LP, doubt I'd ever find it on cd or mp3, but all night'ers have a way of helping you find things.

There are many emphases for the SQL DBA, one of the major items is security.  Where I work security and patching have taken on new importance over the past 12 months.  Patching which used to be a yearly event, is now monthly or even weekly.  Combine that with SOX Controls and other internal controls, there is a focus on security.  Part of that is seen in SQL Server from Microsoft, kudos! 

The product gained a new level of security with SQL Server 2005.  Service Pack 2 gave us Login Triggers / Service Broker Events.  SQL 2008 saw the Builtin\Administrators account gone (separation of duties) and even more controls.

Than today it was distressing to see a news report about Microsoft ignoring a vulnerability with SQL Server, basically dismissing it as anyone with administrator privileges already has control [What happenned to separation of duties?].  The whole point of where security with SQL Server was going was to ensure that administrators could be properly segmented.  Where I work they'd like to get to the point where a SQL DBA can't even view or query the data that they administer.

If you find the below security vulnerability to be an issue than complain to Microsoft.  Also if you didn't know about this vulnerability, than you need to ensure to plug-in to some other industry news sources to get your information on vulnerability and patches, as you can't always count on Microsoft to disclose issues.

-----Original Message-----
From: Security Wire Daily [mailto:SearchSecurity@lists.techtarget.com]
Sent: Wednesday, September 02, 2009 10:38 AM
Subject: New SQL Server password flaw surfaces

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SearchSecurity.com: Security Wire Daily
Breaking security news, the latest industry developments and trends
September 02, 2009
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

UNPATCHED VULNERABILITY DISCOVERED IN MICROSOFT SQL SERVER
Michael S. Mimoso, Editor, Information Security magazine

Microsoft SQL Server administrators are being warned today about an
unpatched vulnerability in the popular database software that exposes
user passwords in the clear, as well as credentials delivered by
applications trying to access the database server.

Researchers at San Mateo, Calif.-based Sentrigo Inc., announced the
flaw this morning, and also revealed that Microsoft has no immediate
plans to release a patch for the vulnerability. Sentrigo, meanwhile,
said it has developed a free utility that will erase these passwords
from memory.
Read more:
http://go.techtarget.com/r/9124859/8704472

SQL Server SAN Migration

I think this is my 3rd or 4th SAN Migration casued by:
 - completely moving data centers
 - changing the storage backends to different vendors
 - consolidating SANS
 - growing to a bigger san to consolidate more

There are several different scenarios to consider
 1.  Stand alone SQL Servers Instances on Internal Disk to SAN
 2.  Stand alone SQL Servers on SAN Disk (changing sans).
 3.  VM Ware SQL Servers (required to be on SAN)
 4.  Microsoft Clustered SQL Servers
 5.  Polyserve Clustered SQL Servers
 6.  BCV's / SAN Mirroring / replication technologies
---------------------------
The most important thing to remember is to backup.  The next most important thing, no matter what the SAN Engineers, Windows Engineers or Vendors tell you about SAN Migration, YOU AS THE DBA ARE RESPONSIBLE.  Understand the migration plan for each scenario, regardless of what anyone says, the DBA is always left holding the bag.  If you don't understand the migration plan / scenario, make them explain it, learn it, try and practice it.  I'll explain with an example. 

One of the important items in most scenarios is updating to latest drivers and versions of software, even this step can be dangerous.  In a recent effort to patch servers to the latest version of drivers the SAN disk just "disappeared", when it came back, there was NO DATA.  No amount of research could yield what happenned to the data.  Refer back to the most important thing!
----------------------------

All of the different scenarios are simple, with careful preperation and a good windows and SAN team. 

1.  Stand alone SQL Servers instance on Internal Disk to SAN.

Usually the most difficult thing here is that you are taking an existing stable server and adding a lot of new complexity to it.  Drivers and hardware for the SAN, sometimes this does not always go well, if possible I try and get new hardware and completely swap the machines, configuring the new machine in advance for the SAN and installing sql; taking several dry runs to ensure it's stable.  The next issue is the down time in copying the data from the internal disk to the san.  Than switch the drive letters and start SQL.  Don't forget the most important thing.

2.  Stand alone SQL Servers on SAN Disk (changing sans).

Here is where having a good windows and san team can help you.  Most of the migrations I've been involved with the Windows and SAN team will setup a mirror between the SANS.  Than on migration day we stop SQL Server, the windows and san team ensure the mirror is up to date, than split the mirror and hook the server up to the new storage and ensure the drive letters and mount points come up.  SQL DBA restarts sql, boom, your done.

Sometimes if your switching san vendors, you can't setup a mirror.  Now things get dicey.  Can you get "hooked" up to both sans simultaneously, of so than your ok, stop sql, copy the data to the new san, reset drive letters and mount points, restart SQL. 

If you can not get "hooked" up to both sans simultaneously than you need to default to some type of backup and restore mechanism, tapes or copying sql files to local disk (if you have room).  I'm usually not a fan of this, as I find that different vendors use different drivers, switching vendors means now you have both vendors drivers on the machine, the machine decays and becomes unstable, NOT FUN.  Don't forget the most important thing.

3.  VM Ware SQL Servers (required to be on SAN).

These have been my easiest san migrations.  That's because we have awesome dedicated VM Ware administrators.  They do it all, just schedule the outage.  But trust me, Don't forget the most important thing, check your backups went to tape, double check your Disaster Recovery plan.

4.  Microsoft Clustered SQL Servers

I've only done Microsoft Cluster migrations with SAN Mirrors, and it has been uneventful.  This is because the "mirrors" end result is the preservation of clustered resources (quorum), drive letters etc.  But don't forget the most important thing.  Again a great windows and san team makes this easier.  I'd fret to switch vendors on the Clusters, but if I ever do I'll update this post.

5.  Polyserve Clustered SQL Servers

This is the SAN Migration worst case scenario, "The clustered file system".  Below are the steps we followed for migrating sans with Polyserve.  Don't forget the most important thing.

-Dump vsql and vsqlinstance information from cluster
 -mx vsql dump >> vsql.txt
 -mx vsqlinstance dump >> vsqlinstance.txt
-Get a listing of all storage by copying the grid on storage summary to excel
-stop and disable all SQL instances and VSQLs
-copy the virtual root for each sql server instance to another server (outside the cluster)
-deport ALL dynamic volumes (paths are automatically unassigned)
-stop cluster services on all cluster nodes
-copy the entire c:\polyserve directory to another server (outside the cluster) for each machine (CYA)
-manage the storage to unpresent all LUNs from the old array
-break the mirror relationships and then present all of the mirrored LUNs
-create three new 1GB LUNs on the new array and present them for new mem parts
-put partitions on the three new LUNs
-go into the config utility on node 1 and delete old membership partitions and add the three new membership partitions
-start cluster services on this node
-export the config to other nodes and start the service on the rest of the nodes
-import all importable dynamic volumes
-assign paths
-enable instances and vsqls
-done
Polyserve SAN Switch.doc (29.5 KB)

 

6.  BCV's / SAN Mirroring / replication technologies

Administring the advanced SAN technologies is different for each vendor and quite proprietary.  You definetly want to test and work with each one individually and ensure it all works.  The details of this are far outside the scope of a simple blog post, but having great SAN engineers will make this easy, as they generally setup the mirrors, clones and movement of them to different machines or remote locations.

Don't forget the most important thing.

Ok, for whatever reason we compress the XML Blobs we store in the database.  This created the problem for me to view the data, as the DBA is always asked to go find this record in the XML.

I ended up creating a little windows form application in VS2005, using C# that can decompress the blobs so I can view the data.

I also added a peice that shows how to compress the blobs and save them to a database.  There was also a fun peice about displaying byte[] data in datagridview, it's in there!

I've also created a sample database with one table and a few records that are compressed, it's a sql server backup file, or you'd have to modify the code to create a table with some compressed blobs to see it work.

This was a just a fun thing to do, we get a lot of mileage out of compressing the XML data.

BlobTest.bak (2.58 MB)
XMLBlobViewer.ZIP (64.2 KB)

Screen shot:

This post may raise more questions for you than it answers, but it really is something that happenned in my shop and confused some people.  So I'm creating this post as a place holder so I'll be able to reference this 10 months from now when the same issue comes up and I've forgetten the solution.

Basically for whatever reason we use Excel, darn !  No matter how I try and recreate these Excel sheets in Reporting Services, some business users won't bite and they still need excel files.  Generally these SSIS to Excel data pumps work fine....the ones that generally trip the process up are ones with long text and multiple "named" work sheets.  To solve the "named" work sheets problem we often use an Excel template file that is copied to the destination and renamed, than the data pump occurs.  As we migrate the SSIS packages from Development, QA to production, sometimes an "odd" excel worksheet gets introduced, usually named "sheet1", lovely, below is the procedure we used to correct this...

Thanks to Tom Reeves from our team.

To Delete Unwanted sheets in Excel for use in SSIS

1) Map a local drive to the E: drive of the app server (ServerNameHere\E$)
2) Open template file on server and delete unwanted tabs
3) Open SSIS package
a. You’ll need to make note of the tab name(s) that you are working with
b. Copy query from the source object in the data flow
4) Create a new DTS package – SQL2000
a. 1 SQL Connection
b. 1 Excel Connection
c. 2 Execute SQL Tasks
i. Both pointed to the Excel connection manager
ii. One for Drop table and one for Create table
5) Open the Transformation flow between the SQL connection and the Excel connection - DTS
6) Copy and paste the query from the SSIS package into the Source tab
7) Click the Create button on the Destination tab and copy the code from the window.  You can hit cancel on this screen now.
8) Open one of the Execute SQL Tasks and set the connection manager to the Excel connection and type your Drop sql statement. 
a. Example: drop table `SheetNameHere` - No dollar sign in the table name
8) Open the other Execute SQL Task and set the connection manager to the Excel connection and type your Create sql statement.
Example:
CREATE TABLE `SheetNameHere` (`Division_Code` Decimal (2,0) ,
`Division_Dept_Id` Decimal (7,0) ,
`Office_Dept_Id` Decimal (7,0) ,
`Last_Update_Date` DateTime ,
`Reason_Text_Opt_out` LongText ,
`Reason_Text_Opt_in` LongText ,
`WHQ_Comments` LongText ,
`WHQ_User` VarChar (30) ,
`WHQ_Last_Update_Date` DateTime ,
`military` Short ,
`office_type_code` VarChar (1) ,
`ABC` VarChar (10) ,
`Client_Segmentation` VarChar (50) ,
`number_of_windows` Long ,
`number_of_desks` Long ,
`Number_of_bilingual_tax_pros` VarChar (50) ,
`Year_round_indicator` VarChar (1) ,
`Latino_designation_year_prior` Long ,
`office_status` VarChar (1) ,
`Notary` Long ,
`ITIN` Long ,
`Spanish_Speaking_Preparer` Long  )

Patching SQL Server still has a long way to go !

Patching SQL Server still has many necessary improvements.  It's an endless cycle.  Service Packs and Cumulative Updates.  The release process of the patches is much better, but the technical windows installer portion of the process is painful.  Ever look at a machine that has 4 SQL Server instances?  Take a close look at your c:\windows\, C:\windows\installer, c:\Prog...\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix.  Where I come from we call that a cluster f***!

If you have 20-50 SQL Instances, it's a manageable process, when your dealing with 100's and 1000's it's a nightmare.

Troubleshooting the process is a kin to the "needle in the haystack" analogy.  Recently while applying SP3 to a SQL Server I received the following error:

...
Error Number              : 11032
Error Description         : Unable to install Windows Installer MSP file
------------------------------------------------------

Fortunately I've seen so many errors applying SQL Server service packs and patches (remember we patch 100's to 1000's so I'm not saying these errors are common place, but even 5% can generate a huge volume of work).   Service pack Errors I've Seen.
 http://www.lifeasbob.com/2008/01/29/CumulativeHotfix53215InstallIssues.aspx

When searching for the 11032 error cause, the summary.log leads to another file, some sqlrun_sql.msp.log.  Always search for “return value 3” in any MSI logs to quickly find the error.  I found the following in the log file:

MSI (s) (9C:78) [20:33:17:978]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI7F3.tmp, Entrypoint: InstallFTERef
FTECa.DLL: INFO: FTE: InstallFTERef(), Entering...
FTECa.DLL: INFO: FTE: GetFTERefInstallParams: FTERef : ?*
FTECa.DLL: ERROR: FTE: InstallFTERef: Fail to create FTERef file: D:\data\FTData\noiseCHS.txt,Err=3
MSI (s) (9C:FC) [20:33:18:322]: User policy value 'DisableRollback' is 0
MSI (s) (9C:FC) [20:33:18:322]: Machine policy value 'DisableRollback' is 0
Action ended 20:33:18: InstallFinalize. Return value 3.

Sure enough the path d:\data was wrong, it should be d:\mssql.1\data,  I copied the FTData directory to a new one I created called d:\mssql.1\data and it completed successfully.  It's not even worth the effort to think how this got screwed up, the ftdata directory is picked up in the registry, so someone moved the directory or hacked the registry.

We did find one behavior change with SP3, DBCC CheckIdent.  DBCC CheckIdent used to successfully take Null parameter, now it does not, easy enough to correct by testing for Null and replacing with a zero.  We found this through a job that resets the identity value of table, the job began failing when we applied sp3, sure enough testing on sp2 shows it was successful.  Probably this is a good thing that sp3 tightened up the parameter validation on DBCC CheckIdent.  Example below:

Declare @maxId Integer
Select @maxId = Max([Identity_column]) + 1 From [Some Table]
Print '--Resetting seed to' + Convert(varchar(10),@maxId)
DBCC CheckIdent ('Some TABLE',reseed,@maxId)

Installing vendor databases on SQL Server is usually pretty straight forward.  Not many DBA's like the process, but the proliferation of 3rd party products that use SQL Server is continuing to grow.

Recently this particular vendor, of course insisting their product was 64 bit and SQL Server 2005 compatible, ran into problems for me.  It's only compatible after you manually add registry entries that make the installshield program think there is a 32 bit instance of MSDE !  Yah, that's compatible (oh by the way, those registry entries also break the sql browser service so you whole instance becomes unavailable unless it is accessed directly through a port number).  NICE !

Getting through the above process was painful enough, than the installshield installer errors out with a SQL Server error on creating a view, indicating that a dependent object is missing.

Technical support, while helpful is clueless.  "Never seen that before!"  After 3 days of issues, i'm basically troubleshooting for them, taking traces from our lab environment and comparing them.  Finally we stumble on to the fact that installshield is creating a _setup directory where it is putting the SQL Server scripts.  This folder (_setup), happens to be under the data directory where the MDF's are being created (NICE).  This data directory happens to be a mount point!

Ahhh, for whatever reason, Installshield runs the scripts differently from a mount point vs. a drive letter.   We think it's either a bug in Installshield or an issue with the permissions on the mount point.  We were not able to find any differences in permissions, but sure enough when we changed the location of the data files (which changes the location of the _setup directory), objects in the database were created in the correct order.   3 days of my life gone.

This vendor could have done many things differently.  Providing some type of manual work around, scripts to create the database (this was not an option so I was screwed), backups to manually restore, something, someway to escape the installshield install (which was like version 5 !).

And this was not a small dippy vendor, this is compliance software used by many of the big banking and financial institutions.  No wonder so many found them selves in danger of collapse !

Watch those mount points. 

The new loop.  I used to always default to using a vbs script to loop through and delete files, now I use forfiles, a one liner, and much easier to look at, just like the pic below!  Who knew looping could be so much fun.

Deleteing backups, log management...tiresome tasks.  Recently learned a new trick from SQL Puma for deleting files using a utility from the windows resource kit.  A program called forfiles.exe.  Much easier to use than deleting with a VBS Script (see my posts on Archive Logs and SQL Logs).  Powershell will eventually make even forfiles obsolete, but it is not on each of our servers and the windows support team is reluctant to put it there :( ForFiles is on every one of our windows 2003 servers so it's good to go.

To delete all backups older than 6 days:

Delete all files in the standard TEMP folders and all their subfolders after 9 days:

forfiles -p"%SYSTEMROOT%\TEMP" -s -c"cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" -d-9
forfiles /p "%TEMP%" /s /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -9

Delete all *.TMP files in the system root (like C:\WINDOWS) that are older than 9 days:

forfiles /p "%SYSTEMROOT%" /m *.TMP /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -9

Delete old IIS log files:

forfiles /p "%SYSTEMROOT%\system32\Logfiles\HttpErr" /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -99
forfiles /p "%SYSTEMROOT%\system32\Logfiles\W3Svc1" /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -99
forfiles /p "%SYSTEMROOT%\system32\Logfiles\SmtpSvc1" /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -99

Dealing with spaces in filenames:

Forfiles -p R:\MyFiles -s -m *.* -d -365 -c "Cmd /C Echo 0x22@Path\@File0x22 "

This script copies jobs from one msdb database to another, useful for when SQL Server is re-installed, and the DBA Forgot to script out the sql agent jobs; but you still have the old msdb mdf and ldf. 

Simply attach the database as msdb_old.

It is also possible to use this via a linked server and copy some sqlagent jobs from one server to another; this script is specific to sql server 2005. 

One item I had to adjust is that some of the jobs appeared "greyed" out afterwards and I had to open each job and adjust the target server to "local". 

But it was still quicker than re-creating all the jobs.  (I was unable to restore the previous version of the msdb database because it was at a lower service pack level than what the new server was built at - had I been smart as the new server was built there would have been a restore of the msdb database completed when the new server was at the same level, but I missed that step and was then left with the task of recreating all the jobs, argh).

I'd be cautious using this script if there were a lot of reporting subscription jobs (as there would have to be some synchronizations with the report database and website), but in this particular case all the jobs were custom backups, tlogs, exports, imports and other purely tsql and ssis related tasks.

http://www.lifeasbob.com/Code/ScriptVault.aspx?script_id=80 

Affinity mask keeping SQL Server Agent from starting.

Recently had a unique issue in one of our HP Polyserve clustered environments.  One of the unique aspects of HP Polyserve is that you can cluster different types of hardware.  This can reduce costs and risks (as a bug in firmware or hardware on one machine is also most likely going to exist in the other node, especially if they are the same make, model, and usually purchased together).

We had an affinity mask set on a 16 processor machine, the instance was intentionally re-hosted to another server in the cluster, which was an 8 way machine.  The instance did start, but SQL Agent would not.

The following error messages were found in the sql errorlog:

initconfig: Warning: affinity mask specified is not valid. Defaulting to no affinity. Use sp_configure 'affinity mask' or 'affinity64 mask' to configure the system to be compatible with the CPU mask on the system. You can also configure the system based on the number of licensed CPUs.

SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.

A quick bit of research showed a similiar issue in a Microsoft Cluster, where the one node did not have the correct permissions for the "lock pages in memory" setting, causing the same result of SQL Agent not starting, you can see a great write up of this in Suhas' blog.

Seems if the "start up" process for a SQL Server instance is interupted that the settings changed from the defaults in sp_configure are not set and the process does not continue.  From what I can determine this "start up" process is the settings contained in sp_configure, if any of these fail along the way the process just stops or rolls them back and the other settings like "Agent XPs" (required to start sql agent), will not occur.

I think this is a behavior that should not exist in SQL Server, why does an incorrect affinity mask keep SQL Server from finishing running the other settings set by sp_configure ?  These options should be mutually exclusive and one failure should not preclude others from running.

I've had similar issues with how SQL Server starts up affecting SQL Agent, and it does not appear to be a priority to fix, see my post on SQL Agent will not start when a user database is in recovery. 

This was easy to fix, but required setting a correct affinity mask and stopping and starting the service.  Generally we don't use an affinity mask on many instances.  HP Polyserve provides pre and post start up scripts which we'll use to address the issue in the future, though in extreme cases of failure no pre shutdown script will be effective.  Plan your failover capacity carefully.

Recently I had a nice experience of working an outage of a SQL Server caused by a SAN Issue.  Here is where clustering breaks down.  Fortunately I work in a big shop which uses Microsoft, Veritas, Polyserve and VM Ware clustering technologies; but all of them have a single point of failure, the SAN.

The official response to the problem was:

We are experiencing intermittent {vendor here} issues causing some SAN storage to become read only. Server team is closely monitoring for this condition and putting the setting back to read/write. A fix is available and being planned for Saturday night, unless the issue becomes more prevalent that it is now.

Lovely.  What is missing from the statement above is depending on which clustering technology you are using, it may require a reboot to bring the storage back for windows (sometimes all nodes !).  Veritas, Polyserve and VMWare seem to handle san / fiber hickups the best.

It may be time to research a stretch cluster with different sans and some type of replication or mirroring.  The uptime of 9's (pick your number) is a difficult task to reach and in my opinion not truly possible with one SAN.  I've seen too many SAN Failures.  SANS are supposed to be built in redundant everything, but somehow almost all my outages on High Availability SQL Implementations are the SAN.

Of course it has to be something, i'm not inferring that a SAN is no good or poorly designed, just that as every point of failure is addressed, another one appears. 

How the vendor could know about this issue and not let us know, is confusing in itself.  The vendor is responsible for maintenance and patching of the SAN, seems they wanted to keep this bug "close to the vest" and maybe just "roll" it in with some other firmware patching.....i'm not impressed.

Keep your vendors accountable and ask them how often they patch the san, and what patches are missing from your environment.  Work with the vendor so they know that you are willing to accept patches and get them applied, don't wait for the bug to affect you before applying it.

This may apply to SQL Server as well, how often do we patch to a specific level and try and stay steady there, not wanting to apply all the cumulative updates, unless it affects something.  It may be an affect you don't like.

Be more pro-active.

Windows has historically had a reliability issue in the enterprise (remember daily reboots?), SQL Server received this tag right along with it.  Over many years starting with the release of SQL Server 2000 Microsoft has been working to correct this issue, now we need to start working on our vendors.

Yesterday 5 Windows servers, crash in one day, of course all of them running SQL Servers.  I've got 146 SQL Servers and go months with no issues, than BAM!  These were all different "flavors" of SQL (2K, 2K5 and different service packs).  Most likely not a SQL issue or even a windows issue, but regardless the product takes a hit and so do I.

The machines were so "unresponsive" that the only way to correct was to kill the power to the machines.  Of course no logs and no indication of what the problem was.  I highly suspect one of the many 3rd party crap that we run on these windoze machines:  backup, anti-virus, san drivers, monitoring programs etc; though we'll never know for sure what was to blame.

Even our Microsoft Clustering did not save me!

No fail-over, no nothing, had to reboot both nodes of the cluster; plenty of room still for the 3rd party clustering utilities as they also have failures as well, but I seem to have higher up time with them...try Never Fail, Golden Gate, HP Polyserve or Veritas.

We still have a long way to go to increase stability, we've got to continue now to harp on our 3rd party vendors that provide the services not built into windoze, just wish we knew which one to call for this outage.

So I'm enjoying a cup of tea and decide, wouldn't it be nice to upgrade 1000's of instances of MSDE sp3a to sp4?  Nice, i love unsupported products, let's do it !

Ugh, I know, but somethings aren't up to me, I deliver the results, this was the task, it's in the skill set, get it done.  I ran into several issues where I had to upgrade a MSDE instance from SP3a to SP4.  This should be a simple exercise, and it was for many installations, but there is a consistent theme where the upgrade fails and no manner of hitting, kicking or screaming helped. 

Uninstalling the original installation and installing the SP4 version of MSDE was successful, but then the master database, logins, msdb all has to be restored to get the instance back to a working state.

I'd be fine with the uninstall and reinstall, but we're talking 1000's of installations, how many are going to have a problem ?  Surely the solution can't be to uninstall and reinstall.

I found some good documentation on troubleshooting sp3 to sp4 for MSDE (remember it's been around awhile and is out of support).  The best link I found was http://support.microsoft.com/kb/901163, unfortunately it didn't resolve the problem.

Consistently the error that keeps happening is UpgDetectBlankSaLogin Return value 3.
---------Actual Log File-----------
Starting custom action UpgDetectBlankSaLogin()
Entering Function MyMsiEvaluateCondition
MsiEvaluateCondition for VersionNT returned TRUE
End Function MyMsiEvaluateCondition
An error was encountered during connection to server: 3
Ending custom action UpgDetectBlankSaLogin().
Action ended 16:20:07: UpgDetectBlankSaLogin.2D02443E_7002_4C0B_ABC9_EAB2C064397B. Return value 3.
Action ended 16:20:07: INSTALL. Return value 3.
----------END Actual Log file----------

The sa password is definitely not blank, no matter how much the sa password was changed or it's complexity it always failed on this.

Creativity is needed to get around this, enter the windows software development kit (SDK) and Orca.  Orca is an MSI Editor, and is found in the Windows SDK.  The SP4 Service pack contains the MSI Files, and I decided to remove the step that runs the UpgDetectBlankSaLogin.    Simply find the step in the MSI File (as shown below) and Delete (drop) the row associated with UpgDetectBlankSaLogin.  Save and use it !

Now this is a calculated risk and requires testing.  But it works, and on everyone of these installations that was failing the now modified SP4 runs fine.

This MSDE task took 3 days of my life away from me, hopefully you'll be more fortunate!

MSDE is on life support, and so is this solution, but if you can't solve the problem, go around the problem.

Remember this means buying it and paying maintenance.  SSIS is a great tool, even if you did have to buy it, but because it's free and bundled with SQL Server 2005/8, we rarely look at other ETL Tools with an open mind.  Recently my list of SSIS Pet Peeves and the Toughest SQL Server 2005 Migration ever, caused me to wonder, "Would I use SSIS if it wasn't free and bundled with SQL Server?"

I know the company i work did a RFP for ETL Tools, but I was never privy to the responses or even allowed to participate in the process ( why include DBA's in the search for an enterprise ETL Tool, that'd be foolish!).  Nothing ever came from this, so that leads me to think that SSIS must still be the best value out there, probably because it's free.  I sure would have liked to seen the responses, especially on cost.

I've found a nice web page on ETL Tools, though they want you to purchase their comparison, but if nothing else you can see a nice list of ETL Vendors:

http://www.etltool.com/etltoolslist.htm

No. List of ETL Tools Version   ETL Vendors
1. Oracle Warehouse Builder (OWB) 11gR1 Oracle  
2. Data Integrator & Services  XI 3.0 Business Objects, SAP
3. IBM Information Server (Ascential) 8.0.1 IBM
4. SAS Data Integration Studio 4.2 SAS Institute 
5. PowerCenter 8.5.1 Informatica  
6. Elixir Repertoire 7.2.2 Elixir 
7. Data Migrator 7.6 Information Builders
8. Integration Services (SSIS) 10 Microsoft  
9. Talend Open Studio 1.1 Talend
10. DataFlow Manager 6 Group 1 Software (Sagent)
11. Data Integrator 8.12 Pervasive
12. Transformation Server 5.4 IBM DataMirror
13. Transformation Manager  5.2.2 ETL Solutions Ltd.
14. Data Manager/Decision Stream 8.2 IBM Cognos
15. DT/Studio 3.1 Embarcadero Technologies
16. ETL4ALL 4.2 IKAN
17. DB2 Warehouse Edition 9.1 IBM
18. Pentaho Data Integration  3.0 Pentaho  
19. Adeptia Integration Server 4.9 Adeptia
20. Clover ETL 2.5.2 Javlin  

Interesting Blog post on evaluating some ETL Tools:
http://it.toolbox.com/blogs/evaluating-software/evaluate-etl-tools-4169

If anyone has any experiences with other ETL Tools and how they compare with SSIS, i'd love to have you comment on this, letting me know why your using a different tool.  After working with SSIS now on several large projects I'm now very comfortable with SSIS and understand it's quirks, depending on it's "cost" compared to other products, I'd use it, even if it wasn't free.  I hope Microsoft doesn't read this and decide they want to start charging for ssis (and for sure there are already some features of SSIS that aren't available under certain editions).

One of the greatest strengths of SSIS is how quickly we (DBA, User, Developer, BA, QA Etc) can get at the data either for query, analysis, import or export...SSIS and it's predecessor DTS were one of SQL Server's greatest strengths in this area.  I work in a shop with other DBMS's and consistently SQL Server is more flexible in this area, because of SSIS/DTS.  I do think they could have made SSIS Better than it is, the learning curve compared to DTS is steep and the strong data typing of the meta data can be difficult at best.

Recently I finished my first deep dive into using SQL Server Integration Services (SSIS) on a project.  This was the conversion of 88 fairly complex DTS Packages from SQL 2000 to 2005.  I've used SSIS numerous times in the past, but I still considered myself at the "beginner" level, I'd say i'm almost "Intermediate" now that I've completed this project. 

All of our SSIS Packages for this project were implemented on a separate layer from the dbms, ultimately allowing application administrators the ability to control the jobs (via windows task scheduler).   This is one of the greatest benefits of SSIS is the ability to separate them from SQL Server.  The three cons to this are loss of SQL Agent for scheduling,  SQL Licensing and Performance of some tasks (can't use SQL Destination).   The pro's out weigh these con's, and in the very few instances where a con can't be overcome we do allow the ssis package to run on the same layer (usually large loads from Oracle to SQL where the SQL Destination object must be used for performance). 

It is definitely nice to allow the administrator unfettered access to the box and they are completely responsible for developing, deploying, configuring and scheduling their own SSIS Packages, not the SQL DBA Team.  We have 4 SQL Server DBA's, 170 Instances; there are a lot more App/Dev labor hours than their our SQL DBA labor hours.  The App/Dev team loves it too, it takes them a bit to get used to it, but everyone of them loves it.  Make sure you have good SLA's as DBA's still need to understand when large loads and complex ETL's will run against a database.

During the process I compiled a list of "pet peeves" for SSIS, here they are.  Hopefully as we adopt SQL 2008 SSIS, i'll be able to cross some of these off.

• Excel (the list of issues seems endless)
• Columns longer than 500 Characters
• Strong data typing (Data conversion on 50+ columns is brutal!)
• Mapping columns after data conversion, ugh !
• Using variables for dynamic file names is not intuitive
• This is timestamps in filenames etc, once learned it's not difficult, but for others viewing the package the first time it can be convoluted to determine how the variables are used to manipulate the name of the file source
• Using wild cards for FTP (either put or get) is not intuitive
• Converting some previous DTS Tasks would perform a put (mput) of {timestamp}.*, this ended up either remaining a batch file in ssis or creating a for loop that actually performed individual puts (really not smart to login 10 times for 10 puts).
• Mapping columns in data flow tasks
• If the names don't match there is no option to just do a 1:1 in order match, frustrating and tedious when your dealing with 50 or a 100 columns to perform manually
• Configuration Files
• A must to implement, but just seems to have a high learning curve
• Restart of a failed packages (checkpoints)
• Just didn't work the way we expected, but once learned was quite successfully
• Oracle Integration
• The pain here was and is endless, 3rd party tool is really necessary if your dealing with lots of integration
• Default Properties for Fail Parent and Fail Package
• For us it seemed we always wanted this to true and the default was false, there seemed to be no way to globally change this
• Precedence Constraint Editor does not contain an expression builder
• Oh how I missed the 3 ellipses
• Executing another SSIS Package from an existing SSIS Package
• We have several "shared" packages, but we weren't able to use the execute package task due to limitations on changing the properties, so we ended up using the Execute process task, which calls a batch file with the proper configuration file...ultimately the execute package task was useless
• Readability of Configuration files makes editing difficult
• We end up using XML Notepad on most servers, it'd be nice to see the property name as part of the node or an element so one could visually scan down the side and find things quicker.

We're still not ready to support sql 2008 (Company Infrastructure, vendors and DBA Group are a little slow), maybe this June or July we'll do the first sql 2008 implementation).

For me, most SQL Server migrations have been easy.  No mess, no fuss, hit the "easy button".  We even do the migrations the "hard" way...new servers, named instance, usually to a new clustered or consolidated environment using Polyserve or VM, and we separate all DTS (ssis) and User jobs out to an application (ETL) server.  As a DBA with 100's of servers it's a lot easier to let each application group manage their own jobs, off the sql server, than for us to handle the requests.  Usually the most difficult issue is connectivity, firewall and connection strings.  Of 170 instances we support very rarely is there a difficult issue, we've dealt with some odd performance issues here and there, but for the most part it's been smooth sailing (hah, I remember being up for several days straight monitoring a system with 5K tps and watching it crash every day, but that's the extreme end of the bell curve).

Finally we couldn't put it off any more, and it was time to tackle the elephant in the room. 

It's not a big Instance (5 databases, 60gb), but the challenges are big.  We're dealing with 88 DTS Packages and 45 sql agent jobs, Connectivity to multiple Oracle databases, AS400 and other SQL Servers, 60+ downstream consumers of data.  Excel Flat Files, Text Files, Web services, 3rd party vendor imports and exports.  Let the games begin.

I knew I was screwed when I de-attached the databases from SQL 2000 and attached them to sql 2005 and got the following errors:  "Attach database failed for Server x.  An exception occurred while executing a T-SQL Statement or batch.  Converting database x from version 539 to 611.  Microsoft SQL Server, Error 195. fn_convertdatetoint_notime is not a recognized function name.  Incorrect syntax near the keyword 'left'".  Ultimately we determined that this issues was caused by user functions in SQL 2000 setup in the master database, NOT Supported in sql 2005.  We also ran into isolated issues where there were tsql syntax errors in sql 2000 that did not throw any errors, but would not compile in 2005!

Linked Server Issues were also encountered.
   Setting up a linked server to the AS400 on 64 Bit windows
      Required a new version of the IBM iSeries driver
      Required a patch to the IBM iseries driver
      Required patching windows to install 64 bit odbc drivers
   Setting up a Linked server to SQL 2000 SP4
      Required patching sql 2000, http://support.microsoft.com/kb/906954
   Setting up a linked server to Oracle

Over-all the process took about a month (with 4 DBA's and other work), bulk of the work was the DTS to SSIS packages.  We did look at some 3rd party utilities, but they still left several hours work for each package, so we just did the brute force method and did them one at a time.  Currently the process will spend 2-3 months running in parallel and being QA'd, than onto production.

Hit the "next" button.

Recently had to perform an update to some VBS Scripts I wrote in the past to archive and delete logs.  (Previous post: http://www.lifeasbob.com/2008/02/26/SQLServerLogManagement.aspx)  The scripts I've used in the past automatically move all files in a specific directory to an archive directory, and then delete the archived directories that are older than 30 days.  This was nice, but most of the directory names and the number of days were hard coded in the script, in addition it did not use wild cards (*.txt) to move the files, so it was all files or nothing.

I updated the VBS Scripts to take wild cards and directories (both source and target) as parameters.   The annoying thing is I implement these jobs via windows task scheduler (no sql agent on the box), and in order to call the vb scripts with parameters I ended up using batch files as I had multiple iterations of different wildcards and directories, guess we haven't come very far after all!

Anyway you may find this stuff useful for things where someone creates a process which generates lots and lots of files of any type.  In my case these were about 10 files per day, exported to excel, and we needed to archive them to different sub-directories based on the wild card.  What you end up with looks like this in Task Scheduler and File exploder...er, i mean explorer...

I've uploaded the examples of the vbs and bat files below, all have been renamed to have a .txt extension, so rename them after download.

Archive_Daily_logs_bat.txt (.6 KB) 
Archive_Daily_Logs_vbs.txt (4.95 KB)
Archive_Delete_Dirs_bat.txt (.36 KB)
Archive_Delete_Dirs_vbs.txt (.75 KB)

For the powershell Evangelist's, yes this can all be done in powershell, but since I only have powershell installed on 1 of 170 servers, it will have to wait.  Eventually i'm sure we will push powershell to 170 servers.  Soon i'll be creating a new powershell category and begin uploading and converting all these.

Recently I ran into an issue where I needed to edit a large number of dtsConfig xml files.  I thought there was an option in the windows operating system to perform a find and replace across multiple files, but alas I can't find it...

Than I remember a vb script I had hobbled together from several other people to do this.  There are several share ware, free ware and other programs to do this, but I really think that find and replace is so basic of a task that it should be easy to kick out...

So here it is, remember that vbs files are a security issue when downloading from the internet, so i've uploaded them with a .txt extension.  Rename it to .vbs.  There are two programs, a replace.vbs and Replace_in_All_files.vbs.  The latter is the one that you must edit with the directory to look for files, extension of files to search and the find and replace string.

Use at your own risk.  The program will make a backup of the file.  VB Script is not the most efficient find and replace program for very large files, but if you have 100 or so dtsConfig files to edit, it works like a charm !

The same type of program can be done in a Power Shell script, and it is the cool new buzz language, but since it is only installed on 1 out 170 servers we support, it's not quite useful yet....i'll post the power shell script later.

replace.txt (1.76 KB)

Replace_in_All_files.txt (1.5 KB)

Recently had a need to ensure that some tables were indexed in a particular order, mostly due to a very small maintenance window.  I did not want to create any supporting tables to do this, so we quickly modified the reindex script to include a priority and list the tables in the query.  If there were more than just a handful, our thought was to use the extended properties of each table to add a priority, and then order by that priority for the reindexing.

The quick way for a few tables was just changing the select statement to:

            
              SELECT table_name,
table_schema, Casewhen table_name in ('Client','conflict') Then 1
    When table_name in ('appointment') then 2 Else 3 EndAs Priority FROM information_schema.tables
a WHERE a.table_type
= 'BASE
TABLE'AND a.table_name
<> 'dtproperties'ANDNOT (a.table_name LIKE'tmp%') ORDERBY Priority,table_name 
          

CreateTable BobTest
(bobTestId intidentity(1,1) primarykey,
    bob_description varchar(100)) CreateTable z_ReindexFirst
(testid intidentity(1,1) primarykey) EXEC sys.sp_addextendedproperty
@name=N'ReindexPriority',
@value=N'1' ,
     @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',
     @level1name=N'BobTest'EXEC sys.sp_addextendedproperty
@name=N'ReindexPriority',
@value=N'2' ,
     @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',
     @level1name=N'z_ReindexFirst'SELECTCAST(e.name ASVARCHAR(1000)) As ext_prop_name, Coalesce(CAST(e.value ASVARCHAR(1000)),0) As ext_prop_value,
        t.name FROM sys.tables AS t LEFTOUTERJOIN sys.extended_properties AS e
                        ON t.[object_id]
= e.major_id                         AND e.minor_id
= 0      WHERE e.name isnullor e.name='ReindexPriority'ORDERBY e.value desc,t.name droptable bobTest droptable z_ReindexFirst

SSRS - Reporting Services Subscriptions not sending

Recently we ran into an issue with a Reporting Service Instance that was not sending any email subscriptions.  We could find no errors or obvious issues.  Of course a quick reboot solved the problem, but only temporarily, the next day, the issue was right back.  Time to call out for help, Microsoft Support.

Microsoft Support found the problem and provided us some useful scripts.  Basically the issue was that someone had created a snapshot, that ran every night.  The snapshot ran for 12+ hours, causing performance issues and other problems with all other subscriptions.  This is a "shared" reporting service instance, set up for several application groups.  The scripts from Microsoft were quite useful, i've listed them here, all of them are in my Script Vault.

I found the scripts very useful, as it addresses one of my continual issues, which is addressing capacity in shared environments.  For our Shared Reporting Service Environment, we create a Directory / Folder for each group and tweak permissions to keep each application separated, than we turn over full permissions to each sub-folder to the application group. 

Obviously the danger here is that someone can create a poorly performing report that effects the entire environment.  The scripts included below can help identify which reports run the longest.  We then modified this query a bit more to roll-up the report times, by sub-directory, so we can determine which application is using the most capacity in the environment.  We can then engage this group to optimize their reports or began capacity planning to build them a dedicated environment.

Here is a script listing of what I added to the Script Vault

1.  Find What User runs What Report - (Rpt Svc - Who Runs What)
2.  Find what subscriptions have been run and when - (Rpt Svc - Subscr Run When)
3.  Find what SQL Agent Schedule goes with what Report (Rpt Svc - SQL Agent to Report)
4.  Average Report RunTime - (Rpt Svc - Avg Rpt Runtime)
5.  Average Report RunTime Per Parent Folder - (Rpt Svc - Avg RunTime Pnt Fld)

We then used the last two queries to develop a Reporting Services reports that is available to the administrators of each directory, so they can manage their own performance and capacity, thanks to Tom Reeves,  for developing the last two.

Not sure if I just work for the most screwed up company in the world, or if I've just had a long run where I only seem to work for screwed up companies.  Maybe i'm drawn to them in some sort technology sociopath mental illness issue.  I was once told that, ""People with Mental Illness Enrich Our Lives".   If the same applies to a particular companies technology, than i'm enriched a lot!  To the problem...

The infinite wisdom of the powers that be of my company changed the domain\user login from domain\{first initials}{lastname} to a "generic number".  I know there are a lot of "smiths" and "guptas", so the original naming convention was not perfect either, but it worked for 80% of the employees.  The generic number is very frustrating for SQL Server DBA's.

The fun of verifying users for SOX Audits, just became a joy on par with cleaning toilets and replacing sewer lines, no longer easy enough to just run a query to get user's and permissions, now you need to translate the cryptic AD Login, so that management can verify each user....hmmm...thankyou sir, may I have another?  Imagine looking at sp_who2, or Activity monitor, or whatever query you use to view who is connected to your sql server and seeing, {domain}\x111111 as the user who is running a table scan against every table in the most important database you have, and the user was even nice enough to put a table lock hint on the query, nice! 

Now go find x111111 so you can ring his bell, or revoke their access and maybe they'll call you.  The x111111 is useless to you, at least the other AD login convention was useful most of the time, now it's completely useless.  Not only does it frustrate me on troubleshooting problems, but also adding new users.   Even though we modified the ticket system to ask for the login, invariably a significant number of tickets don't include the login name, and we have to bounce tickets around until we can find the users new cryptic AD Login.

To resolve this I had to write a utility that queries active directory and looks up the id of x111111 and returns their name.  I've included the two scripts (vbs - so rename them), one does a lookup by name (so horkay returns a553542) or by id (so a553542 returns Robert Horkay).  We also incorporated them into a web page, so that we could quickly and easily run these queries against Active Directory - (don't forget that your web.config will need to impersonate an identity so that you can query active directory). 

Next I'm going to have to write a custom version of the Activity Monitor, that uses the CLR to translate the cryptic Active Directory Login to a usable name, so that we don't have to bounce between management studio and a web page.

Trouble shooting performance issues is one of the real differences between an operational DBA (what we used to call Jr. DBA's) and a Sr. DBA.   

Even with all the new tools, there is always that support call....It's been working fine for the past 5 months, and today the application is performing poorly, and of course it's a database issue.  At this point i'm usually biting my lip and silently protesting, but often they are right, so it's time to dig into it.

What's the error ?  Answer:  We don't know

What's the issue ? Answer:  We don't know, but it's slow.

These issues are particularly troublesome.  Poorly written error handlers in the application, code developed by off-site vendors that support personnel don't understand, we've all seen the issues...AND now it's your job to fix it!

We have so many more tools now with SQL Server 2005 and 2008, that it becomes important to know which tool to use when, so the problem can be solved quickly.  I even have a PDF document, "Troubleshooting SQL Server 2005/2008 Query Performance/Scalability Issues" that we used our plotter printer on 3x4 paper just to help.

I start in with profiler, as I want to try and see if there are any client side errors, since it seems that the applicaiton is not performing any error handling or trapping any errors.  Sure enough we find an error on violation of a unique index, PUNT, back to the application team for more analysis; but why after 5 months would this just now come up (of course there have been no changes.) 

We finally find an issue with a stored procedure that seems to be hanging or timing out and then progressing to the next step in the application causing the unique index error, since we aren't trapping for errors on the client side and profiler can't find client side time-outs, it was particulary bothersome to find.  Even the procedure seems ok when executed through management studio and returns in sub-seconds, but something happens when the application runs it (still haven't figured this out).

Finally determine there is a degree of parallelism issue (DOP), which we found through analyzing the wait types.  The wait types was not on our first list of items to check.  Changing the DOP from 0 to 2, immediatly resolved the issue and dropped the CPU from 40% to 15%.  Utlimately some indexing would help the query, but we can't build indexes on the fly as the load is so heavy that even a table lock for 10-15 seconds would generate 100's of support calls; DOP can be done on the fly with little effect.

Analysis of the query plans shows that as the data grew the DOP changed the query plan and began to cause issues with a wait type of CXPACKET. 

This is why we still need real DBA's.

Most requests we can find a way to solve easily, sql server 2005 finally began to give us great insight into what was happenning with DMV's, 2008 expanded on this....  But every so often those requests come along that I still can't solve.

Application team is requesting to know the execution count of a particular stored procedure by hour, from 3 days ago !

I have no solution.

It may be possible to "tap" the default trace and get a count of the procedure and roll it into a table, which may help going forward, but not for the past.  The volume of data generated could be quite large, as this particular server is performing 5,000 tps on average.

It was explained to me that they are trying to figure out this count as it represents a session timeout from the login page, and that specific counts from the iis log on the hits to the logon page do not accurately reflect those that had a session timeout vs. those that logged on again, hence the desire to know the counts by hour of the stored procedure used to login.

No help from the DBA on that one, any suggestions are welcomed...

Mountpoints are fun and easily can solve issues with more than 26 drive letters (don't ask), but recently we ran into several issues with monitoring the mountpoints.  In particular we have a report that is based on past growth history of a database, disk size and disk free space, and it estimates when a drive will be at 80% capacity and when it will fill up.   With 100's of database servers this report can prioritize and pro-actively identify which server will encounter a problem next and when.  Of course the limitation is in bold, drive. 

Report Example:

When we began using Mountpoints the report was not as accurate and it needed to be adjusted.  We have some internal services that collect the drive size and free space to a central DBA database.  Review of this monitoring reveals it is using a WMI Query, a quick review of the WMI SDK shows another call that will pick up mountpoints, Select * from WIN32_Volume.  Life is good.

Not so quick after hurdling from drives to drives and mountpoints, a problem was revealed where the WMI call failed on two servers.  Englightenment.  These two sql servers have also been giving us odd issues with SQL Management Studio (SSMS), which is highly dependent on WMI, SMO, .NET and probably some other stuff.  Fixing WMI on these two servers fixed the issue and corrected the SSMS issues.

Steps to fix WMI (Thanks to our Windows Team for the steps below):

Here is a short quick Visual Basic Script (VBS) you can save to a text file with a .VBS Extension to see the call to WMI to check disk space for mount points or drives.  It filters out certain mount points for Polyserve as we don't want to monitor those, also for some reason z:\ is mapped in our environment and this wmi script pulls that with nulls, so you need to test for those.  You can also use Powershell, but it is using a wmi call underneath the hood as well, and we have yet to install powershell on all our servers.

          
            Set DiskSet =GetObject("winmgmts:{impersonationLevel=impersonate}!//BCCMAPP02")_
    .ExecQuery("Select
* from Win32_Volume") ForEach objItem In DiskSet
    Ignore =False     iflen(objitem.name)
>= 51 then         IfUCase(Left(objitem.name,51)) = _
            UCase("C:\Program
Files\PolyServe\MatrixServer\conf\mounts") Then             Ignore =True         EndIf     EndIf     If Ignore =FalseThen         msgbox(objItem.Name
& vbCrLf & "Percent
Free = " & _             round((objItem.freespace/objItem.Capacity)*100,2)
& _             "
= " & objItem.freeSpace & "
= " & objItem.Capacity)     EndIfNext
        

The server could not load DCOM. [SQLSTATE 42000] (Error 7404).  The step failed.

Began receiving this error from failed sql server jobs on a sql server 2000 box and a windows 2000 box, after a reboot.

We didn't spend too much time researching the issue, as we were still in a maintenance window, so we performed another reboot and things worked fine.

I've seen many dcom errors over the years, but not this particular one from SQL Server, most likely com+ or ms dtc sub-systems (services) did not start or failed to start on the reboot.  There were no system or event log entries that were immediately helpful, though there were other odd entries that seemed related to dcom not working:

Altering a table column from varchar to datetime is pretty straight-forward in the SQL Server Management Studio (SSMS), until you look at the tsql generated.  For many operations SSMS will generate a tsql script that will:

• create a temporary table
• drop all the foreign keys
• copy the data to the temporary table
• create the new table with the correct data type
• copy the data to the new table
• drop the temporary table
• add the foreign keys back

That is a lot of operations and on a really large table of millions of rows may take a very long time to complete.

SQLCricket comments that it is possible to change the options in SSMS to warn on table operations, i think this is only in sql 2K8.

SQLPuma comments that it is possible to change a varchar() to a datetime via tsql with an alter table alter column command.

In this particular case we were modifying a varchar(10) to a datetime.  All the data was in a valid format.  The easiest method is to alter the column, another method to complete this is to:

• rename the existing column (tmp_varchar etc)
• add a new column with the correct name
• update the new column (in batches if necessary)
• drop the original column (now with a tmp_name)

This is very quick, much safer operation and is much "nicer" to the database log file.  Example of tsql is below:

          
            CREATE
            TABLE dbo.testing
(test_id bigintNOTNULLIDENTITY (1,
1) primarykey,
some_dt varchar(10)
) GO insertinto testing
(some_dt) values ('2009-01-01') insertinto testing
(some_dt) values ('2009-01-02') insertinto testing
(some_dt) values ('2009-01-03') insertinto testing
(some_dt) values ('2009-01-04')
go EXECUTE sp_rename 'dbo.testing.some_dt',
    'Tmp_some_dt', 'COLUMN' GO AlterTable testing Add some_dt DateTimeDefault('1900-01-01') NOTNULL GO Update testing
    Set some_dt
= Convert(DateTime,Tmp_some_dt)
GO AlterTable testing
    DROPColumn Tmp_some_dt
GO select * from testing
go --drop
table testing
        

I twisted my head around a select statement that wasn't returning what I wanted.  Eventually I focused in on the column list, that had column names with spaces, so it required using brackets [].  These brackets when mixed in the column list can lead to tsql parsing that is not expected.  In this case I mixed some columns with brackets and some without, ultimately leaving a comma off.

You'd expect the tsql to not compile, but it does and runs with no errors.  Once tsql encounters a column list with brackets, it parses based off the brackets and commas ignoring the additional field i wanted to include in the select list..

grrrr...

          
            

            create
            table #testTable
    (tableid intidentity(1,1) primarykey,
        [login name] varchar(20),
        [is_password_validation] int,
        [is_password_expiration] int,
        [login type] varchar(20)) Insertinto #testTable
([login name],[is_password_validation],     [is_password_expiration],[login
type] ) values ('User
1',0,0,'AD') Insertinto #testTable
([login name],[is_password_validation],     [is_password_expiration],[login
type] ) values ('User
2',0,0,'AD') Insertinto #testTable
([login name],[is_password_validation],     [is_password_expiration],[login
type] ) values ('User
3',0,0,'AD') Insertinto #testTable
([login name],[is_password_validation],     [is_password_expiration],[login
type] ) values ('User
4',1,1,'SQL') Insertinto #testTable
([login name],[is_password_validation],     [is_password_expiration],[login
type] ) values ('User
5',0,1,'SQL') select * from #testTable --
goodselect [login
name],     is_password_validation --
NOTICE THE MISSING COMMA     [login type]     From #testTable
        

UPDATE - 01/29/2009 - I just received a solution to this issue, that does not require a temp table, which was my wimpy solution, thanks to Shawn who left this great solution in the comments:

          
            Select * From #checkDates whereconvert(datetime,caseisdate(rptDate) when 1 then rptDate elsenullend) Between'02/27/2009'and'02/28/2009'
        

The isdate() function does not work properly (or at least as I expect it would) when it is used inside a derived table or a Common Table Expression.  This is really frustruating as it changes the way I think of derived tables and CTE's.  I always assumed that the inner query or CTE was resolved first, but if you follow the TSQL below, you can see that is not the case.

This may affect other functions, isnumeric() or even custom functions, so test them carefully.

I was working on a report issue that was generating an invalid derived date from a varchar, 02/29/2009.  Very simple to filter out, but due to the way the where clause is resolved / short circuited in tsql, it was going to be necessary to use a derived table or a CTE, but neither would work.  I ended up resorting to using an additional temporary table (wimpy, wimpy), I don't like this solution and would like to find one that does not require the temporary table.

Here is the problem:

          
            create
            table #checkDates
    (chkDtId intidentity(1,1) primarykey,
        rptDate varchar(10)) Insertinto #checkDates
(rptDate) values ('02/26/2009') Insertinto #checkDates
(rptDate) values ('02/27/2009') Insertinto #checkDates
(rptDate) values ('02/28/2009') Insertinto #checkDates
(rptDate) values ('02/29/2009') --
This fails below with invalid dateSelect *
    From #checkDates
    whereconvert(datetime,rptDate) Between         '02/27/2009'and'02/28/2009'--
put the isdate function as part of where clause--
select statement still failsSelect *
    From #checkDates
    whereisdate(rptDate)
= 1 and         convert(datetime,rptDate) Between         '02/27/2009'and'02/28/2009'--
switch to a derived table, the thought being to--
remove the invalid dates from the inner select --
somehow in my mind i think the inner query should be resolved first,--
but this is not the case !Select * from     ( Select *
    From #checkDates
    whereisdate(rptDate)
= 1 ) x     whereconvert(datetime,rptDate) Between         '02/27/2009'and'02/28/2009'--
wimpy solution is to select the "good" records into --
a temp table and then perform the next select, yuck--
BUT IT WORKSSelect * into #InterimResults
    From #checkDates
    whereisdate(rptDate)
= 1 Select * from #InterimResults
    whereconvert(datetime,rptDate) Between         '02/27/2009'and'02/28/2009'----
CTE SOLUTION, DOES NOT WORK EITHER --WITH goodDate_CTE AS (
    Select *
    From #checkDates
    whereisdate(rptDate)
= 1 ) SELECT * FROM goodDate_CTE whereconvert(datetime,rptDate) Between         '02/27/2009'and'02/28/2009'
        

Recently ran into an issue with a linked server between sql server and an as400, using the iSeries access driver via ODBC. 

The linked server was not returning all the rows and it was not throwing any errors either.

There is an advanced settings option on the iSeries as400 setup that needs to be "unchecked", by default the option is checked and can lead to missing records.

Once it is unchecked, the query returns the correct counts or rows.

This is a custom report developed to list user permissions.

Often DBA's need a consolidated list of permissions for a user, this custom report does this.  Developed by Tom Reeves.

Just a quick placeholder on the progress of SQL Server 2005 service pack 3.

This update is important, if nothing else to ensure your on the latest patch which addresses some security vulnerabilities.

There is also a cumulative update for service pack 3, generally I don't go down the cumulative update path unless i've been advise by Microsoft (ie, it addresses a problem being experienced) or the SQL Community in general highly recommends a specific CU.

I always start with patching my local developer edition, x86 Instance.

Slowly working through a few boxes, so far we've patched the following with no issues:

• 64 Bit named instances running under Polyserve 3.6.1 - both dev, qa and production, no issues, Enterprise Edition.
• 64 Bit named instance stand-alone server, Enterprise Edition.
• 64 Bit named instance stand-alone server, Standard Edition.
• 64 Bit Clustered SQL Server, Active / Active Configuration, no issues, did require reboot of both nodes and fail over, Enterprise Edition.
• 32 Bit SSIS stand alone installations (no dbms) no issues.
• 32 Bit Reporting Services Web Farm, authentication issues afterwards.

I had an issue with the Reporting Services Web Farm, which uses SQL Authentication for connectivity, caused the login to be disabled.  I had quite a difficult time getting it re-enabled and changing the password, but no errors in applying the service pack.

I've got a development box, old dev box, windows 2000, sql 2000, that runs a restore script daily.   This Restore script is a vbs file, it restores a file ftp'd from the production box.  It has run for many years with no issues, now every 3-4 days it hangs and runs forever.  So far the only solution has been to reboot the box.

I've dug around a bit and found many instances (10, 15, sometimes 20+) of dw20.exe running in the process / task list.

This is listed as the windows error reporting tool.  Not sure what is erroring or being reported on, but since this server is shared with devlopers who have local admin access to the server, i'd bet someone did something that than began causing this error...either configuration changes or installed some software, difficult to determine.

So far I've only found a two easy solutions, put the box on a daily reboot, or disable the windows error reporting tool.

I'm going to try and disable the tool and see what happens:

http://support.microsoft.com/kb/841477 

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

To disable the Application Error Reporting tool, add a DWReportee value of 1 to following registry keys:

I've always wanted the ability to see the modified date (alter procedured) of a stored procedure.

Recently "SQL Cricket" - Rick Mcintosh, showed me a Dynamic Management View (DMV) that shows you the modified date of a stored procedure.  In SQL Server 2008 you can add a column in object explorer to see this (date modified), though in sql server 2005 you can not add columns in object explorer, but the DMV works in sql 2k5.  Too bad it doesn't work for all other objects, like tables.

So get your SQL2K8 Management studio up and running, it provides more information, even for sql server 2005.

DMV is below.  Thanks to "SQL Cricket".

An election has come and gone.

SQL PASS is completed.

SQL Server 2008 is out.

It's time to prepare for the "State of SQL Server".  This was an idea of mine, to create a report and corresponding presentation to Senior leaders of my company on the "State of SQL Server", analogous to the Presidents State of the Union Address. 

It won't be in-depth as the Presidents address, but my job isn't either !

The goal of the report is to inform the Senior leaders of where the company is with SQL Server and where we are going with SQL Server, bringing together the knowledge from PASS and our unique knowledge of working with SQL Server at our company.  It also provides an opportunity to talk about where Microsoft is going with SQL Server and how our strategies are aligning or differing from that.

As DBA's we have the choice, we can present this information, or you can wait for the Microsoft Technincal Account manager (TAM) or sales representative to talk about.  I prefer to have these conversations with the Senior leaders before Microsoft does.

I can not share the report as it is proprietary.

An outline might be:

I.  SQL Server today

   A.  Provide an Overview of the environment

   B.  Used for What

II.  SQL Server 6-12 months

III.  SQL Server - The business intelligence platform

IV.  Other DBMS's