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:

http://support.microsoft.com/kb/974648/en-us

I'm working on creating a robust login auditing system for sql server 2005 and 2008.  Part of that process is scraping the error log for Error 18456 to keep counts and provide reports on failing logins.  Works great, but it depends on the two line entry in the error log of the following:

2009-10-01 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.

2009-10-01 00:02:00.34 Logon     Login failed for user '<user name>'. [CLIENT: <ip address>]

Suddenly the process fails today, which is ok, that's why we test, test and soak test.

Seems it is possible to generate a 18456 error that is not followed by login failed for user message.

Good coding practices would have avoided an error (by checking to ensure the next line did contain a login failed message).  We were working on the assumption that the two entries always happen together.  Not so.  Kind of makes me wonder where it went!

This happened on a SQL Server 2005, EE, 64 Bit, SP3, CU 4.

The specific entry is below:

Error: 18456, Severity: 14, State: 16.
CHECKDB for database '<removed>' finished without errors on 2009-10-01 04:05:02.370 (local time). This is an informational message only; no user action is required.

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

WHERE 'thomas' in(Firstname,LastName,LEFT(emailaddress,7))

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.

Recently we upgraded to a new version of HP SIM (systems insight manager), of course not testing it or letting the DBA's know.  Suddenly some things crash.  The new version of HP Sim provides a "richer discovery model"; oh it's rich!

Seems the new version performs some type of scan on the scsi bus, which causes our multi-path software (EMC Powerpath) to loose connectivity to the SAN, this causes the file system to "Panic", and all filesystems unmount.  Nice.

SIM is a hardware monitoring solution from HP for HP servers.  The server administration team loves it, the SQL DBA's don't mind it.  It of course uses SQL Server for a back-end database, so it helps keep us employed as well !  Basically SIM provides hardware inventory and monitoring of the servers.

Specifically: 

Version:  Systems Insight Manager 5.3 with SP1 - Windows
Build version:  C.05.03.01.00 

Using the Manual Discovery Task that ships with the product.

The issue was most notable with our clustered servers, especially Polyserve.  Below were the error messages:

Work closely with your administrators and be careful of how these monitoring solutions will affect your production servers.

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,
 Case when table_name in ('Client','conflict') Then 1
    When table_name in ('appointment') then 2
  Else 3 End As Priority
FROM information_schema.tables a
WHERE a.table_type = 'BASE TABLE'
 AND a.table_name <> 'dtproperties'
 AND NOT (a.table_name LIKE 'tmp%')
 ORDER BY Priority,table_name 

Create Table BobTest (bobTestId int identity(1,1) primary key,
    bob_description varchar(100))
Create Table z_ReindexFirst (testid int identity(1,1) primary key)

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'

SELECT CAST(e.name AS VARCHAR(1000)) As ext_prop_name, 
       Coalesce(CAST(e.value AS VARCHAR(1000)),0) As ext_prop_value,
        t.name
       FROM sys.tables AS t 
       LEFT OUTER JOIN sys.extended_properties AS e 
                        ON t.[object_id] = e.major_id 
                        AND e.minor_id = 0
       WHERE e.name is null or e.name='ReindexPriority'
ORDER BY e.value desc,t.name

drop table bobTest
drop table 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")
For Each objItem In DiskSet
    Ignore = False
    if len(objitem.name) >= 51 then
        If UCase(Left(objitem.name,51)) = _
            UCase("C:\Program Files\PolyServe\MatrixServer\conf\mounts") Then
            Ignore = True
        End If
    End If

    If Ignore = False Then
        msgbox(objItem.Name & vbCrLf & "Percent Free = " & _
            round((objItem.freespace/objItem.Capacity)*100,2) & _
            " = " & objItem.freeSpace & " = " & objItem.Capacity)
    End If

Next

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 bigint NOT NULL IDENTITY (1, 1) primary key,
some_dt varchar(10)
)
GO

insert into testing (some_dt) values ('2009-01-01')
insert into testing (some_dt) values ('2009-01-02')
insert into testing (some_dt) values ('2009-01-03')
insert into testing (some_dt) values ('2009-01-04')
go

EXECUTE sp_rename 'dbo.testing.some_dt', 
    'Tmp_some_dt', 'COLUMN' 
GO

Alter Table testing
Add some_dt DateTime Default('1900-01-01') NOT NULL
GO

Update testing
    Set some_dt = Convert(DateTime,Tmp_some_dt)
GO

Alter Table testing
    DROP Column 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 int identity(1,1) primary key,
        [login name] varchar(20),
        [is_password_validation] int,
        [is_password_expiration] int,
        [login type] varchar(20))

Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 1',0,0,'AD')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 2',0,0,'AD')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 3',0,0,'AD')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 4',1,1,'SQL')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 5',0,1,'SQL')

select * from #testTable -- good

select [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
    where convert(datetime,case isdate(rptDate) when 1 then rptDate else null end)
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 int identity(1,1) primary key,
        rptDate varchar(10))

Insert into #checkDates (rptDate) values ('02/26/2009')
Insert into #checkDates (rptDate) values ('02/27/2009')
Insert into #checkDates (rptDate) values ('02/28/2009')
Insert into #checkDates (rptDate) values ('02/29/2009')

-- This fails below with invalid date
Select * 
    From #checkDates
    where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'

-- put the isdate function as part of where clause
-- select statement still fails
Select * 
    From #checkDates
    where isdate(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
    where isdate(rptDate) = 1 ) x
    where convert(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 WORKS
Select * into #InterimResults 
    From #checkDates
    where isdate(rptDate) = 1 

Select * from #InterimResults
    where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'
--
-- CTE SOLUTION, DOES NOT WORK EITHER 
--
WITH goodDate_CTE AS (
    Select * 
    From #checkDates
    where isdate(rptDate) = 1 
)
SELECT *
FROM goodDate_CTE
where convert(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.

User_Permissions.txt - Right click and save with a RDL Extension. (37.25 KB)

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".

SELECT name, create_date, modify_date

FROM sys.objects

WHERE type = 'P'

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

Recently I ran across an interesting problem where we had to kick off an SSIS Package on a remote server, i found the following to help with this:

I tried psexec and it's great. A thing of beauty. Simple, clean and it just works.

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

Here is a batch file that that uses it to execute an SSIS package on a remote server:

@echo off
echo Enter Username
set /p UserName=
psexec \\10.20.30.40 -u %UserName% -e dtexec /sql "\Maintenance Plans\SSIS_Package_Demo"
pause
exit

The only problem with psexec is that you have to run it using a username and password with full administrative rights on the operating system of the server where SQL Server 2005 and the SSIS packages reside.

That's not a minor problem in any type of secure envirnoment.

It was a deal-killer for us.

However, rexec is a similar utility that authenticates the user name on the remote computer before executing the specified command.

http://technet.microsoft.com/en-us/library/bb490989(TechNet.10).aspx#

The rexec client is included with Windows 2000/XP/Vista, but they do not come with a rexecd daemon to provide the service.

Winsock does have a 3rd party (non-free) rexecd Windows daemon, however...

http://www.denicomp.com/rexecdnt.htm

...and when you use it "programs are executed on the Windows system in the security context of the user specified in the rexec command."

Here is a command line that runs the same package:

rexec 10.20.30.40 -l username dtexec /sql "\Maintenance Plans\SSIS_Package_Demo"

There are no simple, secure ways to remotely execute SQL Server 2005 SSIS packages short of purchasing extra SQL Server licenses.

A remote execution service like rexecd is an absolute necessity for running SSIS packages. It's well worth the $44.95 license fee.

SSIS, Oracle, SQL and 64 BIT - NOT Perfect together !

Recently ran into an issue where we use SSIS to pull data from Oracle and issues with converting a Oracle Numeric data type to SQL Server.  This did not go well, final corrections seemed ok, until we deployed them.

There are several solutions to this problem, it seems that having the right drivers of course helps, and so does casting / converting the data while brining it in from Oracle. 

I found a great blog entry on this, i've copied it below (as great links often disappear), and also the link:

http://blogs.microsoft.co.il/blogs/bei/archive/2008/10/18/ssis-64bit-using-oracle-provider.aspx 

An often overlooked feature of SQL Server Enterprise Edition is support for advanced scanning, referred to as merry-go-round scans.  Often I'm asked about the differences between enteprise edition and standard, this is one that I often forget about this one.  Recently I was researching some information on read-consistency problems ( nolock etc) and came across this type of scan.  There are many resources about the differences, i've quoted some here below, and also list the reference.

From:

http://blogs.msdn.com/boduff/archive/2008/01/24/why-should-i-use-sql-enterprise-edition.aspx

http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=49285

There are some key enterprise edition only performance benefits across RAM, Parallelism, Query Plans and DISK I/O that will lead to better performance on high end systems, which I will try to list here.

1) Lock Pages in Memory

Lock Pages In Memory" allows SQL Server 2005 to manage its own memory (as opposed to having the operating system do it). It is extremely important to give this right to the SQL Server Service account, especially on 64-bit SQL Server 2005 systems with lots of RAM. It also is required on 32-bit systems to enable AWE.

See http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

2) Advanced Scanning (aka Merry-go-round scan)

In SQL Server Enterprise Edition, the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.

See http://msdn2.microsoft.com/en-us/library/ms191475.aspx and  Merry-Go-Round Culprits for performance variances

3) Larger Read Ahead Buffering on Storage Devices

Determining I/O section mentions that EE does up to 1024k read ahead buffering on a Storage Area Network (std only does 64k). This indicates that EE is more suitable to SAN’s which need more buffering due to increased latency.

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

4) Large Page Extensions

SQL Enterprise Edition retrieves pages up to eight at a go.

http://msdn2.microsoft.com/en-us/library/aa337525.aspx

5) Parallel index Operations

This is particularly useful in data warehouses where indexes may be frequently dropped and re-created.

http://msdn2.microsoft.com/en-us/library/ms189329.aspx

Error: 8646, Severity: 21, State: 1.
Unable to find index entry in index ID 1, of table 1877581727, in database 'db name here'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

Recently ran into a serious issue with SQL Server issueing the above message under load.  After shutting things down and running check db there was no corruption, but every time a particular stored procedure was executed under load, stack dumps would occur with the above message in the Error Log.

With the help of Microsoft support we isolate the issue to a particular delete statement that was being executed with a join to another table and a where clause.  The query was performing a hash join and there is some bug that was causing a problem.  Microsoft continues to research the issue, but recommended we use a query hint to force a merge join to resolve the problem.  Testing indicates that the problem has gone away.

Old Query:

