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.

Mining structure has to contain at least 2 mining models to see their accuracy charts?

Hi ,all here,

Thank you very much for your kind attention.

I just found that I am not able to view the accuracy chart for my mining model. The error message is: no mining models are selected for comparision. Which is quite strange.

Any guidance? thank you very much.

With best regards,

Yours sincerely,

Can you provide more details? The only scenario I can think of where this would happen is where the first model was a clustering model that had no predictable attributes.|||

Hi, Jamie,

Yes, my mining model is clustering model. So as you mentioned, seeing the accuracy chart of clustering model is restricted in there? If so, what's the resolution? Thank you very much for your further guidance.

With best regards,

Yours sincerely,

|||

The accuracy chart is only useful if your model has a predictable attribute (column), and the accuracy of the prediction can be evaluated against some test data. If your clustering model does not have a predictable attribute, the accuracy chart cannot be used.

|||

Hi, Bogdan, thank you very much for your guidance.

So in what way can we verify if the clustering model is good enough or not?

With best regards,

Yours sincerely,

Migration to MySQL?

From a "price point of view" migrating from the actual platform (2 node
cluster Windows server 2003 with SQL Server 2000 Enterprise Edition)to MySQL
can be very attractive.
In fact our management starts to think that way...
My question is related to the faisability of this operation in a critical
production cluster server.
Is it recommanded?
Pros and Cons?
Why yes and why no.
Please advise.
Franco
It is difficult to answer this type of questions rigorously in a newsgroup
forum. I'd suggest that you invole all the stakeholders and conduct a
comparative risk analysis on the migration. The compartive risk analysis
should cover the key risk factors that are important to your organizaton.
Note that cost factors are but one category among many others.
Some of risk factors to consider include reliability and resilience (e.g.
high availability, stability of the DBMS, proven platform, etc), scalability
and performance (e.g. scalability in terms of database size, # of processors,
memory size, partitioning, index maintenance, etc), manageability (e.g. ease
of backup/restore), operations issus (e.g. need for consistency checks,
availability of expetise, monitoring tools, etc), portfolio impact (e.g. what
is the mix of your installed DBMS and apps using various DBMS, dependencies
among the databases, etc), and supportability (e.g. vendor support, user
community, etc). Obviously, you would have to rate these and other factors
per their relative importance to your apps and organization.
Linchi
"Franco" wrote:

> From a "price point of view" migrating from the actual platform (2 node
> cluster Windows server 2003 with SQL Server 2000 Enterprise Edition)to MySQL
> can be very attractive.
> In fact our management starts to think that way...
> My question is related to the faisability of this operation in a critical
> production cluster server.
> Is it recommanded?
> Pros and Cons?
> Why yes and why no.
> Please advise.
> --
> Franco