Dedicated DTS Server - Opinions?
-
Hi All -
My shop currently runs SQL Server 2K and 6.5 and several servers (most servers are in the same physical location). Most of these servers are "small" and "old" (a sad indicator of the state of our economy). Anyhow - in the past it has been the standard to run / schedule all DTS jobs on two dedicated servers. These servers don't have any "real" databases themselves - their sole purpose is to run jobs.
Now we embarking on a project to consolidate SQL Servers (buying newer and slightly larger hardware). I'm asking for the opinion of my colleagues about the wisdom of dedicating a server "just for running DTS" - versus running jobs on SQL Servers that host the real databases.
I'm reserving my opinion until I hear yours. Thanks folks.
-Mike
Mike at the Mill -
We run DTS packages on either the source or destination server (usually the destination) to reduce network load. If you run the package from a third server, the data will go from the source server to the "job" server, then to the destination server.
Greg
Greg
-
It all depends on at what time of the day your DTS scripts are scheduled to run. If there is no traffic on server when you run your DTS then probably you don't need a separate server to run your DTS. It is very difficult to answer this question correctly till we have more information like how many transactions and other processes hit the SQL server when you run your DTS package. How much space do you have on server were your SQL server is installed, How many DTS pacakges do you run at the same time?
- Kamlesh Agnihotri
-
Thanks for the feedback so far. Kamlesh, without "writing a book" about our environment, let me say the following:
The two servers dedicated to DTS jobs have about 25-30 jobs each. These run at all times throughout the day. We are a 24X7 manufacturing environment. Most of these packages are involved in moving transactions from one server or platform to another. We have jobs that run as infrequently as once or twice a week. But we have many more jobs that run, for example, every 15-20 minutes throughout the day.
In terms of size, as I mentioned, we are definitely small potatoes. Our largest database is only 7800M. Most of our other databases are under 500M. The physical server resources are also quite small. We really do not use (i.e., take advantage of) RAID. All storage is direct attached. And did I mention OLD?? Does that give you an idea of our environment, Kamlesh?
I'm interested in hearing your thoughts.
-Mike
Mike at the Mill -
Mike,
Are you interested in upgrading your environment or you want the answer for the current environment? Even in current environment you run many of the DTS's every 15-20 minutes. I don't know what kind of business you are in, so I am guessing that in 15-20 minutes, you will not be having huge number of transactions(may be less than 1000 rows for each DTS). If my assumption about number of transactions is correct then you don't need separate servers for running your DTS's. In my opinion you should use RAID and upgrade physical resources of your servers. Let me know if it makes sense.
Thanks,
Kamlesh Agnihotri
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply