Maintaining 2 indentical SQL Server Databases

Posted on 2011-04-19
Last Modified: 2012-08-14
My team has 2 web projects taking data from the same SQL server 2000 database.  One project can have read and write right and data is updated from this project while the other just needs the read right from this database.  When many users query data at the same time, the server will choke and times out on certain queries.  The fastest solution probably is to have 2 copy of the same database running at the same time and each project gets data from its own database.  We can accept the fact that the read only project may not have data updated in real time because data is updated from the project that can write data into the database but we would like to keep data to be in sync in real time as close as possible, what may be the suitable solution for this?

Thank you.
Question by:lenguyen0366
    LVL 16

    Assisted Solution

    by:Imran Javed Zia
    There are many ways to address these issues:
    Use Data Replication Services
    Use Mirror Server
    Database Snapshot

    I recommend you to use later version of SQL Server may be 2005 or 2008. hope it will address lot of performance issues

    LVL 21

    Assisted Solution

    by:Alpesh Patel
    Please use mirroring or Log shipping is the best way
    LVL 8

    Assisted Solution

    I suppose one of the requirements is that the databases need to be up & running 100% of the time?
    This rules out quite a lot scenario's.

    However I would not go this wway, and improve the application/queries/indexes to prevent long locks from accuring.

    Also as suggested upgrading to the latest en greatest SQL version will help you (much smarter locking)
    If you hppen to behet to SQL2005/8, please try the 'snapshot isolation level' for the read-only app, it will solve all your problems ;-)

    LVL 28

    Accepted Solution

    You don't mention what indexing and performance tuning you've done on the existing database - instead of adding a second copy of the data, the "timeout and choke" problems can possibly be solved by seeing which queries are responsible and adding some indexes to support them. If the indexes aren't present to do their job, those queries can be using table scans or other operations that are very expensive, and the right indexes can make those queries return almost instantly.

    On a basic level, you can use the SQLServer/SSMS canned reports to see which queries are expensive, as far as disk access goes. In SSMS, connect to your server, and then right-click on the server and select "Reports" -> "Standard Reports" -> "Performance - Top Queries By Total IO". This report will quickly show you the top 15 queries run on your server (since it was last restarted) that resulted in the highest volume of data read from disk (across all executions - if a query is lightweight, but was run a million times, it could show up above a heavy query that's run once/day, for example).

    Once you have the expensive queries in hand, you can either talk to your DBA about creating some indexes to service them, or if you don't have the DBA (or are the de-facto DBA), can you cheat a bit by placing the query in an SSMS window, and from the Query menu, selecting "Analyze query in the database engine tuning advisor". This is a tool that comes with SQL Server (when you install the "Management Tools - Complete" option in the installer) that will look at your query, compare it to your database, see how it would best be pulled, and then recommend indexes and statistics that would improve performance for that query. Keep in mind that indexes, in exchange for speeding lookups, can have a slight negative impact on updates/deletes - it's slight, but over-indexing your data can outweigh the benefits in some cases. Once you have some recommendations from the DETA, you can apply them (preferably first to a test copy, if you have it) and see how much they improve performance.

    While you could do a second copy of the database, you'd have to manage it to make sure they're in sync all the time, and that can get complicated. If you can avoid a second copy, and continue to allow both processes to use the single copy you already have, that's the ideal solution. You may even be able to add some indexes to improve the performance of both applications, not just the slow one.

    Good luck!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now