DELETE FROM {child table}
FROM {child table} aq, {parent table} a
WHERE aq.id = a.id
AND ( a.status_id in (4, 5, 6, 7)
OR type_id = 3
OR a.match_id =2 
AND a.id%@p_in_threads = @p_in_thread_no

New Query

DELETE FROM {child table}
FROM {child table} aq, {parent table} a
WHERE aq.id = a.id
AND ( a.status_id in (4, 5, 6, 7)
OR type_id = 3
OR a.match_id =2 
AND a.id%@p_in_threads = @p_in_thread_no
option (merge join)

Been a long time since i've had to install and setup SQL Server on a clustered server that wasn't Polyserve.  Suddenly you've got 4 of them to do in one day.  And of course we run into a set of gotcha's...

1. Logged onto remote node
2. MSDTC not running as part of a cluster group
3. Network DTC not installed
4. Not Enough Disk Space
5. IRPStackSize too small

1.  Make sure no one else is logged on to the other cluster nodes

2 and 3.  DTC

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

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

4.  Not Enough Disk Space, this one was frustrating, as the error message is actually caused by the next issue, IRPStackSize:

These is not enough disk space on the destination disk for the current SQL Server data files. To proceed, free up disk space to make room for data files, or install the data files to a different drive.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2953409&SiteID=1 

http://sqlserver-qa.net/blogs/tools/archive/2008/01/04/error-there-is-not-enough-disk-space-on-the-destination-disk-during-a-sql-server-failover-cluster-installation.aspx

5.  IRPStackSize

http://blogs.technet.com/askcore/archive/2008/01/25/irpstacksize-and-clusters.aspx

http://support.microsoft.com/?id=285089

http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/regentry/58629.mspx?mfr=true

When you have a SQL Server instance in Polyserve that will not fail back to it's primary, you know you have a problem.  Best bet, call technincal support [that's what maintenance is for!], but here is what happenned to us recently and what we did to correct this issue [we have seen this before and called technical support and concurred this is the permanent fix].

Automated patching of the development environment caused some sql instances to fail over, this is expected.  We do not run the instances in "auto fail back" mode, preferring to complete this step manually to minimize "ping-ponging" instances.  After patching we reviewed the environment, and it looks good, with the excpetion of one instance, it is on it's secondary, it is running, it is available, but notice the status of "warning".

There is no nice error message in the console.  Right clicking the instance to "show alerts", displays nothing.  What gives ?

Who cares right, just move the instance back to the primary and let's get working....no dice.  The instance won't move, and NO Error or Message is given, crap, you know your in trouble now....for the newly Polyserve initiated, this is when you STOP and call technical support.  The more you play with things, the worse it will get and it will cause technincal support consternation in correcting the issue.  Since this was development I get to play...

The instance will not fail back, if we rehost, that result is no change.  Disabling the instance is the same result.  I'm pretty sure you could reboot the server and that would cause it to fail back to the primary, matter of fact, i'm positive....but what if you have other instances on that same physical server and you can't afford an outage ?  Also that may temporarily correct the issue, but it doesn't address the root cause and future scenarios of patching or fail over, may result in the same condition.

What the hell is happenning ?

Finally digging through all the logs (including Polyserve logs), i find that Polyserve still thinks the instance is "starting", and since it is in "starting mode", it does not fail it over when requested.  There needs to be someway of over-riding this stupid behavior but it is "by design".  See pic:

What and why ?  Based on past experience and knowing this instance existed from version 3.4 to 3.6, I know this deals with the registry issues.  A quick peak of the registry on the server it is currently hosted on shows that some of the registry entries point to the virtual root (3.6) and some of the registry entries point to mxshells (3.4), notice the registry entries below, they should all point to the virtual root:

Now the correct way to fix this is to delete the sql instance from Polyserve (not the machines).  Verify the registry entries and sql instance on each machine.  Delete any polyserve sql.original, sql.preg etc files (make a copy first).  Re-virtualize it and re-verify everything. 

Obviously if this is production instance, you might have to wait in doing this, as it is time consuming.  In which case you can manually stop the services and see if you can get things to fail back, though you may have to reboot the server.  At some point, the only way to correct the root cause is find a maintenance window to allow you to delete the instance from polyserve, correct each individual sql instance and re-virtualize.  Fun !

Moving blobs to a dedicated file group, maybe with several data files, is a very good idea !  Of course, if you wait to do this until your database grows quite large, you've got a problem.  I define large as 50+ gb.

Currently I have an issue with a 120gb database.  100gb of the data is in one table, with an image data type.   I'd like to isolate this tables blob data to a dedicated file group, made up of 4 data files.  This is not difficult, what is difficult is making it happen. 

I've experimented with doing this 3 ways:

1.  SQL Management Studio  - 28 hours.

2.  SSIS - 18 hours.

3.  Custom TSQL Scripts, 4 spids, dividng the table up, 12 hours.

I have found all of them to be very painfully slow.  So slow in fact that I'm not able to do it, as I currently can't take a 12+ hour outage. 

Knowing your volumetrics and design up front is very important for this exact reason.

SQL Server 2005 can do this quicker with parrallel loads, if the table is partitioned, but this is a 3rd party database from a vendor and can't support partioned tables.

SQL Server 2008 provides some new options for storing the blobs outside the database, which is always my preference if possible.

Below is the script for option 3, handy sometimes:

use emailxaminer_run

declare @min int

declare @max int

Declare @startPosition int

declare @endPosition int

declare @currentPosition int

set @startPosition = 980066

set @endposition = 30000000

set @currentPosition = @startPosition

declare @increment int

set @increment = 1000

While @currentPosition < @EndPosition

begin tran

INSERT INTO dbo.Tmp_RT_FileCache (FileID, MessageId, FileType, FileSize, datelastmod, FileName, FileBLOB)

SELECT FileID, MessageId, FileType, FileSize, datelastmod,

FileName, FileBLOB

FROM dbo.RT_FileCache with (nolock)

Where FileId Between @currentPosition AND

(Case WHEN @currentPosition + @increment > @endPosition then @endPosition

Else @currentPosition + @increment END)

commit tran

Print 'Where Clause = ' + convert(varchar(10),@currentPosition) + ' AND ' +

Convert(varchar(10), (Case WHEN @currentPosition + @increment > @endPosition then @endPosition

Else @currentPosition + @increment END))

Set @currentPosition =

Case WHEN @currentPosition + @increment > @endPosition then @endPosition

Else @currentPosition + @increment + 1 END

--Print @currentPosition

Evaluation of the Polyserve 3.6.1 is underway.

Recently completed the upgrade of our Development cluster.  All went smooth. 

This is another one of those difficult upgrades that requires multiple outages (though small).  First you have to un-install the software and then install the new software.  So this causes an outage.  The File systems also have to be upgraded, so you have to stop using each file system to upgrade it, this causes another outage. 

So far things have been quick and no issues.

Definetly had we not completed the 3.4 to 3.6 upgrade, this would have been very difficult, as the 3.4 to 3.6 upgrade was more complex.

No word yet on Polyserve support for sql server 2008.  Though based on the sparse and ADS (Alternative Data Streams) file system options in 3.6.1 they are very close, and I'd bet you will have to be on 3.6.1 as both the sparse and ADS seem to be necessary for sql server 2008.

Could not find prepared statement with handle 2.

This was a very odd and difficult item to track down and fix.  While working with a production migration of an application to sql server 2005, the application did not work properly.  Of course a 3rd party product, error message from client was ambiguous at best, but obviously something wasn't working.

Upon running a sql profiler to try and capture the error message, success, profiler did capture an error (a true shame the front end client doesn't capture the error, as it is being returned to the client, another example of truly great programming!):

Error: 8179, Severity: 16, State: 2

Could not find prepared statement with handle 2.

A Quick review of this error causes me to be slightly dumb founded, as the error is not happenning in QA.   The vendor suggests changing the SQL Server Services to run under the same service account as the application, not sure where that suggestion comes from, but I politely take it under advisement, while privately thinking that i'm dealing with someone who is more clue-less than me.

I've seen some isolated instances where odd errors happen with SQL Cumulative updates, and this instance is on cumulative update 6, so I begin to wonder if it is an issue with the cumulative update, but that doesn't hold up as QA is also on cu 6.  I've also seen issues with the sp_execute and handles having issues due to client resets and the client MDAC stack, I decide to research this a bit.

Finally I find a KB Article on a similiar subject, not exact, but close enough to try, http://support.microsoft.com/kb/913765/en-us.  FIX: An SqlException exception may occur when you call one of the Execute methods on the instance of the SqlCommand class in an ADO.NET 2.0 application. 

I ask the vendor about their data access layer, versions of ado.net and patches. The vendor response is less than enlighting and only re-enforces my belief that i'm dealing with less than steller support.  Remaining composed is critical, as you can't insult the vendor, we're using their product and I'm in the middle of production upgrade that is melting down, with the business waiting...must keep composed and concentrate on the issue....a few choice explicitives are released, thankfully the mute button works properly.

Now why the DBA has to troubleshoot the data access layer of the client, I don't know, but it never fails that it does come down to this.... The DBA has to be one of the most knowledgeable subject matter experts, and not just of the database, but the entire domain that is involved with the application.

Short story ends, patch above is applied, to the client, and all works.  What a shock.

How often do you receive requests to add a user and make their permissions the same as Joe's ?

How often do you work a problem where something works for {user a} but not {user b} and you need to compare their permissions, to elminate that as the problem.

Rick Mcintosh, developed a superb script to clone a user, which can easily be enhanced to compare two users as well (using exists).

Use at your own risk, so far in our testing it has been great, go to my script vault to get the latest version, under Logins - Clone, http://www.lifeasbob.com/Code/ScriptVault.aspx 

Recently ran into an issue where the default schema for all users in a database needed to be changed to dbo.  This was a database that was upgraded from sql server 2000 to 2005, of course a schema was created for each user.  We quickly deleted the user schema's (as they weren't used), but for some reason the default schema for each user was left as their own.  There was no quick way with the gui to change this, so I generated the following query which creates the command to alter the users as an output of column1, cut and past it into a new query window and execute.

SQL 2K5 Only.
Changes the default schema for all users.
Will generate a script in column 1, cut and paste to new query window.
Will fail for Windows groups.

SELECT
'Alter user [' + u.name + '] with default_schema = dbo' as command,
u.name AS [Name],
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/User[@Name=' + quotename(u.name,'''') + ']' AS [Urn],
u.principal_id AS [ID],
CAST(CASE dp.state WHEN N'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess],
u.create_date AS [CreateDate]
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id and dp.type = N'CO'
WHERE
(u.type in ('U', 'S', 'G', 'C', 'K'))
and name not in ('sys','dbo','guest','INFORMATION_SCHEMA')
ORDER BY
[Name] ASC

Working with SSIS, a stored procedure was recently changed from a simple select statement to include some more procedural statements that utilized temporary tables.  Surprisingly the SSIS package stopped working, even though the result set from the stored procedure remained the same.  One of the reasons to use the stored procedure in ssis, was to avoid embedding the login in the SSIS packages related to data access, what gives ?

Error is:

Do you want the component to fix these errors automatically?

I've seen several solutions to this problem using "set fmtonly off" in the SSIS call and "set nocount on" in the stored procedure.  While "set nocount on" is usually recommended, i'm not too pleased with the "set fmtonly off" solution, as I've read issues where this may or may not work and cause performance issues.

There seem to be several different ways to fix this, each of them slightly different, my preference is option 3. 

1.  Put "set nocount on" in the stored procedure, put "set fmtonly off" before the execution of the procedure [in the ssis call]. 

2.  re-write the stored procedure to use table variables instead of temporary tables, a good solution as well.

3.  Add some meta-data and the "set nocount on" to the stored procedure with a "short circuited if clause" (if 1=0), and a bogus select statement at the top.  I've tested with trying to leave the "set nocount on" out and it did not work.

Verify your block size, the default in windows is 4096.

The debate always is what is the optimal block size, I prefer a block size of 64K for NTFS Volumes.

You need to fsinfo to verify this, you can see below this d drive is at 4096, the default, Microsoft recommends a block size of 8K or greater, for volumes hosting sql server databases (the os is fine at 4096).  Block Sizes smaller than 8K may lead to data corruption.

fsutil fsinfo ntfsinfo f

c:\>fsutil fsinfo ntfsinfo d:
NTFS Volume Serial Number :       0x60f2135af213342e
Version :                         3.1
Number Sectors :                  0x000000000237efff
Total Clusters :                  0x000000000046fdff
Free Clusters  :                  0x000000000046bcfa
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000000008000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x0000000000237eff
Mft Zone Start :                  0x00000000000c0000
Mft Zone End   :                  0x000000000014dfc0

Assigning SQL Server a static port number is necessary for many reasons, cluster, firewalls, security through obsecurity etc.  We use Polyserve and often have to assign port numbers, we've never really had a good guide to follow on this, even the Polyserve documentation doesn't really have a white paper or a short paragraph on a port numbering strategy.  After several years now of running static ports, one is definetly needed.  Before you can create a strategy for your Polyserve environment or SQL Server, the below except is a great generic explanation about port numbering and uses.

This is from December 1999, but is just as relevant today.  Source = http://www.microsoft.com/technet/archive/community/columns/inside/techan23.mspx?mfr=true 

SSIS, Excel and 64 Bit SQL Server, wonderful together!

We've seen similiar errors to this before and I blogged something about, blah blah blah, with a solution like re-installing client tools, before trying that, try this, it's an easier fix and may work.  This was found by Tom Reeves, an excellent SQL Server DBA !

Register 32bit dtexec

I ran into this issue yesterday and once in the past and I finally figured out how to fix it quickly. The issue occurs on a 64bit server trying to use the 32bit dtexec command. Typically the package will have an excel connection in it or some another type of object that doesn’t have a 64 bit driver. When you install the 64bit integration services it registers the 64 bit version of DTEXEC, which in turn unregisters the 32 bit version. To fix this you have to register the 32 bit version again. You can do this by running the below code from the server with the issue.

When working with the Polyserve SQL Installer or Multi-Node Upgrade Wizard for SQL Server, we noticed an issue where even after applying the SQL Hotfix, the SQL Version was showing as not upgraded on some machines, this was concerning as the utility did allow us to re-virtualize with these instances in different versions, this is a big concern.  You do not want a SQL Instance on the cluster to be different versions, Different instances can be different versions, but a single instance should be the same across each machine, see picture:

Surprisingly when you went to the individual machines and checked, the SQL version was the same (so on the picture above, physically checking DEVPLYSQL01, SQLTest1 instance was at version, 9.2.3228).  So why was the utility showing 9.2.3152, even after we applied the hotfix ?  No error was reported, but something wasn't right.

Not sure if the instance was not put in maintenance mode properly, or if something else occurred, but using RegMon (registry montior) while the multi-node installer utility ran, i was able to determine that the Polyserve utility used a registry entry to populate this screen.  We were checking the physical binary sqlservr.exe and @@Version (select @@version).  The registry entry is located at:  HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.n\Setup , the key is PatchLevel.  Note that you may have to determine what Instance maps to what MSSQL.n, this can be done by checking another key, HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names, There you will see the mapping of an instance name to it's mssql.n location, see below:

We never experienced any errors or issues, so the moral of the story is, "to run your upgrade / service pack / hotfix and re-verify version numbers across each node, regardless of the messagebox reporting success", also "re-open the multi-node installer (it caches information so completely leave the utility), and verify that it "agrees" that the version numbers are the same".  Do this before going out of maintenance mode !

Recently I was tasked with creating a list skills needed for Disaster Recovery.  Everything !  You don't necessarily need a SQL Server guru, but you need the highest level of competency there is about the product.  Competency of the product, does not necessarily mean an expert, but it means they understand all peices of the product, how to install them, how to configure them....and then how to recover them.

Basically most of the DR Scenarios we run into our bare metal restores.  We start with a clean OS, which closely matches the production environment, but it may not be an exact match.  From there the process of installing SQL and recovering bak files (or other files from tape) begins.

The skill set I submitted was:

Today I was preparing TempDB for some best practices and had to move and size the log file.

I was not able to size the log file of tempDB until I had moved it....and of course I mean physically move it first, which requires stopping and starting the instance.

I was in the process of creating 16 tempdb data files of an even size on a specific mount point and moving the tempdb log file to another mount point.  The current location only had 5gb free, the new location for tempdb's log file would be 33gb in size, i tried to move and size it at the same time and the command failed.  Then I moved the file.  Stopped and started the instance.  Than sized the log file.  PIA.

USE [master]

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'tempdev', SIZE = 2048MB,

filename = 'D:\EAM\EAMQATempDB\tempdb.mdf' )

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev2', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb2.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev3', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb3.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev4', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb4.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev5', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb5.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev6', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb6.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev7', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb7.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev8', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb8.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev9', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb9.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev10', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb10.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev11', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb11.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev12', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb12.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev13', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb13.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev14', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb14.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev15', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb15.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev16', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb16.ndf' ) to filegroup [PRIMARY]

alter database tempdb

modify file (name='templog', filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')

alter database tempdb

modify file (name='templog', size=10240MB, filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')

When you extend an instance of SQL Server on Polyserve, to a new server the startup procedure in the master database is not installed, which if the new instance, on the new server is installed to a different MSSQL.x path, the sql agent service may have issues running jobs, as the sub systems will have different paths.

For my scenario I had 7 machines in a cluster, with an instance SQLTest1 installed on servers 1,2 and 3, with a directory of mssql.3.  This all previously existed and worked flawlessly for many months, but then I needed to setup SQLTest1 on server number 7, this required installing SQL on server 7, and then adjusting the properties in Polyserve to include 7, than failing over to 7.  All this worked great, but further inspection showed that the there were some SQLAgent jobs failing and/or entering a "suspended" state.

A quick review showed that server 7 installed to mssql.1.  Polyserve is supposed to handle this, it does this through a procedure in the master database that is set to startup automatically.  I've seen other instances installed to mssql.1,mssql.2 and mssql.1 and there is no issue, as that stored procedure in the master database handles adjusting sql agent sub systems.  I reviewed the SQLTest1 instance and the procedure was definetly missing.  I manually added the procedure and ran it, now the fail over between any of the servers work.

I can only surmise that the initial virtualization did not add the procedure, because it was not needed, all the sub systems were the same mssql.3. 

I think this may be a bug, it is very simple to fix, the difficult part is recognizing the problem and knowing what the fix is!

I contacted Polyserve on this, and the thinking is that this was caused by the 3.4 to 3.6 upgrade and would not happen on an instance that was "fresh" on 3.6 from day zero, makes sense.

Received the following error on installing cumulative update 8

This was on a sql server installation under polyserve, and sometimes they (the instances) get screwed up.

I tried to start the instance manually and it would not start (net start mssql$Instance).

I checked the error log and found 4 entries:

SQL Server 2005 Cumulative Update 8 release recently.

Found out that CU8 is available, looks like it may address some issues we are experiencing, hopefully all will test out well, i really wish they were releaseing this sql server service pack 3...this is an indication to me that there are more issues and testing yet to be rooled into SP3.

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

http://robbieroberts.wordpress.com/2008/06/18/cumulative-update-package-8-for-sql-2005-sp2/ 

Interestingly we are on CU6, because we had issues with CU7.

Up to CU6, I always believed in applying and being on the latest CU, if possible (not in peak, time to apply through dev and qa).

After the issue we saw with CU7, I'm now of the opinion of being more cautions and trying to determine if the issues in the CU are resolving problems we experience, than apply it.

FIX: The compilation time of some queries is very long in an x64-based version of SQL Server 2005

http://support.microsoft.com/kb/953569/LN/

FIX: The performance of a query in SQL Server 2005 Service Pack 2 Cumulative Update 6 is slow

http://support.microsoft.com/kb/953721/LN/

50002710 953841 (http://support.microsoft.com/kb/953841/LN/) FIX: E-mail replies are sent to the sender address instead of to the address that is specified in the database mail profile in SQL Server 2005 after you install the .NET Framework 2.0 Service Pack 1

50002753 The Database Mail feature in SQL Server 2005 may access data in the memory that is already freed

Welcome to the EEC, Microsoft Enterprise Engineering Center.  Somehow I was reminded of the turtle in the movie Nemo, when the turtle with the Australian accent welcomed Nemo's father, "Welcome to the EAC Dude !".

The Microsoft Campus is impressive.  The EEC is awesome, the employees were most knowledgeable and a pleasure to work with.

We took an application and database out to the EEC Lab to load test it for a week.  We learned an incredible amount of information and had quite an experience.

I've put some pictures of it off my main site.

When starting the index tuning advisor, the following error was received:

R6031- Attempt to initialize the CRT more than once.  This indicates a bug in your application.  

Well no sh** sherlock !  Whoever rights these error messages needs their heads examined.

Found a Microsoft KB Article that seems to apply, but we found the key to delete at another location; we have not deleted the key, as we did not want to have the system become unstable, and deleting keys, that were not specified is not advisable.

We found the key at:
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers

KB Article says to find key at:
HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers

KB Article: http://support.microsoft.com/kb/913395 

Picture of Error:

Recently we had some issues with stored procedures having quoted identifiers set differently.  We wanted to run a query to get a listing of any other procedures having quoted identifiers set on or off.

With some help from people at work we found two queries using the DMV's.

select * from sys.sql_modules
where uses_quoted_identifier <> 1

and

Started receiving this error on some new installations of SQL Server 2005, sp2 + Cumulative Update 6.

I don't know what the root cause is, but we have many servers running this same configuration without the error, I have no idea why this started happenning.

Database Engine Tuning Wizard: "Failed to initialize MSDB database for
tuning (exit code: -1). (DTAClient)

These objects were missing from the server that was failing, so I scripted them from a server that was working.  Once I created the objects in the MSDB database, Database Tuning Advisor now works.

I wish I new what changed in the server / installs that started this happenning, I've checked the last few recent installs of SQL Server 2005 and notice that it is missing from most of them, both 32 bit and 64 bit installs.  Very odd, makes me nervous that something else was missing from the install or that an error happenned and didn't "bubble" up to the installer. 

I'm going to open a case with Microsoft on monday and see what they say, I need confidence that these installations of the DBMS are complete and without any issue, if the Database Tuning Advisor stored procedures and tables were missed, what about service broker, encryption or many of the other features we haven't touched on yet for these particular installations that we expect to be there installed and ready to use ?

I've created a script files of the objects to create, it is available here, DTA_Objects_SQL.txt (433.76 KB)

Wow, was this a pain...the below error was received:

---

An installaton package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package ‘sqlncli.msi’

---

The history of the install, is most likely the root cause, this was on a application server, we first installed the SQL Native Client and applied SP2.  Then we had a requirement for SQL Reporting Services, than of course another new requirement, Integration Services and Work Station components.  We were able to bring up the install and select the new components, but then the error above happenned.  I think it all revolved around SP2 being applied first.

Some quick research indicated that a possible solution may be to uninstall the SNAC, SQL Native Client, and re-run the setup, selecting the new components.

How to un-install SNAC that was applied with SP2 ?  Manuall extract it and then find the sqlncli.msi, execute the msi (double click on it) and select un-install.

This worked for us !

While upgrading our CRM SQL Server to sql 2005 (or rather testing the application after the upgrade), we received the error below.

The transaction active in this session has been committed or aborted by another session.

Error: 3971, Severity: 16, State: 1

The server failed to resume the transaction. Desc:3300000008.

This was not really an upgrade, but a move to a new server.

I found several references to this error, mostly dealing with MS DTC and SQL 2000, but this was 2005 and did not involve linked servers, so the error was odd.  It also did not show up in any of our testing for this migration.

The only thing we could think of was that something on the client wasn't working right.  We quickly pointed the QA Web layer, which did not have this error, at the new production server, no error.  This allowed us to quickly determine the Database layer was working good.  We realized that we had not upgraded or rebuilt the web servers, and both the development and QA web servers were fairly new.

We applied mdac 2.8 with no success.

We then applied SNAC, SQL Native Client, and all began working.

MS DTC - http://support.microsoft.com/default.aspx?scid=kb;en-us;329332 

A post on google (Link below), combined with the QA success, is what got us thinking that the issue had something to do with MDAC / the client:

Post on google... 

SQL Server install fails with the following error:

Moving the files back to the windows installer cache solved the problem.

For information on the Installer Cache, see: Recovering Space on a windows Server.

SQL Server 2005, plan cache, becoming bloated with excessive amounts of adhoc queries with a usecount of 1, crashing the server.  64 Bit SQL with 32gb of RAM.

Ran some queries to determine which database was causing the most adhoc cache plan bloat, than we set the force auto-parameterization on.

-- Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP)
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS 'Plan Cache Size(GB)'
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP'
OR type = 'CACHESTORE_OBJCP'

-- UseCounts and # of plans for Adhoc plans
SELECT usecounts, count(*) as no_of_plans
FROM sys.dm_Exec_Cached_plans
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc'
GROUP BY usecounts
ORDER BY usecounts

Use this DMV query to find the offenders:

-- Find the ad-hoc queries that are bloating the plan cache
SELECT top 1000 *
FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
--AND size_in_bytes < 200000
ORDER BY size_in_bytes DESC

-- Setting the PARAMETERIZATION option to FORCED
ALTER DATABASE [adtempus_DTSR] SET PARAMETERIZATION FORCED WITH NO_WAIT
GO

-- Setting the PARAMETERIZATION option to SIMPLE (default)
ALTER DATABASE [adtempus_DTSR] SET PARAMETERIZATION SIMPLE WITH NO_WAIT
GO

-- The current setting of this option can be determined by examining
-- the is_parameterization_forced column in the sys.databases catalog view.

SQL Server 2005, Cumulative Update 7.

We downloaded and applied for testing, all tested well.

We tried to implement, and ran into a problem with a vendor install for Symantec End Point reporting database.

We could not figure out the issue, the vendor recommended reverting back to SQL Server 2005, without CU7.  Installation completed succesfully.

We never fullly figured out what was in the vendor script that caused it to fail under CU7 and work in CU6, but obviously something was different.

Based on this "unknown" factor we have not standardized on CU7, in the past none of the CU's have ever broken the functionality of an application, but something in CU7 sure does with Symantec End Point reporting.

I've seen two isolated incidents on SQL Server 2005 where restoring from a SQL Lite Speed backup where the MDF, NDF and LDF file permissions were changed to the user who performed the restore. 

Everthing works fine, but then in the future we decided to move these files, but to our surprise were were unable to move them, giving us an error that the files were in use, read-only or did not have permissions.  We spent considerable time looking for what process had the files in use (Virus Scan, netbackup, SQL ? (process and file explorer from sysinternals), and finally read the error again and decided, maybe the files are read-only !  In the process of checking this, we clicked on the security tab, and the individual who performed the restore was the only account with permissions to the files !

We changed the permissions and copied them fine.

I don't know if this was caused by Polyserve, Lite-Speed or SQL Server; but definetly caused us some frustration, very strange indeed !

Recently we began deploying cumulative update 6 for SQL Server 2005.  This brings the version number to 9.00.3228.

So far we've deployed both the 32 bit and 64 bit version.  Deployed to Polyserve as well.  Have about 8 installations so far.

Nothing special to note, seems we have not had the same issues we had installing Cumulative Update 5.

We still have problems with adhoc query plans bloating the cache and the tokenpermuserstore.

Not knowing if Microsoft will release SQL Server 2005 Service Pack 3 by October 2008, which is when we lock our environment down until May 1 the following year...we will begin deploying this update to all SQL Server 2005 Installations.

KB946608

Hot Fix for Microsoft Knowledge Base article number(s) 942907, 942908, 943526, 944358, 944929, 945442, 945443, 945640, 945641, 945916, 946608, 946793, 947007, 947008, 947179, 947196, 947197, 947204, 947414, 947462, 947463, 947975, 948033, 948248, 948367, 948445, 948490, 948508, 948521, 948523, 948525, 948578, 948582, 948622, 948628, 948754, 948920, 949097, 949105, 949108, 949115, 949116, 949117, 949118, 949119, 949120, 949121, 949200

Knowledge Base Article
======================
To view the contents of the knowledge base article for this update, visit
http://support.microsoft.com/?kbid=942907, 942908, 943526, 944358, 944929, 945442, 945443, 945640, 945641, 945916, 946608, 946793, 947007, 947008, 947179, 947196, 947197, 947204, 947414, 947462, 947463, 947975, 948033, 948248, 948367, 948445, 948490, 948508, 948521, 948523, 948525, 948578, 948582, 948622, 948628, 948754, 948920, 949097, 949105, 949108, 949115, 949116, 949117, 949118, 949119, 949120, 949121, 949200 directly, or
visit http://support.microsoft.com and query the Knowledge base
for the product and the KB number of this update.

Began receiving alerts on a job failing.  The job was "suspended".  The error in the history event log is:

Unable to start execution of step 2 (reason: The ActiveScripting subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended).  The step failed.

A quick review of the server shows MSDE was installed on the machine, somehow this causes an error in the registry.

Review the below registry entry and ensure there is an "s" at the end.  According to Microsoft this happens sometimes with MSDE installations and upgrades.  You can see the picture of mine, that it was missing

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup]
"SQLPath"="C:\Program Files\Microsoft SQL Server\80\Tools"

I added the "s" to the end.  Started and stop the instance (i did both services because I could, but may only need to have the agent service restarted.)

Everything is working fine now.

This was on a sql server 2000 installation.

Recently I had to evaluate a migration from MSDE to SQL Express.  Always being proponent of staying on a current and supported DBMS, you might be surprised that my recommendation was to stay with MSDE.

Given that SQL Server Express 2008 is being ready for release, it does not seem prudent to migrate to SQL Server Express 2005.  It may be better to use a leap frog strategy and skip SQL Express 2005, moving right to sql express 2008.

There is no doubt that SQL Express is better than MSDE, but for us, we really don't need any of the features of SQL Express, MSDE is adequate.  But we do need to be on a supported DBMS that runs on Vista, and it is what is driving the upgrade.

SQL Server 2005 to Oracle via Linked Server - OpenQuery Syntax with Datefields

For some reason the Oracle Linked Server in SQL Server 2005 is more "type sensitive" when it comes to data types.  We've had to resort to using OpenQuery instead of the 4 part linked server syntax and using conversions of the datatypes.  The unique part of using openquery is to get as much of the query to resolve on the host (oracle) side as possible, to do this you will need to become familiar with Oracle's functions

Between on two hard coded date fields:

Select * from openquery(TSR,'SELECT to_char(FILE_SEQ_ID) as FILE_SEQ_ID, MACHINE_NM, TO_CHAR(PATH_ID) AS PATH_ID, TUNER_ID, FILE_NM, TO_CHAR(FILE_SUFFIX_ID) AS FILE_SUFFIX_ID, TO_CHAR(PREVIOUS_FILE_SEQ_ID) AS PREVIOUS_FILE_SEQ_ID, LEAP_RECV_TS, CRE_PROC_TS, TO_CHAR(FILE_SOURCE_CD) AS FILE_SOURCE_CD from TSR1.LOAD_FILE_HIST where CRE_PROC_TS between to_date(''03/10/2008 03:00:00'',''mm/dd/yyyy hh24:mi:ss'') and to_date(''03/14/2008 03:30:00'',''mm/dd/yyyy hh24:mi:ss'')')

Between on hard coded date field with System Date.

Select @fin_Current = fintotal from openquery(FIN,'select SUM(tot_returns) as fintotal from division_vol_sum where sum_dttm between to_date(''01/01/2008 00:00:00'',''mm/dd/yyyy hh24:mi:ss'') and trunc(sysdate) and division_dept_id <> 24718')

We use an EMC Symmetrix SAN.  We zone lots of storage.  Recently had some new storage zoned and we could not "see" the storage.  Turns out that 64 bit windows can only see the first 256 luns zoned to an FA Channel.  Now 32 bit windows does not have this issue, so things actually "took a step backwards" with 64 bit.

This was a frustrating development.  Currently we have no solution from Microsoft (thankyou).  The work around is to have our SAN Administrator zone luns over 256 down another FA Channel.

Plan you SAN Carefully.

Found this:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;310072

MySQL Dump File to SQL Server.  Some how I thought this would be easy, while it was not overly difficult it did require some basic skills, parsing.  I ended up not taking this as far as I wanted, but all i needed was a down and dirty import of a mysql dump file into sql server.  There are many ways to do this, the best two options are to (purchase a utility) or (install mysql, import the dump file, create a proper export that sql server can read and/or an ssis package).  I didn't want to install mysql or purchase a utility. 

I wrote a c# program that parses the dump file out into a DDL and DML File.  I than took the DDL File imported it into Erwin as a mysql database, than changed the database to SQL Server and forward engineered the schema to a TSQL Script file.  I then created a SQL Server database off of the TSQL Script file.  Next I ran the TSQL DML File against the database, and poof, i'm done. 

This example will not work for every mysql dump file, nor did i perform lots of testing, i just needed something converted.  It is not pretty code.  But maybe it can help you...

There was some interesting bits in here; reversing a string, parsing a file one byte at a time and evaluating the byte, word breaking analysis and word grouping.

The resulting files still needed a little cleanup, but it was very easy.  The DDL File had one data type that erwin didn't recognize.  The dml file has some extraneous data from the parse, but it is easily removed.

I've included examples of the source dump file i used, and the resulting ddl and dml files.

DDL_Output.txt (2.8 KB)
mysql_dump.txt (121.02 KB)
DML_Output.txt (207.73 KB)

Want Fries with that ?

Experienced a Polyserve failover today, root cause has been very difficult to flush out. 

One lucky hit was our solar winds monitor was reporting 80% packet loss on the server !

Since we are using server based fencing we wouldn't expect a crash dump.  The problem is that the node is fenced (power_cycled) before the crash dump occurs.  Unfortunately, there is really no way to determine the cause of the crash without a Memory.dmp.  But, one of the side effects of TOE being enabled on the servers is a blue screen. 

TOE, what's that ?

This stands for TCP/IP offload engine. Modern NICs have the ability to offload the processing of network transmission. This allows the CPU to focus on its other responsibilities. This feature is dependent on the  OS supporting the feature. SP2 supports TOE. This is the first iteration of Windows that does so. It definitely has advantages, but we have seen some issues in other areas so turning it off may help solve the problem.

Of course like any good new technology, it doesn't always work right and the side effect of TOE is a blue screen, nice !

Review the attached KB Article on TOE from HP:  TOE_KB.htm (5.76 KB) 

At the same time, we're still working on issues with the CPU synchronization errors in the SQL Server error log:

The time stamp counter of CPU on scheduler id 14 is not synchronized with other CPUs.
The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.

See Microsoft KB Article:  http://support.microsoft.com/default.aspx?scid=kb;EN-US;931279

Now this error I though only affected the AMD Chipsets, but this particular machine is an Intel 4 way quad core, so 16 processors.  On the AMD Chipsets we modified a boot.ini file with /usepmtimer, but on this intel box we had to go to the bios settings.

Hopefully after changing the BIOS Power settings to maximum always, we'll see no more fail overs and no more synchronization issues.

This has been a thorn in my side as long as I have worked with SQL Server.  There are times you want to grant truncate table permissions, either on a table or all tables etc to a specific user and/or service account, BUT you do not want to grant any alter or DDL privlidges or worse db owner!

Now why Microsoft, in SQL Server 2005 did not make this available, I do not know; as smart as the people who I have seen and met that work on the product, I'm sure they could have overcome any technical issue given them.  And please don't use ANSI Standard or some other weak excuse.  I want to be able to type "Grant Truncate on Table to User", just like Select, Update or Delete.  Too BAD for me.

Fortunately Micro$oft did give us a "fishing pole", so go learn how to fish and explore: execute as.  With this procedure you can delegate and impersonate to accomplish tasks. 

So the basic setup we devised, (which was based on a model used by our Oracle group):

1. Stored procedure - using Execute As to elevate rights
2. Control Table - to determine which tables a user is authorized to truncate
3. Audit Table - who, what and when (could be enhanced for why !)

The scripts below need a slight review and may need work for you particular enviornment, but the "guts" of the process is here.  Note that the control and audit table are in a dedicated DBA Schema.

USE {database}

CREATE SCHEMA [dba] AUTHORIZATION [dbo]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dba].[TRUNCATE_AUDIT](

[Truncate_Audit_Id] [int] IDENTITY(1,1) NOT NULL,

[HostName] [varchar](30) NOT NULL CONSTRAINT [DF__TRUNCATE___HostN__440B1D61] DEFAULT (host_name()),

[UserName] [varchar](30) NOT NULL,

[DATETIME] [datetime] NOT NULL CONSTRAINT [DF__TRUNCATE___DATET__44FF419A] DEFAULT (getdate()),

[TABLE_NAME] [varchar](100) NOT NULL,

[TABLE_OWNER] [varchar](30) NOT NULL,

[SUCCESS] [int] NOT NULL CONSTRAINT [DF__TRUNCATE___SUCCE__45F365D3] DEFAULT ((-1)),

CONSTRAINT [PK__TRUNCATE_AUDIT__4316F928] PRIMARY KEY CLUSTERED

[Truncate_Audit_Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dba].[TRUNCATE_LIST](

[List_Id] [int] IDENTITY(1,1) NOT NULL,

[List_Table_Name] [varchar](256) NOT NULL,

CONSTRAINT [PK_dba.TRUNCATE_LIST] PRIMARY KEY CLUSTERED

[List_Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

USE [{database}]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE Procedure [dbo].[TRUNCATE_TBL] @tbl varchar(1000),@owner varchar(30)='dbo'

WITH EXECUTE AS SELF

as

Declare @username varchar(30)

Set @username = Original_login()

If exists (select * from dba.truncate_list

Where [List_Table_Name] = @tbl)

Begin

Declare @rc int

Declare @truncate_Audit_id int

Insert into dba.truncate_Audit (table_name,table_owner,username)

Values (@tbl,@owner,@userName)

Set @truncate_audit_id = scope_identity()

declare @dynamic_sql nvarchar(1000)

set @dynamic_sql = 'Truncate Table ' + @owner + '.' + @tbl

--Print @dynamic_sql

set @rc = -1

BEGIN TRY

exec @rc = sp_executeSQL @dynamic_SQL

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000),

@ErrorNumber INT,

@ErrorSeverity INT,

@ErrorState INT,

@ErrorLine INT,

@ErrorProcedure NVARCHAR(200);

SELECT @ErrorNumber = ERROR_NUMBER(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE(),

@ErrorLine = ERROR_LINE(),

@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

SELECT @ErrorMessage =

N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +

'Message: '+ ERROR_MESSAGE();

-- Raise an error: msg_str parameter of RAISERROR will contain

-- the original error information.

RAISERROR (@ErrorMessage,@ErrorSeverity,1,

@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorProcedure,

@ErrorLine)

RETURN @RC

END CATCH

Update dba.truncate_audit

Set success = @rc

Where truncate_audit_id = @truncate_audit_id

End

ELSE

Begin

RAISERROR('Cannot find the object because it does not exist or you do not have permissions.',

16,1,1088,16,7,'[dbo].[TRUNCATE_TBL]',1)

Return -1

End

SOX Compliance has missed the target.

I work a lot lately with auditors and sox compliance, and I wonder if this is just something that got created to drum up business.  Post Entron, SOX was supposed to restore confidence, I highly doubt Enron's balance sheet was corrupted by lax database standards.  It's always good to have a good security policy in place, but it seems that SOX Compliance and database security while on parrallell paths diverge when it comes to intent.

A recent article about how companies still "hide" (really under-report) off-balance sheet liabilities, show how SOX has missed the target.  Reading this article than made me think, about why as DBA's we spend so much time on SOX Compliance, when the real issue has very little to do with IT standards.  Not that IT standards and security are not important, but when it comes to SOX, maybe these audit companies are spending too much time looking at the wrong department.

The original article is located here:

http://www.iht.com/articles/2008/02/28/business/norris29.php

FLOYD NORRIS

Off-the-balance-sheet mysteries

SQL Server does a great job of logging, but anyone who administers a sql server should adjust some of the "out of the box" defaults for an instance, and create some custom jobs in sql agent to control output of jobs.

By default sql server logs information to ErrorLog.  Depending on your version of SQL Server (7.0, 2000 or 2005) and a default or named instance it is located along a path similiar to the following:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL${Instance}\LOG

The ErrorLog is a great source of monitoring and should be scraped and reviewed regularly, but by default there are only 6 log files kept on disk, and a new log file is started evertime an instance is started or "cycled".  This is not adequate, an administrator applying a patch to a server seems to require 3 reboots and you don't want to find out that information needed in a log file was lost due to not having enough of them.  We run this to 30 log files kept on disk and cycle the error log daily at 12am via a sql agent job.  This is very easy and can be adjusted via gui in SQL Enterprise Manager or in SQL Server Managemetn Studio by right clicking on the SQL Server Logs and selecting "Configure", which will bring up the dialog below, adjust to any number, but definetly 6 is not enough !

It can also be adjusted via TSQL, but requires xp_instance_regwrite and is highly dependent that you know the structure of the registry for your particular instance and version of sql server, this is one case where using the GUI is the best option, but it is possible to do via tsql:

EXECUTE master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer' --****!!!! VERIFY
,N'NumErrorlogs'
,REG_DWORD
,30 -- number of errorlogs you want to keep
GO

After configuring the instance to retain 30 errorLogs, now you need to implement a sql agent job to cycle the error logs daily at 12am.  This is very easily implemented with a job scheduled to run:
Exec sp_cycle_ErrorLog.

Now onto the good one, SQL Server Agent and the output from jobs.

After managing SQL Server Agent jobs for many years it is apparent that the default options for logging of jobs, Overwrite and Append, is not adequate.  Most of the time we default the logs to append, but overtime and depending on the amount of output these log files, they can become very large, difficult to open, difficult to read and waste valuable time when you have to trouble shoot a job.  This applies to sql server 7.0, 2000 and 2005 and is highly dependent on having all your jobs and logging go to the same directory (if they are currently "scattered" you can correct them manually or through tsql in the sysjobsteps table, with a mass update).

We have developed a jobs that we host on every sql agent instance, Log Archive.  It is a two step job consisting of TSQL and Active X Scripting.  One step moves all the log files to a sub-directory for that day.  The 2nd step of the job removes the sub-directories older than x days (we default to 30).  This makes it very easy to find a log file for a particular day, based on the naming convention of the sub-directories.  This job runs daily at 11:58pm.  Example below:

The code for the two steps is attached, they will require editing for your specific directory structure and number of sub-directories to retain.

Archive_Daily_Logs.txt (.7 KB) 

VBScript_Delete_Old_Dirs.txt (.52 KB) 

This creates a great directory tree, easy to maintain, easy to troubleshoot:

Interstingly found that quotes are necessary around the password for OSQL if the password contains special characters.

Take the following password, spoon12free^$ and create a sql authenticated account with that password and then try to use osql with some simple query, without quotes you receive a login f

osql -S(local) -dmaster -Umy_user -Pspoon12free^$ -Q"select getdate()"

This results in a failure.

I don' know why, but this error suddenly began happenning while trying to start SQL Server configuration manager:

Cannot connect to WMI Provider.  You do not have permission or the server is unreachable.  Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager. 

Invalid class [0x80041010]

This started with every one of my group's DBAs and seems to follow some SMS Push to update something on our desktops, lovely.

One solution from a dba in my group was applying patch 3215, which probably just incidently fixed it.

I researched further and found the following solution:

http://blogs.msdn.com/echarran/archive/2006/01/03/509061.aspx 

Occasionally, during setup, some .mof files don't get installed and registered correctly.  There is a program called mofcomp that is responsible for registering and storing the data associated with .mof files.  If the .mof file information becomes damaged or compromised, or never installed correctly, the problem will result in an error message like below:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.
Invalid class [0x80041010]

The solution is to go to a command prompt and then run mofcomp.

C:\Program Files\Microsoft SQL Server\90\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof"

The output will look like below

Microsoft (R) 32-bit MOF Compiler Version 5.1.2600.2180
Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.
Parsing MOF file: C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmprovider
xpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
Done!

On servers, the .mof file will be sqlmgmprovider.mof.

Been chasing an issue for a long time, where suddenly the connections to a sql server increase exponentially and queries become unresponsive.

Really never made much headway, but found two interesting articles, URL's below:

http://sqlblog.com/blogs/sarah_henwood/archive/2007/07/23/connection-failures-sql-2005-appears-unresponsive.aspx 

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

Supposedly this was fixed in Service Pack 2.  I'm having the issue with SQL Server 2005, SP2, Cumulative Hotfix 5, version 3215.

Monitor the query results of:

SELECT SUM(single_pages_kb + multi_pages_kb) AS
   "CurrentSizeOfTokenCache(kb)"
   FROM sys.dm_os_memory_clerks
   WHERE name = 'TokenAndPermUserStore'

If it is continuously increasing, than try running:

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

We have 81+ servers, 100+ instances of SQL Server, and it is only affecting one, of course it is the one under the greatest load, OLTP 5000 transactions per second.  You can see the result of the TokenAndPermUserStore sum growing to 120mb, we now reset it around 40-50mb and have seen no re-occurences of the issue.

I have a case open with Microsoft and am trying to get confirmation that this is the right solution, but since we began running the DBCC statement we have not yet crashed, and previously we could not make it more than 2-3 days without crash, so that there is proof positive of some correlation.

We'll see what Microsoft responds with.

More on plan cache:

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/21/2-0-diagnosing-plan-cache-related-performance-problems-and-suggested-solutions.aspx

tempDB, size it right; it is important.

use -T1118.

1 data file for every logical processor, 1 log file.  This seems excessive for a 4 way Quad core machine, 16 temporary database data files, each one at 2gb.  We'll see how this runs today.

Read the Microsoft white paper, it's got all the information.

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx 

WorkingWithTempDB.doc (301.5 KB) 

One Unexpected side effect of this is uncontrolled log file growth.  We have a 12gb log file and it was filling up quick, 2gb an hour.  While monitoring this you could see the log file growing continuously, if the log file were to fill up, it could cause an aotogrowth, an autogrowth in tempDB when you doing 1,000's of transactions per seconds will crash the server (ask me how i know!).  To keep this from happenning we implemented a job to checkpoint the tempDB Database every 30 minutes.  Hopefully when we ren't under such load it will ease up and begin checkpointing properly on it's own.  We did verify there are no uncommitted or long running transactions.

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.

In an OLTP system that uses tempDB extensively it is important to monitor both the user and tempDB very closely.