Installing SQL Server 2008 R2 Sample databases and FILESTREAM

When installing the sample databases on a SQL Server 2008 R2 installation i ran into an issue with FILESTREAM settings. You can download the Sample databases at the codeplex site and many other great stuff. When installing, i saw that the AdventureWorks OLTP 2008R2 couldn't be installed.

At first the webpage told me that you have to configure the FILESTREAM. Okay i did:

EXEC sp_configure filestream_access_level, 1
RECONFIGURE
 
After that i tried it again. No go.

Than, somewhere on the page i saw a link to another page which linked to another page, etc which told me to set a check at the checkbox "enable FILESTREAM for Transact- SQL access" at servicewindow of the SQL Server service. You can reach this at the configuration manager:

You can do this with the following steps:
1.On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2.In the list of services, right-click SQL Server Services, and then click Open.
3.In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4.Right-click the instance, and then click Properties.
5.In the SQL Server Properties dialog box, click the FILESTREAM tab.
6.Select the Enable FILESTREAM for Transact-SQL access check box.

And the results are:

You have to install the analysis databases seperately in BIDS.

CU,
Hennie

Data aggregation implementation

This is the second article in the series about MDM based on the article “Master Data Management from a technical perspective” from Microsoft (april 2010). As I mentioned in the first post, i have noticed that there are 4 implementation possibilities of Master Data management:

• Master data registry implementation
• Data aggregation implementation.
• System-of-record-only implementation.
• Complete enterprise master data management implementation.

This blog is about the 'Data aggregation implementation’ also called ‘Watered down’ method. In the article a quote is mentioned : ‘Data aggregation implementations involve the creation of a new system that is neither the system of entry nor the system of record but a downstream system used to aggregate the attributes of multiple systems and pass them to lower level subscribing systems.’ This means that one system is taken as the most business critical and a leading system. This data is loaded in the MDM hub and other systems will take this as a base to compare their data against this.

At first, data aggregation seemed a bit strange to me. Aggregated data is data (measures) aggregated to a higher level (summarized, maximized or whatever). So what is meant by a data aggregation implementation? Data aggregation means from this viewpoint : a single view of the master data. This is in contrast with the multiview approach which is well suited for companies, achieving the 'right view' presented to the right people and processes, at the right time. In a data aggregation implementation there are multiple values unified to one view: the aggregated data.

Advantages:
• Quick wins for a organization.
• Risk to the mission-critical application can be mitigated.
• Less critical applications can begin to source their master data from the master data management application, solving any integration issues that arise without major ramification to the organization.
• Integration processes can be tested and modified in an iterative fashion.

Disadvantages
• Lack of control. It is very difficult for an initial master data management project to get all of the necessary stakeholders to relinquish control of their data to a new system immediately.

So this is it again....
 
Greetz,
Hennie

Master data registry implementation

Reading the article “Master Data Management from a technical perspective” from Microsoft (april 2010) i noticed that there are 4 implementation possibilities of Master Data management:

• Master data registry implementation
• Data aggregation implementation
• System-of-record-only implementation
• Complete enterprise master data management implementation.

I want to describe the first type of implementation in this blog: master data registry implementation. In a registry implementation, each system remains in control of its own data. All system data records are mapped in the master data registry. Data maps shows the relationships between the keys of two different keys. The microsoft article distinguishes two different ways :

• One to one: Every record in the main system will have only one corresponding record in the secondary system
• One to many: Every record in the main system will have one or more corresponding records in the secondary system.

This approach is the opposite of the repository approach in which the master data is stored in the MDM hub. The master data is maintained in the application databases and the MDM hub contains lists of keys that can be used to find all the related records for a particular master data item. For example, if there are records for a particular customer in the CRM, ERP and a other database, the MDM hub would contain a mapping of the keys for these three records to a common key.

Advantage :

• The changes to application code to implement this model are usually minimal.
• Current application users generally don’t need to be aware of the MDM system.

 
Disadvantage :

• Every query against MDM data is a distributed query across all the entries for the desired data in all the application databases. If the query is going against a particular customer this is probably not an unreasonable query but if you want a list of all customers who have ordered a particular product in the last six months you may need to do a distributed join across tables from five or even ten databases. Doing this kind of large, distributed query efficiently is pretty difficult.

Interesting approach! I assumed, before i read the article, there was only one implementation type possible: the repository approach. So there are multiple types of MDM implementations. I will blog about the other types laters...

Also interesting is the System Of Record (SOR) and the System Of Entry (SOE). SOR means that most, if not all systems, recieve their data from its source. When conflicts arise, this system is considered primary.

SOE means that this system is the primary point of data entry. This may be direct entry or through services that update the data in virtual real time. This is more interesting because when studying Datavault Linstedts states that the system of record moves from the source systems to the datawarehouse (because source systems alter). So how to put this in perspective?

Greetz,
Hennie

How to handle linked servers when migrating databases.

Hi Folks,

Times are busy for me and therefore i haven’t much time to BLOG about issues. But there will be time to catch up later. But found some time tto blog about an issue on the current job. As I already blogged about is that in my current project we have migrate a huge amount of DTS packages, databases, jobs, etc from a old environment to a new environment. Currently we are doing a POC and we are investigating a database. As we see there were a couple of linked servers on the machine (about 80!) and we try to figure out what we have do with these things. What has to happen to this linked server when we migrate a database from one machine to another?

Well, below you can see a example of couple databases (DB A, DB B, DB C, DB D, DB X and DB Y) and servers (A,B,C,D and Dnew) linked together with linked servers (LS1, LS2, LS2new, LS3 and LS1new). What will happen when we migrate database B to a new server (Server Bnew)?

We have to create two new linked servers in this case. One on Server C ( LS2new) and Server Bnew (LS1new). We can’t delete the linked servers yet because they could be used by other databases (like DB X and DB Y).

That's it again,

Hennie

How to migrate DTS packages (to DTS/SSIS packages)

Hi,

Currently we are investigating ways how to migrate DTS packages from one server to another. Because of old hardware we need to migrate the DTS packages from the old system to a new system as soon as possible. Thereore we decided to move the DTS packages with minimal adjustments to the new servers.We decided to use the legacy mode of SQL Server 2008.

As i previously blogged,  first we investigated the current situation with following methods:

• Gather information about the databases, DTS packages and jobs.
• Gather information about the content of a DTS packages with DTSDOC.
• Script the jobs of the servers.
• Gather information about the database objects (tables, views, stored procedures, etc) with DBDESC.
• We created a DTS/database matrix. This way we could investigate the impact of moving a database to a another server.
• We also created a Job/database matrix. A lot T-SQL code was embedded in jobs.

We will use this knowledge of the DTS packages and others when we will built the packages into SSIS packages later on in the project.

When analyzing how to movedatabases and DTS packages to another server we discovered that there were 4 ways of doing this:

You could replace a database with a text file or whatever. This is the same way of migration of a database. Also when a DTS package has a stored procedure: this would be the DTS migration. It will point to the old database. You could also use one of these scenarios when migrating to SSIS packages (as we will do later in the project).

If i figure out more examples of migrating DTS packages, i let you know.

Greetz,

Hennie