Database need to be replicated

Question

Database need to be replicated

river1

SSC-Insane

Points: 24309
More actions
December 20, 2013 at 4:28 pm

#287631

Hi,
I need to create a database that can be acceded by other people. This database needs to be up-to-date (with the source database) on a regular basis.
This database will be on a different server in relation to the source database.
What is the best thing to do?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

river1 SSC-Insane Points: 24309 More actions · Answer 1

As told, the two database will be on different servers.

Maybe the best solution is to create a linked server on the destination server. Then on the destination database create a view like servername.database.dbo.table.

This way users can query this view on the destination database without having permissions on the source database.

Question is:

Can I create an HTTP linked server?

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 2

If both servers share a SAN, some SANs have what I call a "SAN Snapshot" that will very nearly instantaneously create a copy of an existing database on the "other" box. We had a half TB database that we did that to in a previous company and the total time it took was just several seconds. The only warning that I have is that is IS a replacement and not an update Anything new you have in the "other" machine will be wiped out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

river1 SSC-Insane Points: 24309 More actions · Answer 3

I don't have SANs. The SQL Server Instances are on two different servers (each server is a standalone server)

Servers are on different physical places.

Can I create http linked server?

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 4

river1 (12/20/2013)
I don't have SANs. The SQL Server Instances are on two different servers (each server is a standalone server)
Servers are on different physical places.
Can I create http linked server?

At this point, I'd recommend "just" using Replication of some form. If it's critical that the "other" server be up to date as soon as possible, consider "Merge Replication". If a delay is tolerable, consider "Log Shipping".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)