When working with Oracle, drivers are truly a pain to get working correctly. I will discuss my preferred choice and why for the following tools – SSIS, SSAS, and SSRS.
Without much question, you should use the Attunity tools for working with Oracle data in the Data Flow task. In SSIS 2008, the SSIS Connector is free and can be found here: http://www.microsoft.com/download/en/search.aspx?q=oracle%20connector. It includes the connection manager, source component and destination component. Without a doubt this is the only way to work with Oracle data components in the Data Flow task.
(NOTE: I cannot find the SSIS 2012 equivalent at the moment. However, Matt Massan’s blog post after PASS Summit 2011 notes more work is being done with Attunity.) UPDATE: I wrote this prior to a blog post from Matt Massan on support for SSIS 2012 with v2.0 of the Microsoft Connector. Check out Matt’s update on this: http://blogs.msdn.com/b/mattm/archive/2012/04/04/microsoft-connectors-v2-0-for-oracle-and-teradata-now-available.aspx.
However, this connector does you no good when working with the Execute SQL task. In SSIS 2008, I use the OLE DB provider from Oracle to create the connection used with Execute SQL Task. In my work with procedures in my first tip, I used the OLE DB provider with 2012 as well and it worked fine.
When working with SSAS 2008 and, until I know differently, SSAS 2012, I would recommend using the Oracle OLE DB driver from Oracle. This driver is not the fastest I have seen (third party drivers are marginally faster and the .NET driver is faster as well), but it has provided consistent results for the right price. Third party drivers will improve the throughput, but not substantially. The Oracle provided .NET driver is faster as well, but has an unchangeable active query timeout of one hour. If you have any processing times that exceed this, it will unceremoniously drop the connection. For these reasons, I have stuck with the OLE DB provider from Oracle which is not necessarily the fastest, but it has been the least painful to work with.
It is with SSRS I have seen mixed results. Primarily because of the better performance in the Oracle .NET driver. If you can guarantee that your reports will return their data in under an hour, this seems to be the best option. However, if you want to manage to a single driver set across all tools, you may find that the management of the OLE DB driver as the only driver makes sense in your organization.
Test, Test, Test
I have given you my experience using the drivers above. However, you may find value in purchasing a third party driver or you may find a different experience when you implement in your environment. Be sure to test and understand the implications in maintenance and system cost when choosing different drivers across your solutions.
Minnesota SQL Server User Group Meeting Review (4/17/12)
First of all, I have to thank the awesome speakers who presented at our user group meeting on Tuesday night, April 17. Mark Souza ( T | B ) for a great question and answer session. He was open about SQL Server and where it is heading. Some items that interested me is that SQL Azure was running the SQL Server 2012 platform in production by December 2011. Microsoft is committed to releasing more changed through the SQL Azure platform at a faster clip. This will allow them to continually improve the quality of releases for the boxed version as well. (Speaking of which, the boxed version is not going away any time soon.)
Mark also spoke about leaving data at its source and using tools to bring the data together for reporting and analysis. He touted the Hadoop integration efforts as key part of this strategy. If you have followed my blog for a bit, you know that I do a lot of work with Oracle and MSBI. As a result, I had to ask when a good version of Oracle drivers would be available. While he did not have a good answer on this at the time, he did mention that OData is a data access solution for me to look at. Look for a future blog post as I explore this protocol to ease some of my Oracle.
After Mark finished answering the variety of questions asked by the crowd we handed the microphone to Itzik Ben-Gan ( T | B ). As usual, he took an entire hour to help us understand that we did not know all of the possible ways to use the APPLY operator. However, I also learned about the DBCC OPTIMIZER_WHATIF function which allows you to change the system settings the Optimizer uses to create a plan. For instance, DBCC OPTIMIZER_WHATIF(1,16) will tell the optimizer to use 16 processors in its plan.
SQL Saturday #118 – Madison, WI (4/21/12)
Finally, I will be out at SQL Saturday #118 in Madison this weekend. I have two presentations there – A Window Into Your Data: Using SQL Window Functions and Performance Monitoring and Tuning in SSAS. Maybe I will see you there.
Earlier this month I published a blog entry on this same subject. In honor of the local Minneapolis launch event, I decided to expand the list. You can find five more reasons I am excited out on Magenic’s blog.
Here is the link and enjoy SQL Server 2012.
I just wanted to plug two events that are free SQL Server training.
First, Thomas LaRock (@sqlrockstar) and Jason Strate (@stratesql) are teaming up to bring you “Choose Your Own Adventure – Performance Tuning”. Join us at the Microsoft Technology Center in Edina, MN or online for this great adventure. This adventure starts at 3:00 PM CDT. More details can be found at http://minnesota.sqlpass.org.
Is that all?, you ask. No. Starting tonight at 0:00 GMT (7:00 PM CDT) is 24 Hours of PASS. Check out the awesome roster of speakers including the likes of Denny Lee from SQLCAT, Marco Russo, and Dejan Sarka. These sessions run through the night and will be close captioned in 15 languages, making it a truly international event. Oh, did I also mention, I will be speaking at this as well.
Take advantage of these free training opportunities by leaders within the SQL Community. We all look forward to seeing you at both or either of these events.
By now many of you have had the opportunity to hear or see something about the next release of SQL Server. It is later on March 7, launch day. I have enjoyed a number of sessions on the SQL Server Launch site. Many people have talked about what they look forward to in the product, so I thought I would discuss some of the things that excite me.
SQL Server Integration Services Improvements
I think the updates to SSIS make the best case for early adoption of SQL Server 2012. If you are currently using SSIS or are planning to use SSIS, the changes to the platform are significant and reduce the overall development time for enterprise class solutions. And yes, developers will see many changes that will further help productivity, “beyond rounded corners.”
As I work with large data stores for reporting and loading SSAS, this index type will greatly improve performance. Once again Microsoft has made significant improvements for large, set based query operations.
Enhanced Window Functions
I have done a lot of work with window functions in Oracle lately. I am thrilled to see similar functionality being added to SQL Server. This allows for more complex aggregations and query results while not sacrificing performance. Join me during the 24 Hours of PASS to see these functions in action.
Wow. This visualization tool really fills a gap within in Microsoft’s BI offering. I am excited to see this in operation at customers as this is a game changer for the end user. The replay capability is both cool and very functional.
Extended Events for SQL Server Analysis Services
While I have not worked with extended events in SQL Server 2008 as much as I would have liked to, the introduction of extended events for SSAS will allow us to follow the path taken by many SQL Server DBAs who have used these events. This will allow us to monitor SQL Server more closely while minimizing the impact we experiences using SQL Profiler.
These are just a few items within SQL Server 2012 that excite me, what excites you?