Thursday, August 14, 2008
Solutions and Projects were one of the really cool features introduced in SQL Server 2005, judging from the responses I got when I showed how it works. Personally, I use them a lot. SQL 2008 has the same feature in SQL Server Management Studio. However, SSMS 2008 breaks this feature in SQL 2005 SSMS when installed side-by-side.
See all the ugly details here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=361706
This also affects 64-bit systems, I just happened to find it on a 32-bit box first.
I am guessing that this failure is inherited from SSMS's Visual Studio ancester, which may make it difficult for the SQL team to fix.. No matter how hard I try, I cannot come up with a good reason that it should work this way. I have thought of several bad reasons, but I will hold off on the rock throwing.
Wednesday, August 13, 2008
Fast on the heels of SQL 2008 is the Feature Pack for SQL 2008. Cool goodies include stand-alone installers for SQLCMD and the SQL Native Client, SQL 2008 Server Management Objects, SQL 2008 pre-defined Policies, and lots more.
You can find it here.
http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C
Thursday, August 07, 2008
It looks like SQL 2008 may have a slight dependency issue. If you have already installed Visual Studio 2008, you will be blocked from installing SQL 2008 until you install Visual Studio SP1. The problem is that Visual Studio SP1 is not released yet. Our guys came in ahead of schedule and they still get no respect.
Not to worry, Visual Studio 2008 SP1 should be out very soon (think days, not weeks) and this problem goes away.
Microsoft actually documented this issue here:
Visual Studio 2008 SP1 may be required for SQL 2008 Installations
http://support.microsoft.com/kb/956139
--GNH
UPDATE: It's Here
Wednesday, August 06, 2008
SQL 2008 is finished. MSDN has all the bits downloadable now. Expect retail versions shortly.
Thursday, July 31, 2008
Windows 2008 Clusters have a lot of new features that make clustering better. You don't have to wait for SQL 2008 to take advantage of some of them. One of the changes is in the DTC (Distributed Transaction Coordinator) options.
With Windows 2000 and 2003 clusters, you got only one DTC instance per cluster. For a long time, we just added the DTC resource to the cluster group, anchored it to the Quorum disk and forgot about it. Starting with SQL 2005, some systems that used DTC heavily started to fail the Quorum disk and crash the cluster under heavy load. So Microsoft recommended creating a dedicated DTC resource group for a Clustered SQL Servers. This later got modified to only be necessary for "heavy" DTC users. "Heavy" has been very loosely defined and left to the judgment of the DBA creating the cluster.
All of this was due to a Windows limitation of only one DTC resource per cluster. You could not even have a local non-clustered DTC instance on a cluster node. Windows 2008 takes that recommendation away. Now you can have multiple DTC instances per cluster.
The Windows 2008 documentation shows various configurations:
http://technet2.microsoft.com/windowsserver2008/en/library/993bfdca-eea4-44cc-9432-b14449c830781033.mspx
The big question is "What is best for SQL Server?" Microsoft has always told us to keep the SQL Resource groups "pure" and not add extra resources, especially ones that tweak SQL dependencies. I posed this question to the Microsoft SQL Development Team recently and they pried themselves away from SQL 2008 preparations to help me out. While they have no formal recommendations at this time, they did acknowledge the need to come up with something AND they gave me some advice. They suggested creating a DTC resource for each SQL Virtual Instance on a cluster. Put the DTC resource in the targeted SQL group and change the log file to be dependent on a group disk. Do NOT make SQL Server or SQL Agent dependent on the DTC resource. The reasoning is that the restrictions on SQL dependencies is so that the installer for patches and hotfixes can stop and start the SQL Engine as necessary during the patching process. Since we are not affecting SQL Server starting, we are OK. This configuration also makes sure each SQL Virtual Instance has a local copy of DTC, thus saving cross-server communications that may cause bottlenecks on high-volume systems.
Wednesday, July 09, 2008
I finally got a working Windows 2008 cluster for SQL 2005. I figure one new set of problems at a time is enough. I iwl go for SQL 2008 on a Windows 2008 cluster later. One thing I noticed right away; clustering no longer requires a domain account for the cluster service. Everything runs on Local Service on each node. Kudos to the Cluster development team on this simplification. One less domain account to track.
The new terminology also takes some getting used to, but I can learn to call a "Resource Group" a "Service or Application". Installing SQL is pretty much straight up, except for needing to create an empty Resource Group Service or Application and put the correct disks in it. The Create Empty Service or Application command is not on the same menu and level as the Create New Service or Application command, causing a minor annoyance.
I did get to test out heterogeneous clustering with two different systems for the host nodes. Same brand of hardware, but very different models. We also used different brand HBAs in each machine. The validation wizard noticed but didn't really care.
Another very useful feature is the ability to change dependencies WITHOUT taking the SQL Service offline. After you add a disk to the Service or Application (Resource Group. I still want to call it a Resource Group), you can make SQL dependent on it without taking SQL offline as long as the new resource is online. This is great for adding new LUNs to an existing clustered instance in a non-disruptive manner. And yes, SQL sees them and will allow you to store SQL database files on these new disks.
Windows 2008 is rapidly becoming my platform of choice for a Clustered SQL Server.
Wednesday, June 11, 2008
I have been working with SQL Server2005 and 2008 on Windows 2008 lately. I really like the Windows clustering improvements and want to take advantage of them. More on that in another post. One quick note that I think is essential. When you install SQL 2005 on Windows 2008, you have to manually configure the firewall to allow SQL connections from the outside world. This is in addition to any SQL surface area configuration changes you do.
- Start Server Manager
- Drill down to Configuration | Windows Firewall with Advanced Security" | Inbound Rules
- Select "New Rule…" on the right.
- The rule applies to a "This program path" : "%ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" Note that your specific execution path may be different due to instance naming.
- Select "Allow the Connection"
- Apply to all profiles
- Name the Rule "SQL Server Database Engine"
- Repeat this for the SQL Browser Service.
- The program path is "%ProgramFiles% (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe". Note that this is from an x64 OS and SQL Install. The SQL Browser service is always a 32-bit application.
- Name the Rule "SQL Browser Service".
The Security Configuration Wizard will create these rules for you, but will also adjust the startup settings for most of the services on the system. Not exactly what you wanted, but you don't get a choice in the matter if you use the wizard. Wonder who thought that one up?
Tuesday, April 15, 2008
One of the major pain points in SQL Clustering is what is referred to by Microsoft as "servicing". Installation, Service Packs, Hotfixes, and Cumulative Updates bring special headaches to those of use that are responsible for the care and feeding of SQL Clusters. Sometimes you can end up with a Clustered system that appears un-patchable. This is one of those times.
The base system is a pretty typical basic cluster. One instance, two nodes whose primary purpose is to host SSIS. The SQL engine exists to drive the SQL Agent for scheduled SSIS jobs. The problem began with Node1 failing completely. Normally this is no problem, and we started building a replacement node and reconfiguring the clustering. Everything went according to plan until we got to the last step in BOL:
- All nodes of a failover cluster instance must be at the same version level. After completing SQL Server Setup, you must download and apply the latest SQL Server 2005 service pack and/or patches to ensure that all failover cluster nodes are at the same version level.
OK, lets apply SP2. Starting with Node2, I apply Service Pack 2 and it promptly fails. Digging into the Summary.txt log gives the following error details:
**********************************************************************************
Products Disqualified & Reason
Product Reason
Database Services (MSSQLSERVER) The product instance MSSQLSERVER been patched with more recent updates.
**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID
**********************************************************************************
Summary
Product instances were disqualified due to build version mismatch
Exit Code Returned: 11203
Not exactly unexpected since I actually needed to patch Node1. So I shift the SQL instance to the other node and start over. Summary.txt gives me this:
----------------------------------------------------------------------------------
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 1399
Product Version (Final) :
Status : Failure
Log File :
Error Number : 11009
Error Description : No passive nodes were successfully patched
----------------------------------------------------------------------------------
Hmm. I can't patch Node2 because it is already higher than the SP I am applying but I can't patch Node1 because I can't patch Node2 because Node2 is already patched. Unlike in SQL 2000, there is no "binary-only" option to patch the SQL code bits on Node1. After all, the actual databases were patched when the whole cluster was built up to build 3161 so all I need to update is the SQL executables. Therefore, the cluster recovery instructions as written in BOL are impossible to follow if the system is not at exactly at Service Pack revision. Given that SP2 was broken from the beginning and we MUST add a hotfix to get it to work correctly, this is more than merely painful.
All hope is not lost, however. There is a way to fix the cluster. The steps are actually pretty simple, but I strongly suggest testing this yourself before applying it to a production cluster. I tested this using a Virtual Server hosted cluster.
First, you remove Node2 (the fully patched node) from SQL following the steps in BOL. Do not evict it from the cluster unless you want to do a lot of extra work. Reboot Node2.
Then you re-add Node2 to the SQL instance. Now both nodes have RTM (Build 1399) binaries. You can then walk them up the patch chain together, rebooting as necessary, to get to whatever patch level you have decided is appropriate.
No SQL configuration gets lost since the clustered instance never goes away. This will take a significant amount of downtime due to the multiple installs, patches, and reboots. My production fix took about an hour and a half on good name-brand, current tech quad-socket servers.
Thursday, March 27, 2008
If you feel an uncontrollable urge to listen to my voice or to meet me in person, here is your chance.
You can listen to me on Greg Low's SQL Down Under Podcast. I discuss SQL Clustering, SANs, and ramble on about other topics as well.
http://www.sqldownunder.com/PreviousShows/tabid/98/Default.aspx
I will be joining Kevin Kline and Hilary Cotter on a webcast for Quest Software on April 9th. We will be discussing features in the forthcoming SQL 2008 product.
http://www.quest.com/events/listdetails.aspx?contentid=7059&searchoff=true&technology=34&prod=&prodfamily=&loc=
And I will be speaking at the SQLSaturday #3 event in Jacksonville, Florida on May 3rd.
http://www.sqlsaturday.com/default.aspx
Wednesday, February 06, 2008
One definition of leverage is "the use of a small initial investment, credit, or borrowed funds to gain a very high return in relation to one's investment, to control a much larger investment, or to reduce one's own liability for any loss." (Courtesy Dictionary.Com).
In programming, the smallest change we can make is a single bit. The next smallest change and the one we can make most effectively in human-readable source code is a single character. This story is how changing one character in a 300 line stored procedure removed 90% of the impact of the worst single query on the entire server.
This process began with a fairly normal analysis of a poorly performing server. As usual, the server was found to be I/O bound. Routine analysis indicated a particular query within a stored procedure to be the worst performing query. Comparing this query to the second worst query on the top ten list revealed it consumed 15 times the CPU and 20 times the IO cycles of the next worst offender. Calling Captain Obvious, please report to Performance Tuning.
I did an index analysis. No joy there. Everything looked correct and the query plans were fine. The query took a lookup string, hit a master lookup table, did key lookups across 4 other tables (three joins deep at most) and returned the extended attributes of the master key. The master table had about 400K rows and was constantly growing with new key values. This lookup was executed hundreds of thousands of times a day, so any improvement would be significant.
Since the schema and data was from an external data provider, I couldn't make any significant changes, nor was there any support for de-normalizing the data for a flat lookup.
My brilliant idea was to create an indexed view and let SQL Enterprise Edition do an under-the-covers substitution. Like most brilliant ideas, this did not work as expected. I could not get SQL to substitute the view for the underlying tables. ARRGHHH!
Finally I realized the problem was not in the query, nor was it in the index. It was something we usually take for granted. An implicit data type conversion was killing me. This was the culprit:
WHERE MasterKey = @LookupKey
Well, it was sort of the culprit. You see, Master_key was defined as varchar(10) by the external data provider and @LookupKey was defined by a wannabe senior developer. Naturally, a real developer uses forward-compatible data types like nvarchar(10) and not nearly obsolete types like varchar(10).
Most of us know not to write something like this:
WHERE Column_Value / 2 = 4
We write something like:
WHERE Column_Value = 8
The idea is to NOT force SQL to scan the column, run every value through a function, and then compare it to a constant. The correct way is to pre-calculate the constant and compare it to the column. Turns scans into seeks, cuts IO, promotes world peace, you get the idea.
So what does this have to do with the first WHERE clause. One rule on implicit conversions to larger data types is that the lower type always gets promoted. Thus the WHERE clause actually became:
WHERE convert(nvarchar(10), MasterKey) = @LookupKey)
Now that is pretty obviously bad.
By now, you have likely figured out the one character fix. Remove the 'N' from the declare function and use the old, bad, non-portable data type. Given the data definition for the real-world data type in this example only uses A-Z and 0-9 characters and the fix was at least a full order of magnitude faster, I can live with that.
Leverage. One character change fixes an entire problem server.