Denver SQL Server User Group - May 16, 2013

Last night I presented "Capture Change and Apply it with Change Data Capture and SSIS" at the Denver SQL Server User Group meeting as a representative of our sponsor for May, Pragmatic Works. Session went well, got some really good questions and comments to make the session even better which I will be working on before I present it again at the Midlands SQL Server User Group next month!

For those that want to use all of my sample code from the presentation, feel free to download it from the embedded link below and try it out on your own computer. The requirements for these samples to work are SQL Server 2012 Evaluation, Developer or Enterprise Editions, AdventureWorks2012 and AdventureWorks2012DW. Also as I mentioned in the session you must have full admin rights to the SQL Server instance that you are going to try and setup CDC on, so a local instance on your own workstation/VM is recommended.

Thanks again to all of those that attended the Denver SQL Server User Group meeting last night!

SQLSaturday News

SQLSaturday #190 in Denver on September 28th is going to host pre-cons on Friday, September 27th! This was something that the Denver SQL Server User Group wanted to do after the great success we had last year with SQLSaturday #169.

While we do charge for these pre-cons, we think that at $99 per person for a full day of training it is an amazing value. Since this is our first year offering pre-cons at our SQLSaturday event we didn't want to go overboard with offering too many options, so we selected 3 pre-cons that we think offer a great range of topics for different SQL Server Professionals (thanks to everyone that submitted pre-cons, we wish we could have hosted all of them). Below are topics and speakers that we have selected along with the links to the full abstract and registration page on EventBrite:

Real World SSIS: A Survival Guide by Tim Mitchell
SQL Server Internals from the Practical Angle by Dmitri Korotkevitch
Predictive Analytics in the Enterprise by Carlos Bossy

All 3 of these pre-cons will be all day on Friday, September 27th and the registration fee does include a continental breakfast, lunch, snacks and refreshments throughout the day. The location for the pre-cons is the same as last year's SQLSaturday in the Cherry Creek Presbyterian Church - Community Life Center (10150 E. Belleview Avenue, Englewood, CO 80111) and there will be wi-fi available to all attendees.

If you have any questions about the pre-cons, please email the event committee.

While I'm on the subject of SQLSaturday just wanted to remind everyone that I am presenting my "ABCs of CDC with SSIS 2012" session at SQLSaturday #200 in Philadelphia on June 1st, so please register if you are going to be in that area at that time (the registration is currently waitlisted, but please register as there might be cancellations and the organizers will keep you updated via email). I will actually be flying out to Philadelphia on the Wednesday before to spend some time with my family that lives in the area, so looking forward to some vacation time on the East coast before the SQLSaturday. Hope to see you all there!

ABCs of CDC with SSIS 2012 - Addendum

I have been presenting the "ABCs of CDC with SSIS 2012" session for a few months now at various User Groups and SQLSaturdays and I learn something new about CDC (Change Data Capture) and the other similar options available in SQL 2012 each time I present it. As I was searching for information the other day I discovered a feature that was delivered with the RTM (Release to Manufacture) version of SQL 2012 that I had not heard about before, Change Data Capture for Oracle by Attunity.

This feature is a way for SQL Server DBAs to get all of the benefits of CDC from Oracle databases. The executables for this feature are on the install media for SQL 2012 in the Tools\AttunityCDCOracle folder (available in both x64 and x86 versions). The 2 executables are AttunityOracleCdcDesigner.msi and AttunityOracleCdcService.msi. Both of these executables are installed on a Windows Server, not on the Oracle system itself (unless you are running Oracle on Windows). So the server that you are installing on needs to be able to connect to the Oracle database(s) that you are trying to capture data and changes on. The Service executable is the one that always runs on the Windows Server and pulls all of the changes from the Oracle database(s) to the Windows Server to be stored on a SQL Server instance in the same form that CDC on SQL Server stores changes. The Designer executable is the administration front-end for setting up the service, which will launch as an MMC (Microsoft Management Console) snap-in.

