Segregating OLTP and DSS Applications

Sometimes it makes sense to split up your OLTP and DSS applications. This can be an excellent strategy if your DSS applications don't need immediate access to information. This is a reason for the recent popularity of data warehousing, data marts, and other data management systems (although these concepts have been around for years ).

You can use a separate database (on the same server or on different servers) for DSS and OLTP, and the DSS database can be much more heavily indexed than the OLTP database. The DSS database will not have exclusive locks holding up queries, and the OLTP database's transactions will not get held up by the shared locks of the DSS. SQL Server's built-in replication capabilities make it relatively easy to publish data from the OLTP server and subscribe to it from your reporting server. SQL Server's replication capabilities can propagate data in near real time, with latency between the servers of just a few seconds. However, for maintaining a DSS server, it is best to propagate the changes during off-peak hours. Otherwise, the locks acquired during propagation at the subscribing site will affect the DSS users, just as any other update activity would. In addition, if for most of the day the DSS server is only executing SELECT queries, you can enable the database option read only. This means that SQL Server will not acquire or check locks for any queries because they will all be shared and therefore compatible. Completely bypassing lock maintenance can lead to noticeable performance improvements for the SELECT queries.

Inside Microsoft SQL Server 7.0
Inside Microsoft SQL Server 7.0 (Mps)
ISBN: 0735605173
EAN: 2147483647
Year: 1999
Pages: 144 © 2008-2017.
If you may any questions please contact us: