SQL Server 2012 certifications – overview and comments

With SQL Server 2012 out, there is going to be another shift in SQL Server certifications. Microsoft is pushing hard on cloud solutions and it is reflected in all materials related to new certifications. There’s new naming, changed certification path and a recertification requirement. Let’s go through it one by one. 

First of all, all MCTS (Technology Specialist) certs are replaced by MCSA (Microsoft Certified Solutions Associate). It is going to demonstrate that a person posseses a core set of skills in SQL Server 2012 and requires to pass three exams:

• 70-461 – Querying Microsoft SQL Server 2012
• 70-462 – Administering Microsoft SQL Server 2012 Databases
• 70-463 – Implementing a Data Warehouse with Microsoft SQL Server 2012

It is a first step in SQL Server 2012 certification and as you can see, the hype about big data is reflected here in data warehouse exam which previously belonged to BI track. My feeling about it is that it is definitely going to create confusion even for seasoned DBAs who will now have to learn a lot of SSIS just to keep their credentials for a product. On the other hand, introduction of exam of querying is definitely a step towards scripting and possibly automation as Microsoft also makes a lot of noise around PowerShell. Combining those two skills – querying and PowerShell – gives a lot of options for administrators regarding remote task execution or monitoring.

The name MCSA itself reappears as there used to be a certificate for systems adminstrators by that name (Microsoft Certified Systems Administrator) in the times of Windows Server 2000 and 2003 and there are lots of people having it. This may probably lead to a kind of confusion due to the naming (“which MCSA are you? old or new?”).

Now let’s see what changes in MCITP. The IT professionals will now be MCSE (Microsoft Certified Systems Expert) – again a name is reused, since it used to be an Engineer, not an Expert. To become a new MCSE you would either have to be an MCSA in SQL Server 2012 and pass two more exams, or MCITP in SQL Server 2008 and pass three exams. There are only two MCSE tracks for SQL Server 2012 (compared to three MCITP flavours in SQL Server 2005/2008):

• Data Platform - requires to pass
  • 70-464 – Developing Microsoft SQL Server 2012 Databases
  • 70-465 – Designing Database Solutions for SQL Server 2012
• Business Intelligence - requires
  • 70-466 – Implementing Data Models and Reports with Microsoft SQL Server 2012
  • 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server 2012

My first thought about Data Platform was that two separate worlds are being brought together (DBA and developers – don’t forget data warehouse skills ), which is actually good. I know a few people doing DBA stuff who are absolutely uninterested in honing their scripting skills or getting to know even the most basic and commonly used DMVs. On the contrary, I worked with developers who were completely oblivious of security or performance matters. I understand the purpose of one Data Platform cert – it proves that you are a well-rounded professional with experience and knowledge of both aspects of database-related work. I’m just wondering whether a generalization like that – one cert to rule them both – is not too big.

If you are a MCSE: DP, there’s no quick telling if you’re a great DBA, fantastic developer, both or – worse – none. Previous MCITPs could give you a hint, but not a proof – have a certification actually proves only that you passed the exam, yet in reality you may suck – and MCSE: DP raises the level of uncertainty even higher.

Speaking of MCITP – if you have one in SQL Server 2008, you may apply for upgrade to MCSE by passing three exams:

• 70-457 and 70-458 – transition from MCTS to MCSA (transition to MCSA is the same regardless of whether you’re on DP on BI track)
• 70-459 – for DBA/developers or 70-460 for BI people

Finally, if you become an MCSE, you are bound to recertify after three years, which is actually good – you may not work with SQL Server anymore, forget most of stuff or learn thousands of new things and this is the way to prove it to entire world.

Time-wise – as stated on exam pages all 70-461 to 70-467 are going to be available on June 11th, and the upgrades (457 – 460) are scheduled to be available in August (no exact date available yet). However, I expect that due to all those changes SQL Server 2012 certifications will not be as popular as they were in 2005/2008, mostly due to preparation you have to go through (3 big exams for start, 2 more for higher perk). Personally I agree with Paul Randal’s opinion that it’s MCM (now it’s going to be MCSM) that only truly matters, since no other SQL Server exams tests your actual knowledge.

My plan is to upgrade to MCITP DBA on SQL Server 2008 (still ) and seek the way up to MCSE: DP (undecided yet). I can’t procrastinate with it for too long – SQL Server 2008 certifications are gone in July 2013 (I might do it as with 2005 - earned my MCITP on a penultimate day).

Database mirroring pseudorandom ramblings

I liked database mirroring since it appeared in SQL Server 2005. In fact, it’s the only HA technique I feel comfortable with (apart from it’s obvious limitations). It’s neither as blunt as log shipping, as random as replication nor as complex as clustering. It either works or it doesn’t – and you know it straight away after you set it up – you don’t have to wait for snapshot to be generated, sent over the network and applied on a subscriber or for a backup to be copied and restored on your secondary.

