Below is a graphic of our sever and database structure. All databases are sql and running mostly 1 way replications defined by the arrows. This has been a working progress for about 2 years. The systems has been running for about 2 years in this configuration and it had been nice, but there is one problem. Schema changes are a nightmare to get from point A to point B. If they are made at the top I could possible have to compare schema and import schema changes 7 to 8 times. All the boxes represent off site servers that I replicate to, they are all on the same network but doing these changes over the network takes hours per task. We are in the process of getting fiber connected to all facilities but that is going to be a few years down the road. Other than networking, are there any other options to redesign this structure to improve the system and make it more versatile to schema changes.
The E911, Eagle, and CED are all offsite facilities. Tobler is my server with 6 databases. I pull in data from E911, Eagle, and CED through 1 way replications. Except for a 2 way replication from Tobler - Local Government Model, to E911 - E911 Local Government Model. Once I get all the data into my server "Tobler", producing an off site backup for all facilities. I replicate data out to our publication database and too our replication database. The publication database feeds all map services and viewer programs for all staff. The replication database I push replications back out to the off site facilities so they receive most current data possible for all organization data.