Mirroring databases connected through a View

I have two databases db_A_primary and db_B_primary, both databases are on one Primary server.

db_B_primary has a View into db_A_primary.

Scenario: db_A_primary goes down and failsover to db_A_mirror on the Mirror server.

In this scenario when the View in db_B_primary is accessed will it automatically be redirected to look at the db_A_mirror database on the Mirror server?

Barry.

When the database fails over, it won't change the contents of the database. If you want the view to always point to a specific server.database.owner.table, you will want to specify it that way in the application. In other cases, you might want the view to point to a local table and wouldn't want to point to a remote server after failover. The bottom line is that it is up to the application to set the behavior desired.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||

Your mirrored database can not have a different name from the principal database. Perhaps you meant Server Name?

You can't do this with a view, but in stored procedures, you can use a try/catch to execute a dynamic query on the live server.

Begin Try

Exec sp_executesql N'Select * From db_A_primary.dbo.ATable'

End Try

Begin Catch

Exec sp_executesql N'Select * From MirrorServer.db_A_primary.dbo.ATable'

End Catch

Mirror a view to a table

I would like to replicate a single view to a table that is stored on another db server (connected as linked server object).
Is there a way to imitate the behavior of a trigger (insert, delete, update) for a view?
I could assign the triggers to the table that provides the primary key.
So I could handle insert, delete events.
But what about updates that affect row in other tables that are used in this view?

Code Snippet

CREATE TRIGGER mirror_tableA_insert
ON [TESTDB].[dbo].[tableA]
FOR INSERT
AS
BEGIN
set nocount on
SET XACT_ABORT ON
set REMOTE_PROC_TRANSACTIONS off
INSERT INTO OPENQUERY(TESTLINKED, 'SELECT * FROM tableA')

SELECT *
FROM [TESTDB].[dbo].[myView] orig
INNER JOIN inserted i
ON i.prim = orig.prim

END

Thanks in advance for any hints!

Marcus

Use replication service instead of using the Trigger.

|||

It is not totally clear what you are attempting to accomplish.

Why are you using OPENQUERY instead of a LinkedServer?

And you may be better served by exploring an 'INSTEAD OF' TRIGGER.

|||

Arnie Rowland wrote:

It is not totally clear what you are attempting to accomplish.
Why are you using OPENQUERY instead of a LinkedServer?
And you may be better served by exploring an 'INSTEAD OF' TRIGGER.

I would like to replicate this view to a MySQL database that is used for a website.

Basically it's data synchronization job. So all dml statements that affect this view should trigger a procedure that synchronizes the MySQL table with the view on the SQLServer.

Yes I managed to set up a linked server for the MySQL DB through the MyODBC 3.51 driver.
First I tried to copy all the rows in this view to a MySQL table using a INSERT INTO OPENQUERY statement.

But a trigger can't be added to a view.

Manivannan.D.Sekaran wrote:

Use replication service instead of using the Trigger.

Yes the replication service would be the way to go when the target server (subscriber) would be a DB2 or Oracle database. But my target datatbase is MySQL (using it for a website).
Maybe it's possible to define this OLE DB data source as subscriber?

|||

You can't do directly, but there are some thrid party tools available. C-JDBC: Clustered JDBC is one of the tool.(http://c-jdbc.objectweb.org/)

|||

Marc Cicero wrote:

But a trigger can't be added to a view.

As I wrote earlier, you may wish to explore an INSTEAD OF TRIGGER.

An INSTEAD OF TRIGGER can work on a VIEW.