Once you have these changes in the SQL Server database, then you can use all of the same processes you use for SQL Server CDC data, including using the new CDC components in SSIS 2012.

For more details on getting this installed, this link will take you to the TechNet article covering this feature.

To see the applications in action, this link will take you to the video of a presentation done at PASS Summit 2011 by Matt Masson and representatives from Attunity (fast forward to around 10:50 to see this feature).

The best part of this is that since it is a built-in feature of SQL Server 2012, it's free to use with SQL Server 2012!

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!

SQLSaturday #215 & SQLSaturday #200!

I've got a couple of upcoming events that I will be speaking at that I wanted to make sure I spread the word on.

First on 4/27 I'll be in Jacksonville, FL to present at SQLSaturday #215. Please visit their website to register and view the complete schedule for the day! They are also doing a pre-con on 4/26 with Brian Knight and Devin Knight on Building a SQL Server 2012 BI Platform, should be a great session (I'd be there too, if I didn't have to drive from Columbia, SC to Jacksonville on Friday afternoon).

And earlier today I found out that I will also be presenting on 6/1 in Philadelphia, PA at SQLSaturday #200! I really happy to be spreading out and presenting in more new places this year and Philly will be great since my Dad lives in the area and will give me a chance to spend some time with him in the days before the SQLSaturday (hopefully I can convince him to come out to the SQLSaturday and see my present too). The schedule for SQLSatuday #200 should be up sometime Monday, so check this link and it should be there soon. Click here to register for SQLSaturday #200!

Hopefully I have some more events that I'll be presenting at in the future as well, looking at SQLSaturday #191 in Kansas City (had a great time presenting there last year) and SQLSaturday #227 in Charleston, SC on the Saturday before PASS Summit 2013 in Charlotte, NC.

T-SQL Tuesday #41 - Presenting and Loving it!

My first T-SQL Tuesday post!

For those that don't know what T-SQL Tuesday is, you can get a brief overview from the host blog for this week @ Bob Pusateri's The Outer Join blog post. The topic for this T-SQL Tuesday is Presenting and Loving it!

When I saw that this was the topic for this week I decided that this would be a good one to jump into T-SQL Tuesday with, since this really is how I got introduced to the SQL Server Community. 

I was not really into doing much with presentations at work and I didn't really have to do it much in College either. I had done some presentations in High School as part of various classes, but I was not very good at presenting or speaking in public at all back then. So, it took a lot for me to even consider doing any presentations again after all of these years.

I started to attend the Denver Visual Studio User Group meetings on a regular basis about the same time that I started to learn C# and .NET and found that I was learning a lot from those meetings and wanted to share some of my knowledge with the group. I had been applying my C# skills to build custom .NET web front-ends for Reporting Services right after it went live in 2002. Since that was such a new product it was something that lots of people wanted to know about, so I created a couple of intro 30 minute presentations and did them at the DVSUG meetings and received a lot of positive comments about expanding on it. From there I created a series of labs that I presented over multiple separate meetings which helped bring many members back to those lab sessions after some low attendance was threatening to cancel them entirely.

After my presentations at DVSUG were going well, I was looking for other groups that I could present this material at and that is when I found the Denver SQL Server User Group along with the other SQL Server User Groups in the Colorado Front Range. I presented some of the same presentations at these groups and had the same success. 

From there I got to know the people running the Denver SQL Server User Group and decided to make the step to volunteer for the Board and help to build the local community in even more direct ways. Just as my career progressed from a COBOL programmer to .NET and then to a SQL Server BI Developer and now a BI Consultant, I have seen my role in the local community grow the same way and it all goes back to that first thought of wanting to share something that I had been working on with others. I have been rewarded many times over by presenting at many different User Groups and SQLSaturdays across the country (that is the great thing about being a consultant is you can use the trips for work to visit/present at other groups) and hopefully being able to present at the PASS Summit in the near future.

