February 6, 2009 at 2:23 am
Hi
I wonder if someone can help. This is all regarding SQL 2005.
I have a central server (say at head office) and a number (currently 100) of remote servers around the country. I need to bring selected data from the remote ones to the central one and push any updates on the central server to the remote ones (as simply put as I can) . Not all the data needs to go to the remote servers and some needs to be pushed out to more than one remote server. The number of remote servers will increase over the next year or 2 to about 200 and I need to make it very easy to add new remote servers to this solution as the end users aren't SQL experts.
I haven't used SSIS for this partly as I don't know it well enough (I've only done simple imports and exports). I have a central table that stores details of the remote servers and I loop through the info in this table in a cursor then call a number of stored procedures passing the remote server in as a param. I then dynamically build some SQL, sometimes joining a table from the central server to a table on the remote server and then execute the SQL using an exec() command. Each stored proc can have several commands issued. I am using the 4 part server.db.owner.table syntax when referencing the remote tables.
When I test it within the same network between 2 servers the performance is acceptable. When I connect to a remote server over the internet/along a VPN the performance is 100 times slower.
Does anyone know if this is normal, if there is anything that I can do to improve the speed or if upgrading to 2008 will help me?
Many thanks......A
February 6, 2009 at 11:11 am
What I suggest involved several processes but it is more effecient way to do it.
Why dont you create txt/csv files and put it in FTP folders (main server remote server) then import/export the files in SQL in each remote server. try to avoid accessing server remotely, it will make your processes slow.
You can also check the pipe line going through the main server and remote server.
You can check your T-SQL/SP processes by using execution plan. here you can see which SQL statement is taking too long.
You should also maintain or defrag your indexes.
I hope this help 😀
February 6, 2009 at 12:28 pm
Why dont you create txt/csv files and put it in FTP folders (main server remote server) then import/export the files in SQL in each remote server. try to avoid accessing server remotely, it will make your processes slow.
I like this idea. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply