SQL Server provides various options for interoperating with Oracle
1) SSIS - Microsoft SQL Server 2005 Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.
Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, for executing SQL statements, or for sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service, the Integration Services service, for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model. Integration Services replaces Data Transformation Services (DTS), which was first introduced as a component of SQL Server 7.0.
The typical uses of Integration Services are: a) Merging data from heterogeneous data sources like Oracle, Teradata, Excel etc., b) Populating data warehouse and data marts, c) cleaning and standardizing data, d) building business intelligence into a data transformation process and d) automating administrative functions and data loading.
SSIS provides a modular architecture to create a workflow and to connect to various data sources including Oracle. SSIS can connect to the following sources out of the box: a) DataReader, b) Excel, c) Flat file, d)OLE DB, e) Raw file, f) Script Component, g) XML and to the following destinations – a)Data mining model, b) DataReader, c)Dimension processing, d) Excel , e) Flat file, f) OLE DB, g) Partition processing, h) Raw file, i) Recordset, j) Script component, k) SQL Server compact and k) SQL Server
There are various ODBC/OLEDB/.NET providers to connect to Oracle that are provided both by Microsoft and Oracle.
2) Replication - Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet. Replication uses a publishing industry metaphor to represent the components in a replication topology, which include Publisher, Distributor, Subscribers, publications, articles, and subscriptions
Many businesses and organizations have data stored in databases from multiple vendors. Integrating this data is often a key component in allowing systems to work together in an organization. Replication allows you to integrate heterogeneous data in two ways:
a. Use Oracle as a source for data that can be replicated to Microsoft SQL Server, IBM, and Oracle databases: With Microsoft SQL Server 2005, you can include Oracle Publishers in your replication topology, starting with Oracle version 8.0.5. Publishing servers can be deployed on any Oracle supported hardware and operating system. The feature is built on the well-established foundation of SQL Server snapshot replication and transactional replication, providing similar performance and usability.
b. Use SQL Server as a source for data that can be replicated to IBM and Oracle databases: Subscriptions are supported for the two most recent versions of Oracle using the most recent version of the OLE DB provider.
3) Linked Servers - A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages: Remote server access, The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise, The ability to address diverse data sources similarly.
Typically, linked servers are used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server parses the command and sends requests to OLE DB. The rowset request may be in the form of executing a query against the provider or opening a base table from the provider. For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server.
4) Data drivers for use in custom applications – You can use the set of data drivers (OLEDB, ODBC, .NET) that are available to create a custom application for interoperating SQL Server and Oracle to give you the most flexibility at the cost of additional development if none of the above options suit your needs.
Click Here for a good discussion on this topic
Engine and algorithm improvements: To improve the accuracy and stability of some predictions in time series models, a new algorithm has been added to the Microsoft Time Series algorithm. Based on the well-known ARIMA algorithm, the new algorithm provides better long-term predictions than the ARTxp algorithm that Analysis Services has been using. (ARTxp is an auto-regressive tree algorithm that is optimized for either a single time slice or short-term predictions.)
Enhanced mining structures: When creating a mining structure, you can now divide the data in the mining structure into permanent training and testing sets. The definition of the partition is stored with the structure, and you can reuse the test set with any mining models that are based on that structure.
You can now query data cached in a mining structure, much like you could query case detail from a model.
You can now attach filters to a mining model and use the filter during both training and testing. Applying a filter to the model lets you control the data that is used to train the model and lets you more easily assess the performance of the model on subsets of the data.
Cross-validation is an established method of assessing the accuracy of data mining models. In cross-validation, you iteratively partition the mining structure data into subsets, build models on the subsets, and then measure the accuracy of the model for each partition. By reviewing the returned statistics, you can determine how reliable the mining model is and more easily compare models that are based on the same structure.
Data Mining add-ins for Office 2007: Market Basket Analysis and Prediction Calculator have been added.
For more info:
What's New (Analysis Services - Data Mining): http://msdn2.microsoft.com/en-us/library/bb510513(SQL.100).aspx
Here is a slide deck I recently used to discuss using SQL Server in a virtual environment:
Issue:
4 instances of SQL Server installed on a 4 node cluster with any SQL Server instance possibly failing over to any other node. All 4 nodes have SSIS installed on them. (I am assuming you have not clustered the SSIS service). You want users to be able to create and store packages to the SQL Server MSDB database on the cluster and schedule the packages. The users will have SSIS and Client tools installed on their local machines.
Approach:
On the users client machine, the SSIS configuration file needs to be modified to specify the information about the location of the MSDB database. (This file is by default at: C:\Program Files\Microsoft SQL Server\90\DTS\Binn\msdtssrvr.ini) Here is an example:
The names of the SQL Virtual servers here are SQLVirtualServer1 and SQLVirtualServer2. I am assuming you want the client to have the ability to save packages to these two servers. If you want the client to save packages to all 4 SQL instances, then you add the other 2. If you want the client to just use one SQL instance, you only need to specify that.
The ServerName element can contain any name that you want. This dictates what shows up in Management Studio.
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name> VirtualServer1MSDB </Name>
<ServerName>SQLVirtualServer1</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>VirtualServer2MSDB</Name>
<ServerName>SQLVirtualServer2</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
The key thing to note here is the SSIS is a separate component from the database engine in 2005. So, when the SQL database engine fails over to another node, it will use the SSIS installed on that node. For example, assume you have the configuration below:
Node names Installed Services
Node A Database Engine A, SSIS A
Node B Database Engine B, SSIS B
When Database Engine A fails over to Node B, it will use the SSIS install on node B as SSIS itself is not a clustered resource and is a standalone component. The entire management is done from the client machine in your case which always connects to the database engine only since that is where the MSDB database is and so always makes a straight connection to the database engine. So, you will never need to make a connection to a remote Integration Services instance causing delegation failures.
I was recently asked this question and so did some research and here are some of the options:
1) XMLA is the protocol designed for universal access to SSAS from heterogeneous clients - http://msdn2.microsoft.com/en-us/library/ms187178.aspx
2) 3rd party components are available to make this work
SQL Server 2005 includes a number of different services and it is an interesting topic of discussion on what services amongst these can be clustered and what services are multi-instance aware.
Cluster aware services:
Database Engine - This includes SQL Server, SQL Server Agent and SQL Server Full Text services
SQL Server Analysis Services
Services that are not cluster aware:
SQL Server Reporting Services - Reporting Services is made up of a few different components. It relies on IIS for hosting the web service and on the SQL Server Data Engine for hosting the metadata database. Both IIS and SQL Server Data Engine are cluster aware services and so in effect you achieve high availability by clustering those components.
A more important discussion when talking about SSRS is in it's ability to scale out. You can scale out a SSRS deployment by having multiple instances of SSRS pointing to the same SQL metadata database to provide load balancing. For more information refer to:
http://technet.microsoft.com/en-us/library/ms156453.aspx
A very common misconception is that windows clustering is thought of as a load balancing mechanism while it is not. Windows clustering and the clustering services that sit on top of it like SQL Server, SSAS etc. provide High Availability and not load balancing. To achieve load balancing, a service like WLBS (Windows Load Balancing Service) needs to used.
SQL Server Integration Services - This is not a cluster aware service. SSIS does not store any data internally as it is purely an ETL engine among other things. So, if you still want HA capabilities on the engine itself, then you can do so by creating a generic service wrapper around it. For more information, refer to:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Instance aware services:
Instance aware services are services that enables us to install multiple instances of the same service on the same machine.
Database Engine, SSAS and SSRS are instance aware services while SSIS is not an instance aware service.
One frequently asked question is if SQL Server is the only option to store content in a sharepoint environment. The confustion stems from the fact that SQL Server is a requirement for installing sharepoint.
The answer is, there is no necessity to store your content in SQL Server. The content can be stored external to the sharepoint environment. This is referred to as external content stores and then you can configure sharepoint to index this content so it can be searched against. Here is a link that explains this clearly:
http://www.microsoft.com/technet/windowsserver/sharepoint/V2/reskit/c2261881x.mspx
The reason SQL Server is a requirement for installing sharepoint even if you decide to store your content external to SQL Server is because SQL Server is used by Sharepoint to store metadata that it needs. While content can be stored in external content stores as documented above, it is a good idea to bring all your content into the sharepoint environment if possible. The following is an excellent article that talks about the migration process and the guidelines and tools required to do this:
http://www.microsoft.com/downloads/details.aspx?familyid=2004483C-E30F-4510-9B43-D4338E576891&displaylang=en
This is a great site to bookmark for SQL Server best practices. There are best practices in several areas of the product. For some reason, the search engines do not always bring this site as the top hits:
http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx
If you have used SQL 2005 encryption before, you might have hated the fact that you need to rewrite you application to access encrypted data. You had to use the encryptbykey and decryptbykey functions to write and read encrypted data. Well, the good news is in SQL 2008, we will do transparent data encryption which means once you encrypt your database, your application need not worry about using special functions to write and read encrypted data.
If you guys havent heard of msdewey, you should definitely check it out - www.msdewey.com
Search for the word " Lord of the rings" for example. Enjoy :)
An interesting topic/discussion i came across today is connecting to SQL Server 2005 64 bit server from 64 bit clients. What options does one have?
There are several good references out there. The best one i could find on this was the Data Access roadmap article here: http://msdn2.microsoft.com/en-us/library/ms810810.aspx
Essentially, there are 2 main options:
1) Connecting from 64 bit native applications:
There are two further choices here:
a) Use MDAC that ships with the OS: 64 bit drivers ships with the OS. This cannot take advantage of the new features in SQL 2005 like database mirroring for example but works well with existing applications that do not need that.
or
b) Use SQL Native Client that ship with SQL Server 2005: This will need to be used to take advantage of new SQL 2005 features. This combines ODBC and OLEDB into a single DLL.
2) Connecting from 64 bit Managed applications:
Use ADO.NET
The other frequently asked question is if you can access 64 bit SQL Server from a 32 bit clients without making any modifications to the app - The answer is YES!
I just created this blog and hope to post some useful tidbits. I am a database technology specialist and so work with the SQL Server product. I will try to post interesting information about SQL Server and the database world in general.
