none
How to use read/write spliting in Ms sql server

    Question

  • Hi experts,

    I have a question about how to do "R/W spliting" in Ms SQL Server. Is there anyone who can give me some information? Thanks for your help.

    Have a nice day.



    • Edited by Alan_chen Thursday, June 07, 2012 8:27 AM
    Thursday, June 07, 2012 8:25 AM

Answers

  • Hi Alan,

    The way I'd tackle that is to use a linked server query.  I'm currently working on a project where I'm doing exactly this.  My approach is:

    1. Create a linked server reference to the "read" server (let's call it [ReadServer] for the sake of illustration) on the server where you're writing data (let's call it [WriteServer], and the DB that you're writing to, [WriteDB])
    2. Create a view encapsulating your query on the "read" server in database [ReadDB] - this allows you to optimise the query with indexes/stats pages, etc... so the only overhead on the wire is the data transfer
    3. Create a passthrough view [ReadViewLocal] on the write server that basically runs a SELECT * FROM [ReadServer].[ReadDB].[ReadViewSchema].[ReadView]
    4. Then join your Update, Insert and Delete statements against the new local view [WriteDB].[ReadViewLocalSchema].[ReadViewLocal]

    This approach will give you pretty consistent results when you have this kind of requirement.  Alternatively, you could do the data marshalling client-side or via a web service that pulls in data from the read source and writes it to the write destination.

    HTH


    Jeremy Huppatz
    Managing Consultant
    Solitaire Systems

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    • Marked as answer by Alan_chen Monday, June 11, 2012 2:49 AM
    Friday, June 08, 2012 2:15 AM
  • Alan

    I think that Jeremy answered your question entirely . Snapshot copy is more for my opinion of SAN tools that allows you to copy\replicate the data... 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Alan_chen Monday, June 11, 2012 2:49 AM
    Sunday, June 10, 2012 8:20 AM
    Moderator

All replies

  • What does it mean read/write splitting, give an example?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, June 07, 2012 8:34 AM
    Moderator
  • Hi Uri,

    Thanks.

    My understanding of "R/W Splitting" is querying from one server but inserting and updating from another server. My question is how to transfer the W-server data to R-server?

    Am I making it clearer?

    Have a nice day.


    Alan Chen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 08, 2012 1:21 AM
  • Hi Alan,

    The way I'd tackle that is to use a linked server query.  I'm currently working on a project where I'm doing exactly this.  My approach is:

    1. Create a linked server reference to the "read" server (let's call it [ReadServer] for the sake of illustration) on the server where you're writing data (let's call it [WriteServer], and the DB that you're writing to, [WriteDB])
    2. Create a view encapsulating your query on the "read" server in database [ReadDB] - this allows you to optimise the query with indexes/stats pages, etc... so the only overhead on the wire is the data transfer
    3. Create a passthrough view [ReadViewLocal] on the write server that basically runs a SELECT * FROM [ReadServer].[ReadDB].[ReadViewSchema].[ReadView]
    4. Then join your Update, Insert and Delete statements against the new local view [WriteDB].[ReadViewLocalSchema].[ReadViewLocal]

    This approach will give you pretty consistent results when you have this kind of requirement.  Alternatively, you could do the data marshalling client-side or via a web service that pulls in data from the read source and writes it to the write destination.

    HTH


    Jeremy Huppatz
    Managing Consultant
    Solitaire Systems

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    • Marked as answer by Alan_chen Monday, June 11, 2012 2:49 AM
    Friday, June 08, 2012 2:15 AM
  • Hi Jeremy,

    Thanks for your help.

    I got to know there is a way that is called "snapshot copy". I'm not very sure about the way, would you please show your mind with me?

    Have a nice day.


    Alan Chen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 08, 2012 7:09 AM
  • Alan

    I think that Jeremy answered your question entirely . Snapshot copy is more for my opinion of SAN tools that allows you to copy\replicate the data... 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Alan_chen Monday, June 11, 2012 2:49 AM
    Sunday, June 10, 2012 8:20 AM
    Moderator
  • Thanks @Jeremy and @Dimant!

    Have a nice day.


    Alan Chen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 11, 2012 2:50 AM