0

I am new to sql server .I have a sql server and I have 2nd sql server(backupserver).I want to copy data from sql server to 2nd one and all my transactions create update delete must be reflected in the 2nd server immediately in real time.I have very large data in my table assuming million of rows.How can I achieve this.I dont have previalge to use third party tools.

Improve this question
2
  • 2
    What version and edition of Sql Server are you using? 2005, 2008, 2012 / Express, Standard, Enterprise... Commented Oct 16, 2013 at 17:07
  • sql server 2008 enterprise Commented Oct 16, 2013 at 17:08

3 Answers 3

Reset to default
3

You specify that you need data to be reflected on the second server immediately, in real time.

This requirement will add latency to every transaction that occurs on your primary server, since every action will need to be committed on the secondary server before it can be committed on your primary server.

In addition to the latency, this requirement will also likely reduce your availability. If the secondary server is no longer able to successfully commit transactions from the primary, then the primary can no longer commit transactions either, and your system is down.

For more information about these constraints, refer to the extensive discussion around this topic(CAP theorem).

If you're OK with these restrictions, you might consider using Synchronous Database Mirroring (High-Safety Mode).

If you're not OK with these restrictions, please adjust / clarify your requirements.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

You may try to get some help from these two references:

On a side note an important thing you should need to plan before doing replication is the Replication model which you will use for your replication.

There is a list of Replication model which you can use:-

  • Peer-to-peer model
  • Central publisher model
  • Central publisher with remote distributor model
  • Central subscriber model
  • Publishing subscriber model

Each one of the above has its own advantages. Check them as per your need.

Also to add to it there are three types of Replication:-

  1. Transactional replication
  2. Merge replication
  3. Snapshot replication
Improve this answer

10 Comments

@constantlearner:- This question is too broad to get answers. The best would be to try and attempt any of the above links and tell us if you get any problem doing so! :)
Thanks for your help I will try with them.I am new to sql server
-1 Link only answer. I'd expect more from a 19k user.
@Mansfield:- As I already mentioned in my comments that this question is too broad. The OP is asking for the replication process of two SQL servers. We dont know what problem he faced. So I have provided the links for that. And thats sufficient as of now for him! Do you think thats not sufficient?
@RahulTripathi If links are all you're going to give him, they should be comments, not an answer. I agree that this question is unsuitable and difficult to answer properly.
|
0

Check out this tutorial on SQL Server 2008 replication:

Tutorial: Replicating Data Between Continuously Connected Servers

Since you said "... reflected in the 2nd server immediately in real time", that means you want to use Transactional Replication. You still may only choose to replicate certain tables.

Does the "millions of rows" represent some kind of history? If so, consider the risks that Michael mentioned... and whether you need all the history in the 2nd server, or just current / recent activity. If it's just current/recent, it may be safer and less of a system drain, to write something in T-SQL or SSIS, for a job to execute that loops, reads, and copies the data.

That could be done with linked servers and triggers... but the risks Michael mentions, about preventing the primary server from committing transactions, are as much or more a concern with triggers... that you can avoid with your own T-SQL/SSIS + job.

Hope that helps...

Improve this answer

5 Comments

-1 Link only answers are discouraged. You should add relevant information to your answer itself, although really this question is too broad and shouldn't remain open.
@Mansfield Ok, good to know... I will adapt accordingly in future. For the record, I thought it was appropriate for such a broad request for training, and more helpful than just telling him his question was too broad, as others had done. (At least I found a tutorial.) If I contribute more (happy to do), you reverse downvote?
I will, yes. Alternatively you could just post the link as a comment. However, the main problem is that this question is bad and difficult to answer properly (and will probably be closed).
@Mansfield done... thx for giving feedback constructively... have a good one.
@Mansfield P.S. next time, if it's just a link, I'll leave a comment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.