If you are thinking about making that step to share and present your ideas, DO IT! It is the one thing that I did all of those years ago that I have never regretted and always encourage others to do it to. Yes, we all get nervous before getting up in front of people to speak, but you can harness that nervousness and use it. Just contact the leaders of the group that you are interested in speaking at and they will give you all of the support you need to be successful at presenting. All of us User Group Leaders are always looking for new speakers in our communities, so if you haven't done it yet, WHY NOT? It was worked out so well for me that my job at Pragmatic Works even has presenting as a big component of the job description and it was one of the main reasons for going after the position there.

SSIS - Using Caching in Loops

Earlier this week I had an SSIS 2008R2 package that I wanted to try and cut down the runtime from over 5 total hours. This package does a lot of work in many separate Data Flows, but a bulk of the time in this package is spent in a For Each loop that iterates through "snapshots" stored a database. These "snapshots" are not actual database snapshots, just a day, month or quarter worth of data that get loaded into a database and uses a unique ID (date/sequence number in this case) on every single row. Normal processing of this database is to just include a WHERE clause in your SQL statements to filter by the appropriate "snapshot". The For Each loop that I had setup needs to do a bunch of logic for different tables within this database, but it needs to do this filter by "snapshot" to cut down on the number of rows being processed.

For regular OLE DB source queries you can specify these kind of parameters very easily in SSIS using a ? in your query and then telling SSIS what variable to plug into that ? at runtime. That is perfect for that use and I used that as much as possible in this setup, but my other issue is that it has many Lookups that need to be done within the For Each loop as well and because of the way the Lookup queries are setup you cannot specify the same kind of parameter/variable based queries. I know that there are some ways to "fake" this out by changing the caching mode from Full Cache to Partial Cache, but that solution requires a lot of crazy changes to make it work and at that point the package becomes very difficult to support.

After doing some more digging around I found out about using Cache Connection Managers and the Cache Transform to load this Cache Connection Manager (I had not previously used either of these components). Initially I thought that this could solve my issue, so I setup a new Data Flow as the first step inside the For Each loop and load up a few of these Cache Connection Managers using the same parameter based queries and the variable from my For Each loop to create the appropriate filtered datasets for use in my Lookups. Then the Lookups can be modified very easily to use these Cache Connection Managers (you do have to setup Index columns in the Cache Connection Manager for every column you are going to use for mapping in the Lookup).

All of this was very quick and easy to setup and with that I kicked off a test and it all worked perfectly for the first time through the For Each loop, then the package ended with the following error message:

[Cache_xxxxx] Error: The component "Cache_xxxxx" (22) cannot write to the cache because component "Cache_xxxxx" (22) has already written to it.

At this point I knew that I was not going to be able to use the Cache Connection Managers within a For Each loop and get unique result sets for each time through the loop, since Microsoft has setup these Cache Connection Managers a write once object. Very disappointing that Microsoft did not find a way to make it so that these Cache Connection Managers can be purged and reloaded. I have heard of some people doing something like this by writing the cache data out to files instead of keeping it in memory, and that solution sounds good, but I didn't want to go do that route since I don't have any access to the servers themselves to make sure it works and I was on a very strict deadline to get a solution working.

Unfortunately until Microsoft makes some updates to how these Cache Connection Managers work, I don't really see an easy way to use them in this context, which is too bad, because that first run through the loop was lightning fast. And yes, I did try to move the Data Flow that fills the Cache Connection Managers outside of the For Each loop and just have it load everything. The solution did not work either in my case because I needed to load over 24 million rows of a very wide table when it is the entire dataset, instead of the "snapshot" filtered version I wanted to do.

Now that I have some experience using the Cache Connection Managers and Cache Transform, I'm going to keep my eyes on updates that are made to both of these components to see if they are more useful in the future in loops.

