Keeping Multiple Database Applications in Sync with Replication

Posted by arlene

Replication is a powerful feature of SQL Server 2000 that allows you to keep more than one database (in fact a whole enterprise of databases) in sync. This means that half of your users can be updating a database in Siberia, and the other half in Dunedin.

The changes that users make can be replicated across to the other database, allowing the users in Siberia to see the changes that the users in Dunedin make, and vice versa! This type of replication is known as two-way replication. The data is pushed from one server to another and in both directions.

One-way replication is where local databases send their data through to a central database. The central database receives the data but does not push any information back.

Since the introduction of SQL Server 2000, we can now do some really cool things when it comes to replication.

SQL Server 2000 is available in a CE version for Windows CE-compatible handhelds. This allows staff out in the field to enter real-time information into their Windows CE devices, save the data, and when they get back to the office, have it replicate to a central server. Talk about cool!

But with the new .Net platform initiative that Microsoft is driving, we have even greater flexibility than this. We can actually build our own interfaces for Windows CE using VB, C#, C++, and so on. So a custom interface that the users are familiar with can be put in the palm of their hands. And with new products like the Compaq iPAQ we can even have nice colored screens. The next wave of computing is here!

Leveraging Enhancements to Improve Development

SQL Server 2000 offers several ways to streamline development and improve data integrity. This section shows you some cool things you can do with Query Analyzer and other development tricks you can use.

Using Cascading Declarative Referential Integrity (Cascading DRI)

When we first created our database we used the database-diagramming tool to create some of the relationships between our tables. When we created the relationships, we had the option to implement Cascading DRI. Although we neglected to take it up, it is perhaps one new feature of SQL Server 2000 that will excite those who have had to write complex triggers in the past to perform this functionality.

DRI are the foreign key constraints that we implement between tables to ensure that relationships enforce the integrity of our data. DRI helps make sure that when an UPDATE is performed, existing data is not violated by the data change. DRI can be supplemented by defining triggers on tables, custom defaults, and so on.

Cascading DRI allows us to delete child rows out of a table when the parent row is deleted. In relational databases it is not possible to delete the parent row before the child row. In the past we had to write complex triggers and stored procedures to perform this functionality. Now SQL Server 2000 can do this!

This doesn’t just apply to deletes, but also to updates. If the primary key of a column changes (and it shouldn’t, it really shouldn’t), we can replicate this through the database. The change will propagate through the database making the change for us.

Possibly related posts: (automatically generated)
Keeping Multiple Database Applications in Sync with Replication

• Network and Servers Technical Compare continue...
• Website Hosting Sever, some Pitfalls you need to avoid part 1
• Using the New Wizards in SQL Server 2000
• Altering the Size of the Transaction Log Files
• Client/Server must Know
• Running Multiple Instances on One Machine
• Future Programming, the Next Development Platform part 1
• Installing and Configuring SQL Server 2000
• Primary, Secondary, and Caching-Only Name Servers
• Massed head with web hosting?