BIG SQL Server Updates!

Wow, take a vacation for a week and all kinds of great things get announced for SQL Server!

For those that missed it, with SQL Server 2012 SP1 Cumulative Update 4 included changes that allow for Power View to now access Multidimensional cubes! Previously Power View was only able to use the newer Tabular Data Models, but now you can use your existing Multidimensional cubes built in SQL Server Analysis Services (SSAS). This change also makes it so that you can write the same DAX queries in your Power View reports against those same Multidimensional cubes without requiring you to use MDX (MultiDimensional eXpressions). The only downside is that it only works with the Power View integrated into SharePoint 2010/2013. Eventually the Power View that is built into Excel 2013 should be able to do this as well, but at this time that update is not yet available (keep your eye on my blog I will make sure to mention it as soon as it is available).

The other big news came out of TechEdNA 2013 in New Orleans today, the next release of SQL Server is going to be named SQL Server 2014! I heard rumours that the SQL Server team was moving away from the R2 name that they used to use for the in-between release (Windows Server teams are still using R2 as they announced Windows Server 2012 R2 as well). The biggest new feature of SQL Server 2014 will be the previously announced "Hekaton" project or In-Memory OLTP as they are calling it now. Should help to provide some amazing performance for databases. Along with that there are many more updates to futher add more cloud/Azure functionality and make it easier to transition to that from traditional on-prem servers. For more details, see this post from the Microsoft SQL Server team and keep you eyes here as I will keep you updated on what I hear as well!

More exciting times ahead with SQL Server, glad I got those SQL Server 2012 certifications out of the way, so that I can be ready for the 2014 versions next year!

SQLSaturday #200 - Philadelphia

Thanks to everyone that attended my "ABCs of CDC with SSIS 2012" at SQLSaturday #200. Got lots of great questions during the session and great reviews/feedback after the session as well. As promised, here is the link to download all of the materials from the session.

I had a fantastic time in Philly visiting with my Dad, who lives in the area and getting to do the tourist thing for a couple days. Even got to try the famous Pat's Cheesesteak (witout onions) and visit the Valley Forge National Park. Lots of fun and hoping that I don't wait another 15 years to get back to Philadelphia.

Thanks again to all of the organizers/sponsors/speakers/volunteers of the SQLSaturday #200 event, things ran very smooth and it was a good turnout. I was honored to be part of the bi-centennial event and to meet so many new people.

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.