MSCA & MCSE

I wanted to put this post up earlier, but waited for official confirmation from Microsoft. Last Wednesday I took the 70-462 (Administering Microsoft SQL Server 2012 Databases) certification exam and passed it!

Those that know the new SQL Server 2012 certification setup are saying, "So what, you still have 1 more to pass before MCSA and 2 more after that for MCSE". Yes, that is correct, but thankfully I was able to take all 5 of the exams in the SQL Server 2012 MCSE Business Intelligence series when they were in beta form last Spring. After waiting a few months to get the results on those beta exams (when you take them in beta you don't get the immediate results like you do for normal exams, they have to manually graded and evaluated) I found out that I had passed 3 out of the 5: 70-463 (Implementing a Data Warehouse with Microsoft SQL Server 2012), 70-466 (Implementing Data Models and Reports with Microsoft SQL Server 2012), 70-467(Designing Business Intelligence Solutions with Microsoft SQL Server 2012). Ironically enough these are the last 3 exams in the series, not the first ones. I already passed 70-461 (Querying Microsoft SQL Server 2012) in March after a few attempts as well.

With that I got my confirmation on Saturday morning from Microsoft that I had achieved the requirements for the SQL Server 2012 MCSA (Microsoft Certified Solutions Associate) certification. While I was relieved to get that message, I immediately logged into the MCP (Microsoft Certified Professional) website to see if everything was there for MCSE (Microsoft Certified Solutions Expert) Business Intelligence as well. For some reason it was only showing the MCSA, not the MCSE. I was a bit surprised, but figured I would wait a couple of days to see what happened. Sure enough on Sunday morning I did get the message that I had the MCSE as well. Guess the MCP systems run a pass each night and can only pick up one of these certifications at a time.


For those of you out there that are also trying to get the new SQL Server 2012 certifications, keep pushing yourself to get them. For me getting these certifications is more of a personal goal, even though there are some benefits from Pragmatic Works as well. Hopefully you will also get the support your need. I have linked to each of the pages on the Microsoft site for the exams that I took. Microsoft has also published a great series of pages and videos on the Born to Learn 90 Days to MCSA series. If you are in the Denver area there is a Certification Study Group that meets every Thursday (except the 3rd Thursday of each month) at the Cherry Creek Presbyterian Church - Community Life Center (contact marketing at denversql dot org for more information).

If you have never taken a Microsoft certification exam before, I would encourage you to sign up to take one to get an idea of what they really are like, even if you really aren't ready. If you can sign up for one before May 31 there is a Second Shot deal going on, where you can get a voucher code that allows you to retake an exam that you didn't pass the first time again for free. Just make sure you request the voucher first and when you setup your exam appointment put that code into the voucher box on the registration form, then you will need to use that same voucher code again when you register to retake that same exam again. The Second Shot vouchers only work if you put it in the first time you take that specific exam.


This was my big goal for early 2013, so I'm glad to have that one off my back. I'm enjoying not worrying about studying for any other certifications right now, but I'm sure I'll find more to challenge myself with! I'm pushing myself to do more speaking engagements this year and so far it looks like it is paying off with 2 SQLSaturdays over the next 2 months (#215 in Jacksonville, FL & #200 in Philadelphia, PA). I'm planning on speaking at the Charlotte BI Group in August and I will be submitting for at least 3 other SQLSaturday's for September thru October. Plus, I did submit for PASS Summit as well, which hopefully I will get chosen for as well.

Good luck!

ABCs of CDC with SSIS 2012

Today I presented a session as part of Pragmatic Works free "Training on the T's", called "ABCs of CDC with SSIS 2012". The session was attended by 507 people and I received a lot of positive feedback (even though my voice as a bit rough because of a cold that I'm getting over).

This session focuses on defining what CDC (Change Data Capture) is for those that have heard of it and maybe even know that it has been offered with SQL Server 2008 and greater, but didn't really know how to use it. I also show how it is much easier to consume the CDC data now with SSIS 2012, since it now includes native components for CDC.

I have posted all of the slides and demo materials for this session to my SkyDrive for anyone to download and try out. The only thing that you will need to get this up and running in your environment is the AdventureWorksDW2012 database from CodePlex  and a SQL Server 2012 Enterprise/Developer/Evaluation Edition instance with the Database Engine and SSIS setup on (CDC only works on these editions of SQL Server). You will need to make sure you have full admin rights on this instance since the commands to setup CDC require that level of permission.

Below are a few of the most common questions that I received in the session with answers:

What is the performance impact for using CDC on a database/table?

This will vary based on the amount of changes that are being done to the database/table you are using CDC on, but Microsoft has optimized this process to use as little CPU/memory as possible. As with any process that you are going to add to a database you should test this in your own environment to know for sure what the impacts are. There is a great White Paper available on MSDN that covers many different scenarios and impacts of CDC on each.

How is CDC different from Replication, Mirroring or other Disaster Recovery/Backup Scenarios?

CDC is only going to track the changes to the data in the database/table that it is implemented on, so it will not be able to capture security changes or many other DDL changes done to a table (columns being added/removed are tracked, but with limits, see MSDN article). The main purpose behind CDC is for a quick way to track the changes to the data made over time and to periodically apply those changes to another database with the same schema. CDC is not meant to be used in place of Replication, Mirroring, Log Shipping or other disaster recovery type of scenarios. It is possible to use some of these other technologies with a database/table that has CDC enabled (see the "Change Data Capture and Other SQL Server Features" section of the MSDN article linked here). The most common use for CDC is where a source database needs to be loaded into a data warehouse or the staging area for a data warehouse and you want to get all of the data inserts, updates and deletes applied appropriately without using triggers or other custom code that will have to be maintained.

Can CDC be enabled on any database/table?

Yes it can, but it must be enabled at the database level first and then the table(s) in that database that you want to track with CDC have to be enabled separately. CDC is not on by default on any databases or tables when it is first setup. There are also some important column restrictions that are shown in the slides that I have attached above.

Is it possible to have the CDC track only Updates and Inserts and not Deletes (or any combination of these)?

The CDC stored procedures provided by Microsoft to enable CDC on the database and table do not have any options to ignore certain changes, so once it is enabled it will track all Inserts, Updates and Deletes done to that table. When you are creating the processes that use the CDC data to apply the changes to the destination database, you can choose to not implement any of the types of changes you don't want by using the _$operation column and looking for the appropriate value(s). If you are using SSIS 2012 and the new CDC Splitter, then you can choose not to implement any of the data flows off of it that you don't want to have processed.

Can the SSIS 2012 CDC components read the CDC source data from older versions of SQL Server?

Yes! The SSIS 2012 CDC components can connect to a SQL Server 2008/2008R2/2012 CDC enabled database/table and use that data to then update any SQL Server 2008/2008R2/2012 destination (along with the possibility of other databases that have the required ADO .NET data providers in SSIS 2012). See this link for details on using SSIS 2012 CDC with Oracle!

For more details on how CDC works, there are great resources available on MSDN.

If you have any other specific questions, please feel free to email me at the address in the slides attached above or send me a message on Twitter.

SQLSaturday #183 Albuquerque, NM

I'm starting off my 2013 speaking engagements at SQLSaturday #183 in Albuquerque  NM on February 9th. Currently my session, "SSIS 2012: More Than Just a Pretty UI" is scheduled for 4:15pm in Room 4. For those that can, please sign up on the SQLSaturday #183 site. The event is free to attend, but you will have to pay $10 if you want the lunch provided onsite. I have attended many SQLSaturday's over the last few years and have been honored to speak at many as well as organizing the Denver SQLSaturday in 2012 (stay tuned for more news on SQLSaturday #190 in Denver on 9/22) and I think this is some of THE BEST SQL training out there, done by people in the local community and from around the world. All of the speakers and volunteers put on these events in their free time with the help of sponsors to offset the costs.

If you have the time and you are in the Albuquerque, NM area, please register and attend SQLSaturday #183. If you are not in the Albuquerque area, please visit the main SQLSaturday website and see if there is an event in your area!

SQL Saturday #153 and PASS Summit 2012

For those in the Salt Lake City area this weekend, I will be speaking at the SQL Saturday event that will be held on Saturday, October 20 starting at 9am. I am currently scheduled to speak at 2:45pm in Room 206 on SSIS 2012. For those that are new to the SQL community, SQL Saturday's are free events held throughout the world where SQL Server Professionals share their experiences for a day of learning and networking. If you are in the SLC area this Saturday, you should come by! For more information and to register, please go to http://www.utahsqlsaturday.com. See you on Saturday!

I will also be attending PASS Summit 2012 in Seattle coming up on November 5-9. If have the means to go to PASS Summit, I would highly recommend it, it is the biggest SQL Server focused conference in the US and is a great place to meet SQL Server Professionals from all over the world. There are so many things to do and see both at the conference and after the conference. To get all of the details and register, go to http://www.sqlpass.org/summit. If you are from Colorado and traveling to PASS Summit 2012, we will be having our annual dinner at Elliot's Oyster House on Thursday, November 8th at 6:30pm. You will have to cover your own food and drink, but it is a great chance to meet with the SQL Professionals in the Colorado area outside of the conference. If you are interested, please register at http://cosummit2012dinner.eventbrite.com. Hope to see you there!

SQL Saturday #169 Denver Wrap-up

SQL Saturday #169 Denver was held last Saturday (9/22) at the Cherry Creek Presbyterian Church - Community Life Center, after months of planning and worrying about how the event would go, it turned out to be a very successful event! We met our registration cut-off amount of 300 late on Friday night and the final count at the end of day on Saturday was around 220 that actually attended! Really good since we did not hold a SQL Saturday in 2011 (the first for this area was in September 2010) and shows just how much the SQL Server community has grown.

We started the planning of this event in May/June, which is a bit late for an event of this size. We pulled together a great group of volunteers from the area to help us out and that made all the difference. It is great to see so many people that are willing to give up some of their free time to make sure that we had a successful event. Without them this event would not have been as good as it was, so thank you to everyone that volunteered to help!

The number of sponsors that we had was also a huge help to make the event such a success. Big kudos to Confio Software, they are a local Colorado company and they came through big as a Gold sponsor with lots of last minute support in various forms. I also want to send out a big thank you to Quanta Intelligence for sponsoring the Speaker/Volunteer dinner on Friday, everyone had a great time before all of the hard work on Saturday started. Another big thanks to Datavail who came in as a Gold level sponsor and sponsored the After Party on Saturday evening. Without these sponsors and all of the others - DawaBI, Pragmatic Works, Xtivia, PASS, Red Gate, Fusion IO, Quest Software, COZYROC, Qortex, SQL Cruise, Sapien Technlogies, McGraw Hill, Wrox and Pluralsight we could not had this event.

Since this was the first time that I was the lead coordinator on this kind of an event I decided early on that I would not present a session, since I knew that I would already have enough stress for the day. That was a good decision because there were many challenges that needed my attention throughout the day. We have a list of things that we know about now and will keep in mind for the setup of the next event, and for those wondering, yes I'm sure that we will be having another SQL Saturday in the near future. Overall it was a great learning experience I'm looking forward to the next one and being able to enjoy it more and to roam around and soak it all in more.

To get a different perspective on the event, check out the following blog posts that have been posted by others:

http://diditsave.wordpress.com/2012/09/25/sql-saturday-169-denver-recap/

http://sqlserverperformance.wordpress.com/2012/09/23/dmv-emergency-room/

http://extofer.com/blog/sql-saturday-169-in-the-bag 

http://www.zero1design.com/2012/09/23/what-i-learned-at-sql-saturday-169/

Thanks again to everyone that was involved in our SQL Saturday and a big thanks to everyone that attended the event!

SQL Saturday

Wanted to put out a quick post that I have not had the time to put up until now. If you are going to be in the Denver, CO area around September 22, 2012 I would encourage you to register for a day of SQL Server technical training given by many local and national speakers. Yes, that's right we are doing SQL Saturday #169 at the same location as 2010, Cherry Creek Presbyterian Church (10150 E Belleview Ave., Greenwood Village, CO 80111).


We are asking that anyone that registers pay a $10 fee for lunch, this will help us to provide all attendees with a lunch that is not pizza and provide plenty of beverages throughout the event. Please register to attend and we look forward to seeing you there!


If you are a speaker, please submit your session abstracts on our Call for Speakers page. And if you are sponsor, please go to our Sponsors page to help us out!


I would also like to mention that Brian Knight, Founder of Pragmatic Works will be presenting a 2 day SSAS workshop on September 20-21 at the Denver Microsoft office (7595 Technology Way, Suite 400, Denver, CO 80237). If you are interested in attending that event, you can go to the Pragmatic Works registration page linked here. You will need to bring your own computer to that workshop with the SQL bits already installed along with AdventureWorks installed, more details can be found on the link above.

Also, in other SQL Saturday news I will be presenting at SQL Saturday #159 in Kansas City, MO on August 4, 2012. Looking forward to getting some great BBQ in Kansas City and presenting my "SSIS 2012: More Than Just a Pretty UI" session, so come see me if you are going to be at SQL Saturday #159!

I will post more on my presentation at SQL Saturday #159 and our own SQL Saturday #169 over the next few months!