Monday, September 29, 2008
No wait, that’s kickboxing. PowerShell is the something of the future. The management interface, the uber-scripting language, the what???
PowerShell, and its SQL-targeted implementation shipped with SQL Server 2008, brings to mind Michael Faraday’s response when asked “What use is electricity?” He replied “What use is a newborn baby?” PowerShell is somewhat of a newborn baby, much like the very early versions of SQL-based databases were. We see how those databases have grown and transformed IT and business in ways we never thought of. Maybe the future of PowerShell is just as bright?
Enough philosophy, let’s see if we can put this baby to use. Rather than the obligatory “Story of PowerShell”, I am just going to dump you to the Windows PowerShell home page. http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx You don’t need me to rehash the story or to write yet another “What is a cmdlet” post.
Now, let’s play with this toy and SQL Server. SQL 2008 installs a SQL-Specific provider into PowerShell when launched from SQL Server Management Studio. BOL includes instructions on how to configure PowerShell to add the SQL provider by default or to launch SQLPS.exe outside of SSMS.
The SQL Provider allows you to browse any SQL Server system list like the file system. While this is all based on SMO (Server Management Objects), this is not exactly the same as browsing the SMO object model. As a matter of fact, the toughest thing in PowerShell is bridging the gap between the provider and the object model. Part of this is that the SMO object model is somewhat flat, while the provider shows as a hierarchy.
Start with the provider and drill down to a particular column. You get something like this:
SQLServer:\SQL\MachineName\InstanceName\Databases\Adventureworks2008\Tables\Sales.CreditCard\Columns\CardNumber
If we want an SMO object for the current item we do this:
PS SQLServer:\...\Sales.CreditCard\Columns\CardNumber> $MyColumn = Get-Item .
Note the “.” at the end. Very important. You get an error otherwise
If we just wanted a “blank” Column object we would do this:
PS SQLServer\...\Sales.CreditCard.Columns.CardNumber> $MyColumn = New-Object –TypeName Microsoft.SQLServer.Management.SMO.Column
Pretty much every object for SQL is directly under SMO in the object model, regardless of where it is in the provider hierarchy. This makes it easy to find the documentation on each object since the links are all on one page. The provider hierarchy should look very familiar since it is very close to what we see in SSMS.
Now we can create objects mapped to actual provider locations which represent real server elements. From here it is not so hard to get or set properties, invoke methods, and make a lot of adjustments within the SQL Server system. Since SSMS is based on SMO, we can reasonably assume that any task in SSMS can be accomplished from PowerShell. Remember, PowerShell is a scripting environment, not an application development environment. Some of the things in SSMS require a lot of “glue” to tie the object changes together to accomplish a particular task.
This brings us back to why is PowerShell for SQL still a baby? Well, a baby can only do three things (eat, cry, poo). PowerShell is not quite as limited, but it is really only good for automating some very specific tasks and not for general SQL Server operation and maintenance. I see two critical cmdlets missing in the SQL PowerShell story before we can say it has grown to the next stage. Copy-item and New-Item would make SQL much more complete. Right now, if I want to create a copy of a table, alter its properties by adding a column and change its database (called a parent in the object model), then write the changes to the server, effectively createing the altered table in the new target database, I would have to drill down and copy each column and each property of each column individually. Copy-Item does with other providers such as the file system. Right now, if you try to invoke the Copy-Item cmdlet you get:
Copy-Item : SQL Server PowerShell provider error: Copy-Item is not supported.
And yes, it is bright red by default.
New-Item is a close cousin. If I back up to the columns level (cd ..) and want to add a new column, I cannot use New-Item. I get:
New-Item : The method or operation is not supported
This error message gives me hope that maybe I am not doing something correctly, but I suspect it is also not completely implemented. I get the same message when I execute it at the database level of the provider.
So, while PowerShell is unfinished, it is still a cool tool do script a lot of tedious, repetitive junk where you just set the same thing over and over or to retrieve the same thing from all databases/tables/etc. For version 1, I give it an “A”.
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