Stay tuned for a future blog post where I will show all of the setup steps for using Cache Connection Managers and Cache Transform along with how it works with the Lookup transform in detail (with plenty of screenshots).

SQLSaturday #215 - Jacksonville, FL

Just a quick announcement that I have been selected to present at SQLSaturday #215 in Jacksonville, FL on April 27th!

Looking forward to hanging out with my Pragmatic Works colleagues again and see all of the attendees at the event. For more information on the event, please visit their page and make sure you register, because space is limited and it is FREE! SQLSaturday's are held all around the world and there are usually multiple events going on at the same time in various locations, if Jacksonville is not convenient, make sure you check out the SQLSaturday site for other locations and dates.

Currently I'm scheduled to present "ABCs of CDC with SSIS 2012" in BI Room 1402 @ 1:15pm, but that schedule may change so make sure to check the schedule on the site.

Building a Microsoft BI Demo Environment

I've been asked at multiple meetings in the last month what is the best way to build a Microsoft BI demo environment. There are a few ways that you can do this:

Excel 2013

If you have access to Excel 2013 (you can get the 60 day evaluation version here), both PowerPivot and Power View are included as add-ins that don't require additional downloads (or SharePoint), just activate them from the File menu, select Options.

Then select Add-Ins from the Options left navigation panel. In the Add-Ins panel change the Manage dropdown at the bottom of the panel to COM Add-ins and click Go....

From the COM Add-Ins popup, check the boxes for Microsoft Office PowerPivot for Excel 2013 and Power View and then click OK to close the popup.

That should add the POWERPIVOT menu to your Excel 2013 menu bar and allow you to build PowerPivot workbooks.

To create Power View reports, just go to the Insert menu and click on the Power View button that should appear between PivotChart and Sparklines items in the ribbon.

With Excel 2013 you can create the PowerPivot/Power View workbook and save them as normal Excel workbooks without requiring SharePoint or other software on your computer.

Microsoft Virtual Labs

Microsoft has posted virtual labs that you access with an internet connection. All of these labs are provided free of charge, but there is a limit of time you can access each lab. This link will take you to a list of all available labs, or use this link to go right to the Power View lab or this link for the PowerPivot lab. There are also some TechNet virtual labs that you can access from this link as well.

Virtual Machine

The last and most complex to setup option is to build your own complete VM (or VMs). For this option you will have full control over the environment, but you will need to install and configure all of the software yourself. For a complete environment you will need to install Windows Server, SQL Server, SharePoint and Office. For help getting all of this installed and configured in the correct order, see this post put together by Microsoft. This guide is also available, but it is a bit dated since it is using SQL 2008R2 and SharePoint 2010. There is also a guide for specifically setting up SharePoint 2013 or SharePoint 2010. All of the software that is required for setting up this VM(s) is available in evaluation versions, just keep in mind that these versions will expire after a period of time (180 days max). The other thing to keep in mind is that you can buy a TechNet Professional annual subscription for $349 (renewal $249 after first year), you might also find this cheaper online. I recommend the TechNet Professional license over the other versions of TechNet because it does include license keys for all of the Enterprise server products that Microsoft offers.

Microsoft has also provided a version of a VM (previously referred to as the Microsoft All-Up BI Image) that you can download, this VM can be run in HyperV, VirtualBox or VMWare and is available at this location. Here is a review of this VM from Dan English. Microsoft has said that they are working on a new version of this image, but it is currently not available for download.


Personally I like the control of having everything in a VM that I have built, but it is a lot more work. The good thing is you will become very knowledgeable on how to install and configure all of this software (comes in very handy for Microsoft Certifications). I do buy a TechNet Professional license for my own use every year, so this is how I am able to license all of the Enterprise versions of the OS, SQL Server and even SharePoint. I also have Excel 2013 installed on the laptop itself as well, so that I can do a quick demo if necessary without spinning up a whole VM.

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.