Several Stored Procedures Running At The Same Time

[HLEE1167]

We are a very intensive Access production shop. We are going to develop and implement a SQL Server solution. Basically what we do is that we have hundreeds of Access databases in hundreeds of machines and then we have people who run queries, programs and reports on those databases. The SQL server solution is going to copy data from all those databases (different projects, clients) and then some procedures may be run at the server itself. How bad is to run several stored procedures on a single server at the same time (in terms of eficiency)? I read that Microsoft is going to allow to program using VB.NET, Do that mean that DTS is not going to be necessary to populate the Server tables?

Thank you

 

[Catadmin]

DTS has been completely re-written as SSIS (SQL Server Integration Services). You can still use the same methodology to import and export data from other dbs, but you don't need it if you're going from Access to SQL because you can just use a File ODBC and link the Access tables directly to the SQL Server.

As far as the Proc performance goes, that depends on how you write the Procs and how good your SQL Server hardware is. Outstanding hardware can still have problems if the procs in question are so badly written they kill the server every time they execute and vice versa. You'll want get an Execution Plan on your Procs and make them as efficient as possible. Test and test and test when you write them. Then test again. Once you've gotten the procs optimized, you shouldn't have a whole lot of problems if your hardware and network connections are good. We have lots of Access front-ends leading to one SQL box and most of our issues are Hardware/Network related or Badly-Written Query related more than they are the # of actual connections.

Does that help you out?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"