TryCatchFinally.net Some SQL, some .NET, and whatever else

29Sep/124

Making sense of SQL Server 2012 MCP Certification paths

I earned my MCDBA on SQL 2000 and then skipped the certification tests for 2005 and only took one for 2008. Now that I'm thinking about taking some 2012 tests to get my certifications updated, I find myself confused - no matter how many times I check the Microsoft Certification page for SQL Server, I find myself still a bit unclear about how things upgrade from 2008 to 2012, and if it makes sense to squeeze in a few SQL 2008 tests while I still can (they retire on July 31st, 2013).

To help make sense of them, I made a few cheat sheets that I'm hoping will clarify what tests are needed for which certifications (including which ones apply towards multiple certifications, so you get the biggest "bang for your buck", in a way).

In these charts, the certifications are on the left side and the individual tests are across the top - the boxes marked in the chart correspond to the tests required to earn a particular certification. Also, you can click on each chart to get a slightly larger/clearer version.

SQL 2008 Certifications:
If you want to earn the "MCSA: SQL 2008", you'd find the certification on the left (it's the last row) and see which boxes are shaded (exams 70-432 and 70-448).

You may also notice that some of the certifications are colored - that's to help make sense of the SQL 2012 upgrade paths. Each of the colored certifications can be used as part of an upgrade to a certification in SQL 2012. In the chart below, the left set of "Exams" along the top are certifications - the boxes are colored the same as the above chart, to help make clear which certifications can be upgraded:

Upgrading certifications from SQL 2008 -> SQL 2012
Using this chart, say you want to earn your "MCSA: SQL 2012" (it's the first row) and you already have your "MCSA: SQL 2008" (it's the first column - green from the previous chart). To complete your certification, you'll need to pass exams 70-457 and 70-458.

Finally, here are the same SQL 2012 certifications, but without the upgrades from SQL 2008 - in this chart, it assumes you're starting from scratch:

SQL 2012 Certifications:
If you want to earn the same "MCSA: SQL 2012" as before, find it on the left (it's the first line), and then you can see that it requires passing exams 70-461, 70-462, and 70-463.

Hopefully this helps sort things out a bit and make the upgrade paths a little more clear.

More information about Microsoft Certifications for SQL Server:
SQL Server certification - main page
MCSA: SQL Server (covers both SQL 2008 and 2012)
MCSE: Data Platform (new for SQL 2012)
MCSE: Business Intelligence (new for SQL 2012)

26Sep/120

Cluster terminology – What “Active/Active” actually means

As a follow-up to my last entry (attempting to clear up some Windows Clustering terminology), I've found an article that makes another distinction that I forgot to include - the difference between an active/passive and an active/active cluster:

The misconception of active/active clustering (a la AirborneGeek.com)

The understanding among those new to cluster seems to be that a/a vs. a/p is a licensing question, and then if you're licensed for it, you just turn it on. In reality, it really just describes whether you have clustered services living on only one node or split between both nodes (during normal operation - during a cluster failover, any cluster might be active/active for a short period of time. Or, I suppose, your cluster is active/active if your quorum drive lives on the opposite node from your clustered service). There's no load-balancing involved in clustering at all - at any time, only one node owns a particular resource, and only that node is responding to client requests for that resource.

In SQL Server 2012 AlwaysOn, the new high-availability feature, the SQL Server service is running on both cluster nodes, but client access (through the "Availability Group") is controlled by the cluster service. That means that all clients making a connection go first to the active server, and then the SQL Service there might send them to get their data from one of the other nodes (it's worth reiterating here that, in AlwaysOn, SQL Server isn't clustered, but the SQL services operate independently on each node).

19Sep/120

Clearing up Windows Cluster terminology

I wanted to clear up some terminology around Windows Clusters that seems to cause a bit of confusion. I've stumbled across a few questions on StackOverflow and Experts-Exchange that seem to have some basic confusion around "clustering servers" and "how to install an application to a cluster", and I'm hoping to set a few things straight.

  1. There's really no such thing as a clustered server. Servers can have clustering enabled and configured, but the servers themselves aren't really clustered - they're just set up to enable clustered applications. When servers are part of a cluster, they still do all their thinking on their own, including running their own applications, services, and tasks, without the other servers in the cluster even being aware.
  2. You don't cluster servers, you cluster applications and resources. Once servers have had clustering installed and are configured, you can cluster an application or a resource. This clustering is really just telling the cluster manager that you want it to control which server clients talk to when they want to access the resource. The cluster manager ensures that the application (or service or resource) is running on only one node at any given time, and to the extent it's able, it ensures that it's always running (watching for a failure and bringing the resource online on another node and then directing clients to that node instead).
  3. Applications don't have to be "cluster-aware" to be clustered. I work mostly with SQL Server, which is cluster-aware, but applications you cluster don't need to be. You can cluster any service, or resource on a server by just adding it to the cluster manager - the cluster manager will ensure it only runs on one server at a time, not allowing it to start on other nodes. For example, we use a monitoring tool that runs as a service - we installed the service on each cluster node and then added to the cluster manager - it now can be failed back and forth between nodes as a clustered resource, so it's always online, is failure-resistant, and shares a segment of the HKLM in the registry between nodes - all without being explicitly cluster-aware.
  4. SQL Server doesn't need to be clustered when it's installed on a cluster. While you can install a clustered instance of SQL Server (which automatically registers everything with the cluster manager), you can also install stand-alone instances of SQL Server (or any other application) on a cluster. That's actually how a new feature in SQL 2012 - AlwaysOn - works: You install a non-clustered instance of SQL Server on different cluster nodes, and then you let the cluster manager coordinate client connections to the SQL Servers, but they still operate independantly and replicate their data between each other.

Hopefully this clears things up and doesn't lead to more confusion. When I first started working with clustering, I had the impression that setting up a cluster caused the servers to act as one and share all their resources, but that understanding led to a lot of confusion when it came time to set something up or troubleshoot an issue. With the understanding that "clustered servers" are really just servers with clustered resources, and not actually clustered themselves, hopefully it will simply things!

4Sep/120

Clean up vendor names and other data with unwanted numbers/characters

In creating an accounting report, the vendor names we get back from our credit card processor needed some major clean-up: "52334SOUTHWESTAIR1234", "ABD2343-BLUE DINER 843", and so on. I initially found a great function for this from Pinal Dave:

http://blog.sqlauthority.com/2007/05/13/sql-server-udf-function-to-parse-alphanumeric-characters-from-string/

But I wanted to make a few enhancements to it:

  1. He leaves numbers in the string and I'd like to remove them
  2. I'd like to combine multiple spaces in a row into a single space, but leave spaces intact

The first is pretty easy to do - just remove the [0-9] and add a space to the PATINDEX. The second one uses a trick from another post I did a few years ago.

Here's the modified version:

CREATE FUNCTION dbo.UDF_ParseAlphaChars2
(
   @string VARCHAR(8000)
)
RETURNS VARCHAR(8000) WITH SCHEMABINDING
AS
BEGIN
   DECLARE @IncorrectCharLoc SMALLINT
   SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)

   WHILE @IncorrectCharLoc > 0
   BEGIN
      SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
      SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)
   END

   -- Trim groups of spaces into single space
   SET @string = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@string,' ','<>'),'><',''),'<>',' ')))

   RETURN @string
END
GO

--Test
SELECT dbo.UDF_ParseAlphaChars2('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
SELECT dbo.UDF_ParseAlphaChars2('52334SOUTHWESTAIR1234')
SELECT dbo.UDF_ParseAlphaChars2('ABD2343-BLUE DINER 843')
GO