Mirror, replication or copy a database on same server

Question

Mirror, replication or copy a database on same server

rslinct

Valued Member

Points: 58
More actions
June 8, 2009 at 9:28 am

#222250

I am trying to create a "mirrored" database on the same server to allow external access. I want to keep my primary database free of external access and I figured I could do this using methods like mirror, replication or copying.
It seems I can only mirror to a separate server and replication involves me installing more app from the CD. A manual copy operation sounds error prone and clumsy so I figured I would ask before I choose a path.
Any time any data changes in any table on database A, I want that to occur for database B. Sounds like a mirror to me but again it is all on the same server. Plus there were warnings of not using the mirror feature in production servers etc.
Replication sounded like it would do the trick but I am stopping short of installing this until I know it can do what I need it to do.
I can use triggers and manual commands but I really would rather have the "system" do the work for me.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

rslinct (6/8/2009)
I am trying to create a "mirrored" database on the same server to allow external access. I want to keep my primary database free of external access and I figured I could do this using methods like mirror, replication or copying.
It seems I can only mirror to a separate server and replication involves me installing more app from the CD. A manual copy operation sounds error prone and clumsy so I figured I would ask before I choose a path.
Any time any data changes in any table on database A, I want that to occur for database B. Sounds like a mirror to me but again it is all on the same server. Plus there were warnings of not using the mirror feature in production servers etc.
Replication sounded like it would do the trick but I am stopping short of installing this until I know it can do what I need it to do.
I can use triggers and manual commands but I really would rather have the "system" do the work for me.
Thanks

You can create a mirror database on the same server but on a different instance.

What is the latency that users can tolerate for data? if a longer latency time is acceptable, you can go for log shipping and keep the target DB in standby mode.

You can go for transaction replication but check out for issues that may arise because of identity columns in your tables...

Pradeep Singh

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

A mirror won't do this for you even if you did have a separate server to use, as you can't access the data on a mirror database directly.

Looks like replication or log shipping are the two choices you may have to consider.

OTF SSCertifiable Points: 6104 More actions · Answer 3

I think you may have left out some pertinent information which could help answer your question.

As you have rightly pointed out, there are any number of approaches to "mirroring" a database but I would say the approach you would take would depend largely on why you want the database "mirrored".

For instance, issues such as latency, performance, High Availability Best Practices as it may apply to your situation, the activity type on both databases (eg. are both databases writable? Is one simply redundant?) would all have to be taken into consideration in order to give an appropriate response.

If you do use a Mirrored Server I think you can access the data by creating a Snapshot over it.

http://technet.microsoft.com/en-us/library/ms175511.aspx

Chim Kalunta MCP, MCTS, MCITP

rslinct Valued Member Points: 58 More actions · Answer 4

I will have both a read only database and a read / write database. I have to provide access for various reasons, some just to generate reports from and others the receive info back from.

I am not concerned with linking the write info back into the original though, those I have to handle manually on an as required basis.

As for latency, as long as I can assume they are completely updated within 5 seconds, I am ok with it.

ps. One Orange Chip Points: 29422 More actions · Answer 5

rslinct (6/8/2009)
I will have both a read only database and a read / write database. I have to provide access for various reasons, some just to generate reports from and others the receive info back from.
I am not concerned with linking the write info back into the original though, those I have to handle manually on an as required basis.
As for latency, as long as I can assume they are completely updated within 5 seconds, I am ok with it.

I dont think i understood this correctly. If you want your target database to be used both for read/write, log shipping will not fit into your requirements as the target db will be in standby mode(readonly). If you need read/write activity on your target db and you donot want changes to propagate back to primary server, transactional replication will fit. If you want the changes to be propagated back to primary server, you can go for transactional replication with updatable subscribers OR peer to peer replication.

Pradeep Singh

rslinct Valued Member Points: 58 More actions · Answer 6

Primary system will have 5 databases. Of those 5, three will get "mirrored" to form a secondary level.

Of thos 3 in the secondary level, two will be read only and the third will be read /write.

The one that is read / write, I am going to deal with getting the write transactions manually. So if a third party writes data to it, I will manage this information manually. I do not want it updating my primary database automatically.

Thanks to everyone for their replies and ideas.

ps. One Orange Chip Points: 29422 More actions · Answer 7

rslinct (6/9/2009)
Primary system will have 5 databases. Of those 5, three will get "mirrored" to form a secondary level.
Of thos 3 in the secondary level, two will be read only and the third will be read /write.
The one that is read / write, I am going to deal with getting the write transactions manually. So if a third party writes data to it, I will manage this information manually. I do not want it updating my primary database automatically.
Thanks to everyone for their replies and ideas.

If you're going to MIRROR databases, secondary database wont be available for writes or even reads. They'll be in NORECOVERY mode. Read earlier posts for comparison.

Pradeep Singh

rslinct Valued Member Points: 58 More actions · Answer 8

I use the word "mirror" in quotes because it conveys the idea without using mirror, replication and copy.

I've read all the previous posts since this is a question I asked and one I am looking for advice and answers on. I realize the pitfalls of the mirror and will make a decision based on all the feedback I get here as well as reading and learning outside this post.

Thanks