Hi,
We want to simulate a distributed database environment by creating one or more mirror of our database to share the load through SQL transactional replication or other approach. We are not sure whether this is the right way to achieve this or what is the best approach. Is there any implication of performance or stability suffering? to what extent? Most of our database accesses are a mixture of read and write access and our database schema is relatively complex.
Existing Database Schema layout:
1) 300 tables
2) 200 views
3) 20 indexed views
4) 400 stored procedures / functions
5) 1 Full Text Index
6) 250 foreign keys (enforce relationship for replication, cascade update/delete)
7) 70 triggers
8) 5 GB db size, estimated additional 5GB by year end