what to use for what DTS\Back up and Rec\replication 1

[seminewbee2005]

each of these tools can be used to store data objects in diefferent places but which to use for what. I mean a task can be scheduled to copy a table to another server in DTS, how is this different than replication. And you can also use dts and replication for data redundancy so why use back up and recovery. I know there is a very good reason why. I am hoping someone can explain exactly when to use what tool and for what!
thanks!!!

 

[JayKusch]

OK ... brief definitions of each ... (pieced togther from many sources)

DTS
Database administrators often import, export, and transform data in support of tasks such as data consolidation, archiving, and analysis; for application development purposes; and for database or server upgrades. Data Transformation Services (DTS) in SQL Server provides a set of graphical tools and programmable objects to help administrators and developers solve data movement problems, including the extraction, transformation, and consolidation of data from disparate sources to single or multiple destinations. Sets of tasks, workflow operations, and constraints can be collected as DTS packages that can be scheduled to run periodically or when certain events occur.

Key point here in being TRANSFORM! You can cleanse and conform data w/ the use of ActiveX scripts amd MUCH MUCH more.

Replication
SQL Server replication allows database administrators to distribute data to various servers throughout an organization. You may wish to implement replication in your organization for a number of reasons, such as:
Load balancing. Replication allows you to disseminate your data to a number of servers and then distribute the query load among those servers.

Offline processing. You may wish to manipulate data from your database on a machine that is not always connected to the network.

Redundancy. Replication allows you to build a fail-over database server that’s ready to pick up the processing load at a moment’s notice.

1. Snapshot. Copies an entire view of data to another computer. The destination database view is overwritten with the new version. Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values, low latency, is not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

2. Transactional. Transactions, INSERT, UPDATE, or DELETE statements, executed on one computer are replicated to another computer. With transactional replication, an initial snapshot of data is applied at subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers. Transactional replication is helpful when:

Incremental changes need to be propagated to subscribers as they occur.
Transactions need to adhere to the Atomic, Consistency, Isolation, and Durability (ACID) properties.
Subscribers are reliably and/or frequently connected to the Publisher.
3. Merge. Updates on any computer will be replicated to another computer at a later time. Merge replication is the process of distributing data from Publisher to Subscribers, allowing both the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when connected.

Merge replication allows various sites to work autonomously. At a later time, merge updates produce single, uniform result. The initial snapshot is applied to Subscribers, and then you have tracked changes to published data at the Publisher and at the Subscribers. The data is synchronized between servers continuously, at a scheduled time, or on demand. Because updates are made at more than one server, the same data may have been updated by the Publisher, or by more than one Subscriber. Thus, conflicts can occur when updates are merged.

Merge replication includes default and custom choices for conflict resolution that the user can define while configuring a merge publication. When a conflict occurs, a resolver is invoked by the Merge Agent and determines which data will be accepted and propagated to other sites. Merge replication is helpful when:

Multiple Subscribers need to update data at various times and propagate those changes to the Publisher and to other Subscribers.
Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
Not many conflicts are expected when data is updated at multiple sites. This is because the data is filtered into partitions and then published to different Subscribers, or due to the uses of the application. However, if conflicts do occur, violations of ACID properties are acceptable.

Backup and Recovery - Disaster Recovery

One of the most important functions that an SQL Server database administrator has is to design and implement a disaster recovery plan for the production system. This kind of planning involves planning the following aspects:

• Creating a backup plan for all databases.

• Setting up a warm backup server with the databases that are critical to an organization’s operations.

• Testing the backups at regular intervals.

• Circulating backups for off-site storage.

• Documenting the system and training co-workers in disaster recovery procedures.

• Simulating disaster recovery scenarios to test the timing and value of current procedures.

There are various techniques for designing an efficient disaster recovery scenario. Almost all of these techniques have as their first step:

• Understanding the requirements for disaster recovery for your organization.

• Familiarizing yourself with all the options available in SQL Server, so an efficient strategy may be designed.

Understanding the disaster recovery requirements is an extremely critical step as it lays down the foundation for a robust disaster recovery plan for any organization. The following questions are provided as a guide to the kind of information that should be understood by the person in charge of designing this strategy:

• How critical is the data stored on the database? If any data is lost, is it re-creatable?

• How much information can the organization afford to lose in case of a disaster? What is the maximum downtime that a company can take in case of disaster?

• How much data is modified in the database?

• Is this is a 24x7 (24 hours a day, 7 days a week) system or is there a maintenance/downtime window available on a regular basis?

• What resources are available to design a strategy? This would include resources like a redundant machine, off-site storage facility, etc.

There are several techniques that may be used to design a disaster recovery strategy. Some of these techniques are not discussed since they relate to the core operating system and are outside the scope of this paper. Depending on the kind of disaster a company might be dealing with, backup/restore may serve as the most robust disaster recovery mechanism available.

Since there are several kinds of backups to choose from, a combination usually leads to the quickest recovery path. See the case study in this paper.

Bottom line is that if wether you are using DTS, Replication, Backups (by leveraging Log Shipping, which I did not cover here but is more-or-less a poor man's replication) from one server to another ... If those servers are within the same local (ie Server Room, floor, building or a relatively close area ... you can loose ALL of your data and systems in the event of a catastrophy like a fire, flood or tornado.

SO ... that should give you some items to review.

Enjoy!

Thanks

J. Kusch

 

[seminewbee2005]

Very very helpful reply. thanks so much for your time and effort!!!