The incentive for this post was one by Glenn Berry, which held one answer to my mirroring-related questions. The database mirroring is considered deprecated in SQL Server 2012; it’s going to be available in 2012 and the next version, but then it will be removed from SQL Server and replaced by AlwaysOn Availability Groups. Given the releases of new major versions of SQL Server my totally wild guess it would be like… 2018? Later? All in all, it’s not the major point of concern right now, but something worth remembering and planning when time comes.

What gives me second thoughts is the second part of deprecation note saying “If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping”. What I understand is – combining it with new SQL Server licensing model – you can get replication and log shipping in Standard Edition, clustering and AlwaysOn in Enterprise Edition, but what about mirroring? I would like it to remain in Standard Edition, but to be 100% sure I asked on MSDN forums and I’m waiting for an answer. On the other hand, if it were to go to Enterprise Edition, I would expect dramatic costs increase due to per-physical-core licensing model, which would not be very welcome by most organizations.

When it comes to facts about database mirroring apart from standard source of knowledge (Books On-Line) I also recommend the following:

1. The forementioned post and it’s sequel by Glenn Berry – a detailed introduction and complete checklist of points to be considered while implementing database mirroring.
2. Pro SQL Server 2008 Mirroring – a book by Robert Davis and Ken Simmons, which apparently is not only about mirroring, but also about other high availability techniques as well.
3. Blog posts by Robert Davis tagged with database mirroring – lots of additional information.
4. An MCM video followed by a demo presented by Paul Randal – to give an insight how the mirroring works internally.
5. Microsoft KB article 2001270 on things to consider while implementing mirroring, mentioning exact Performance Objects to watch out for in case of performance issues.

Now few words why I like mirroring in SQL Server:

1. It works.
2. It allows you to do automatic client redirection in case of failure (provided you have a high safety scenario with a witness) – see an example of implementation, works with SQL Native Client, ADO.NET and SQL Server JDBC driver.
3. You can run it on Standard Edition and you don’t need extra hardware for that (unlike clustering).
4. In high safety mode, you can have zero data loss (unless your mirroring is broken).
5. You can have it working even if you are not in a domain, or you don’t have trust relationship between domains in which mirroring partners are – I have recently presented an example during local SQL Server user group meeting based on Books On-Line example available here. It’s completely opposite to simple scenario in one domain when it comes to implementation, but I recommend trying that one out.

For the other side of the coin – things to remember when implementing database mirroring:

1. There was only one Redeemer! you can have only one mirror server – this limitation is overcome in 2012′s AlwaysOn, where you can have 4 replicas, but it has it’s costs.
2. Mirror is not easily accessible but you can use snapshots to query it.
3. Server-level objects are not transferred with mirroring – you have to take care of logins, Database Mail profiles, SQL Server Agent jobs or Maintenance plans yourself. See Books On-Line article on jobs and logins.
4. Mirroring requires full recovery model so you have to be absolutely sure about the transaction log of mirrored databases. Index maintenance, for example, can kill mirroring performance if you have 100GB of log records related to index reorganize sent to the mirror (and it will kill you twice – first the network, then the mirror server).
5. In SQL Server 2008 and 2008 R2 FILESTREAM and mirroring don’t work together. You can’t have both in one database. To be checked – SQL Server 2012′s FileTables and mirroring.
6. The failover is not instant – see Paul Randal’s post and Books On-Line. In short – it depends on the length of your REDO queue, so if you’re not monitoring it, then please do, just for the sake of not being surprised in the event of failover.
7. The failures not detected automatically – there are four cases here (again, I’m quoting Paul Randal):
   • SQL Server unavailability – detected in a second
   • Machine unavailability – default is 10 seconds, you may increase it using ALTER DATABASE SET PARTNER TIMEOUT
   • Unavailable log drive – it takes 20 seconds for a warning, and next 20 seconds (total 40 seconds) for database to be taken offline
   • Page corruption – might not even be detected
8. It’s not the witness who performs the failover, but the mirror! The mirror and the witness agree that the principal cannot be contacted and the mirror switches to a principal. If you don’t believe, set three Profiler sessions on all three mirroring participants and watch out for ALTER DATABASE statements when failover occurs.

Wrapping it all up, database mirroring is a really useful high availability technique (especially when combined with other methods – be it clustering or replication). It is easy to implement, does not have extra hardware requirements. Though it’s marked as deprecated in SQL Server 2012, it is going to be available and useful for at least couple of years, but it has it’s downsides which should be carefully considered when planning.