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

DTS migration project.

In my current project we have to migrate a couple of hundreds DTS packages, jobs and other stuff from a SQL server 2000 Cluster to SQL Server 2008 cluster. For certain reasons we decided to take two steps:

• Phase 1 : migrate DTS packages to a legacy server (SQL Server 2008 with DTS backwards compatibility components)
• Phase 2 : migrate DTS packages to a new environment with SQL Server 2008 SSIS. A complete rebuild to Integration services.

So we divided the project in the following steps:
- Global analysis current situation.
- Global analysis desired situation Phase 1
- A Proof of concept for learning.
- Per system part

• Plan
• Migrate
• Validate

- Global analysis desired situation Phase 2
- A Proof of concept for learning.
- Per system part

• Plan
• Migrate
• Validate

The system part are parts of processes that have a strong cohesion with the other packages in the systempart and loosely coupled with DTS packages in other system parts. Per system part we created three main steps:

- Plan : In these steps we have to gather information about DTS packages, applications, jobs, linked server, access applications, system owner, etc etc. This information is processed in a migration plan. A DTS/databases and job/database matrix will be used for impact analysis
- Migrate. In this step the migration takes place. The system owner is notified that the software is migrated and the software is copied to a development environment, changed and deployed in the production environment, the system owner accepts the software and the last step will be evaluation of the best practices
- Validate. In this step the production is checked and validated by the system owner, The projectmanager is notified and lessons learned evalution is done.

We hope to start soon with a POC for a lessons learned. I'll blog about this laterss..

Greetz,
Hennie