We currently have access to another department's SQL server. However they are taking that server offline within a few months, and we will need to migrate our data and DTS packages to a new solution before then. We have about 30 databases and 20 somewhat simple DTS packages, but only a few users need access. Funding is very tight.
SQL 2005's support of DTS is somewhat of a mystery to me. Docs state with 2000 Enterprise client tools installed + Express edition, that I can run DTS packages on Express edition. But the docs do not state the procedure for doing so, and I cannot register the Express server in the Enterprise manager(states must be using 2005 version of Enterprise manager). It seems I can Design a DTS package that resides on the 2000 server and specify a connection to the Express server, but the DTS package would not run, stating always that the table was in use, and I haven't explored it any further. May have just been a quirk.
I'm trying to conquer this in two pieces:
Cheapest solution for graphical design of DTS packages, or some techniology similar(I think I understand that this is called SSIS in 2005?). This would be needed by 1-4 users.
SQL 2005 Developer Edition for each user, only about $50 per user.
MSDE for each user, but can this house DTS packages and be connected to by enterprise manager?
Cheapest solution for data access and ability to run DTS packages by 2-10 users.
If SQL Express Edition is used to house data, can the DTS packages be distributed in some way that allows the users to run them? I.E. exported as VB code or some sort of file that can be loaded by an Access 2002 or VB.NET frontend.
Can all users have Developer Edition or Desktop edition and run the DTS package from it, but have the DTS package connect to the Express Edition server where the data is housed? Or would this be a violation of what the Developer Edition is intended to be used for?
Any other suggested scenarios are welcome.
Are we allowed to continue using the 2000 Enterprise manager after we no longer have access to a licensed SQL 2000 Enterprise server?
The feature comparison for the different 2005 editions doesn't mention DTS, but I know it works for some editions if the add on components are installed. I'm just not sure which versions support this.
1. For development, SQL 2005 dev edition includes sql server 2005 database engine. No msde is required. The server will persist DTS 2000 packages. You cannot connect via 2000 tools, but you can via included 2005 tools. For editing DTS 2000 packages, you need the DTS Designer download, located on msdn as part of the sql 2005 feature pack.
2. The DTS 2000 object model is free. It can be redistributed from the 2000 install disk, or is also available as part of the backward compatibility redist in the feature pack. Once installed on a machine, DTS packages may be run from the command line or from the agent, loading packages resident on 2000 or 2005 servers, or on the file system.
You don't have a need for 2000 enterprise manager here, as your 2005 dev edition will suffice for development work. Howvever, if you want to save your production packages on sql server you will need to attend to licensing for that server. I think msde or express will work, but am not sure. As an alternative, you can save your packages on the file system. There they can be edited by 2000 or 2005 tools.
No comments:
Post a Comment