It is very rare that one expects to develop a large client-server database from scratch. In most cases, the business enterprise has been saving data for some time, and the historical data from the business exists in a different format. A key component of developing a successful business system is the inclusion and melding of that pre-existing data into a system.
In other cases, data on one server needs to be transferred, migrated, shared, downloaded, or replicated on another server. There may be SQL Server installations of the same of different vintages, or, commonly, different database management systems, such as Oracle, Sybase, or db2. Some times these requirements are one-time events, but it is common to have a business requirement that data is moved, partitioned, or shared frequently, or even continuously.
Microsoft SQL Server 2000 has three tools that are shipped with the product and can manage any or all of the requirements suggested above. There is some overlapping, however each tool has different strengths and characteristics. The trick is selecting the proper tool for the job, and configuring the system properly to achieve your objectives.
The first class of tool is the Bulk Copy Program (BCP), along with its associated routines, BULK INSERT, and the bulk API library applications. This tool is used primarily for grunt-type ad hoc large volume copying operations particularly to and from flat files. The second tool is the Data Transformation Services (DTS), which is particularly suitable for moving data on an on-going basis between various Microsoft programs (and some other brand-name database managers for which drivers exist). The last technique is a set of tools for replication of database between SQL Servers (and some other brand-name client server database managers) on a continuous basis, and is used to keep multiple data sources synchronized with each other.
It’s convenient yet dangerous to make an automotive analogy of these tools. Dangerous because the analogy itself reveals my own preferences, and risks becoming out-of-date as marketing nuances of those manufacturers change. It’s convenient because analogies are the easiest way to describe the tools in a compact way. The BCP tools are like a Ford F-350 heavy-duty pickup – diesel powered, lots of room, available with dual sets of rear wheels- a real powerful workhorse. The DTS tools are less flexible and powerful, but are very useful for moving data on programmed basis to and from predictable locations. I think of DTS as a well-equipped SUV – with all the comforts to make the whole family happy. The replication tools are like a Jaguar – a precision but finicky machine. Once you get it tuned, you can trust it to handle well on the open road, buMay 23, 2006 set up programs and interfaces to handle just about any business condition that requires distributed database processing and synchronization of multiple data sources.
ResourcesThis resource discusses how to use SQL server to write ASP code.
Metro NY / NJ SQL Server Consultants
We specialize is custom database software. Call us for a free consultation (973) 635 0080
Computer Consultants: Database Development