June 22, 2007 at 4:23 pm
Hi all,
I'm starting in a new enviroment next week where a lot of cross-network stuff will be happening on 2K boxes.
I'm referring mainly to best practices on straight import/exports over the wire, but I'd also like some on migrations.
Preferably covering:
When to script or use EM;
Judging how much data you'd want to shove across what type of network (once tried backing up several gigs across the LAN which failed atrociously!!);
Migrations from one LAN to another (perhaps backup/attach on tape).
Appreciate that this is a broad spectrum but I hope this can be covered across several articles.
Cheers,
Jaybee.
June 23, 2007 at 11:38 am
"on 2K boxes" on SQL Server 2000 ?
"I'm referring mainly to best practices on straight import/exports over the wire, but I'd also like some on migrations."
For export/import (DTS ?), an approach I have used in the past is:
1. Export to a local file - this is fastest and holds read locks (intent share) for the shortest amount of time.
2 FTP across the LAN
3. Import from the local file - again faster and holds update locks for the shortest amount of time.
For the FTP:
Use FTP software that can restart.
Schedule the FTP to run during off-peak time.
Schedule the FTP as step in a SQL Server job and the next step is to run "sp_start_job" on the remote SQL Server.
Do not define any linked servers.
For large data volumes, get a USB drive.
Make sure you have USB 2. Just had a migration where they would not open the firewall to the old site and had to use USB. For a 6Gb backup file, USB 1 took 1.5 hours and USB 2 took about 5 minutes.
Re: the firewall - I did not blame them - viri, trojans and malware were rampant. All workstations were re-imaged and the users were only granted user rights where before they had administrator rights. A real fly-by-night department.
SQL = Scarcely Qualifies as a Language
June 23, 2007 at 1:16 pm
Carl, I'd like to thank you much for that!!
Just a couple of questions though,
What FTP softwares can restart?
Why not query linked servers?
How large would data have to be to warrant a USB drive? (I'm shocked at the difference between USB 1.0 and 2.0!!!!
Cheers,
Jaybee.
June 23, 2007 at 2:38 pm
"What FTP softwares can restart?
Most will restart, just check the manual before buying.
If using the internet or for sensitive information, try Sterling Commerce or Tumbleweed as they do encryption.
"Why not query linked servers?"
1 Poor performance as optimizing distributed SQL is extremely difficult and has not been solved. Dr Stonebraker tried to do this an gave up (search the internet for Stonebraker and federated databases)
2. As selects do locks and compared to local queries, distributed queries run for a longer time especially across a WAN, this often cause block and impacts the usability of the remote database.
Consider first a solution using log shipping to replicate a database across a WAN. This allows the replicated database to be read but not updated.
SQL = Scarcely Qualifies as a Language
June 23, 2007 at 2:47 pm
"When to script"
EM definitely for Jobs, DTS, log shipping and replication.
I have worked with Sybase or SQL Server since 1993 (inter-mixed with some Oracle), before there were any decent GUI tools and therefore have a lot of knowledge of the script command syntax, so I almost always script. Security can be either way but as I have tracing on to capture security events,the trace files provide the audit trail. Without the tracing, I would keep canned script and then just make some changes.
SQL Query Analyze Shift-F1 to get to help is your friend.
SQL = Scarcely Qualifies as a Language
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply