"Cross Mirroring" - any good?

Hi

I’m about to move all our databases from an old lousy server to a brand new HP setup.
I have 8 databases (total of approx 100Gb) and I get 2 BL25p servers and SAN access for them.

Now, I want high availability and I like the idea of mirroring the databases for achieving that. On the other hand I don’t like the idea of the mirror server doing almost nothing while the principal gets the entire load – seems like a waste of good resources.

But is this really the situation or mirror server also be loaded, just for being the mirror?

Now, if the main load is on the principal would I then benefit from distributing the databases on both servers, so both servers would act like principal and mirror?

What I mean is that server 1 could be principal for DB 1-4 while server 2 would mirror these DB’s and likewise serever 2 would be principal for DB 5-8 and server 1 would mirror them.

Does it make sense? Would I benefit anything from this?

Thx a lot
Nicolaj

In my experience i don't see any value to perform what you've referred, it is an opposite way of working in database mirroring. For the topic as mirror stays in loading state the application cannot be connected to perform the changes.

You might think the root of mirroring and clustering in this case, which I believe another complicated scenario to setup and manage, because you cannot guarantee that mirroring will fail over after a cluster failover. .

|||

Hi Satya

I migt not have explained my idea very well :-)

consider a setup with 2 databases. In order to split the load of these DB's they are installed on 2 different servers: DB1 on server1 and DB2 on server2.

Now, in order to get failover DB1 is mirrored to Server2 and DB2 is mirrored to Server1. This way the load is distributed on 2 servers and the failover is ensured through mirroring.... i guess... (of course both servers should be able to handle the load of all DB running on them in case of a breakdown)

But is this really the case, or will a server be too loaded just for beeing a mirror server?

/Nicolaj

|||Yes, you can do that to get such option but I haven't come across such setup.|||

Hi Nicolaj,

We are currently considering "cross mirroring" as you have described it - two database servers wit a total of 10-15 databases. I was curious if you have made it and do you have some positive exprerience? :-)

Thank you very much,

Regards,

Zdravko

|||

Hi Zdravko

We are still some weeks from the actual implementation , so I can't tell you about experiences yet. Sorry about that

/Nicolaj

|||See this kBA http://support.microsoft.com/kb/926150 for the subject of cross mirroring or transactions basis.

"Cross Mirroring" - any good?

Hi

I’m about to move all our databases from an old lousy server to a brand new HP setup.
I have 8 databases (total of approx 100Gb) and I get 2 BL25p servers and SAN access for them.

Now, I want high availability and I like the idea of mirroring the databases for achieving that. On the other hand I don’t like the idea of the mirror server doing almost nothing while the principal gets the entire load – seems like a waste of good resources.

But is this really the situation or mirror server also be loaded, just for being the mirror?

Now, if the main load is on the principal would I then benefit from distributing the databases on both servers, so both servers would act like principal and mirror?

What I mean is that server 1 could be principal for DB 1-4 while server 2 would mirror these DB’s and likewise serever 2 would be principal for DB 5-8 and server 1 would mirror them.

Does it make sense? Would I benefit anything from this?

Thx a lot
Nicolaj

In my experience i don't see any value to perform what you've referred, it is an opposite way of working in database mirroring. For the topic as mirror stays in loading state the application cannot be connected to perform the changes.

You might think the root of mirroring and clustering in this case, which I believe another complicated scenario to setup and manage, because you cannot guarantee that mirroring will fail over after a cluster failover. .

|||

Hi Satya

I migt not have explained my idea very well :-)

consider a setup with 2 databases. In order to split the load of these DB's they are installed on 2 different servers: DB1 on server1 and DB2 on server2.

Now, in order to get failover DB1 is mirrored to Server2 and DB2 is mirrored to Server1. This way the load is distributed on 2 servers and the failover is ensured through mirroring.... i guess... (of course both servers should be able to handle the load of all DB running on them in case of a breakdown)

But is this really the case, or will a server be too loaded just for beeing a mirror server?

/Nicolaj

|||Yes, you can do that to get such option but I haven't come across such setup.|||

Hi Nicolaj,

We are currently considering "cross mirroring" as you have described it - two database servers wit a total of 10-15 databases. I was curious if you have made it and do you have some positive exprerience? :-)

Thank you very much,

Regards,

Zdravko

|||

Hi Zdravko

We are still some weeks from the actual implementation , so I can't tell you about experiences yet. Sorry about that

/Nicolaj

|||See this kBA http://support.microsoft.com/kb/926150 for the subject of cross mirroring or transactions basis.

"Cross database ownership chaining"

I saw this option in SSMS under "Servers, Property, Security tab". I looked at BOL but I still don't understand what it is or what it is used. Any help appreciated.

TIA,

Barkingdog

I think that this is detailed described in the BOL. To summarize that shortly. If one user creates a view or a procedure and grant appropiate permissions for THAT object to some user for Select / Execute and the object accesses objects in other databases which are also owned by the creator of the View/Procedure then with Cross database ownership chain SQL Server assumes that the permissions don′t have to be checked for the accessing user of the view/procedure. if the ownership chain is broken, or the function is disabled permissions are check also for the access to the second database for the accessing user. For this case you would have to grant the user permissions in the second database whereas you would not need to in the first case.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Ownership Chaining

By default, all database objects have owners. When an object such as a view, a stored procedure, or a user-defined function references another object, an ownership chain is established. For example, a table that is owned by the same user. When the same user owns the source object, the view, stored procedure, or user-defined function, and all target objects (underlying tables, views, or other objects), the ownership chain is said to be unbroken. When the ownership chain is unbroken, SQL Server checks permissions on the source object but not on the target objects.

Cross-Database Ownership Chaining

Cross-database ownership chaining occurs when the source object depends on objects in another database. A cross-database ownership chain works in the same way as ownership chaining in a database, except that an unbroken ownership chain is based on all the object owners being mapped to the same login account. Therefore, in a cross-database ownership chain, if the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.
If you have more than one database used by an application, and that application calls stored procedures or views in a database that is based on objects in another database, then cross-database ownership chaining is used. Applications that rely on cross-database ownership chaining may generate permission denied errors if cross-database ownership chaining option is turned off.

Risks Associated with Cross-Database Ownership Chaining

Microsoft recommends that you disable the cross-database ownership chaining option because of the actions that highly-privileged users can perform:

Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases. To identify the members of the db_ddladmin and the db_owners roles in the current database, execute the following Transact-SQL commands: EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE Configure cross-database ownership chaining at the database level with the new db chaining option for sp_dboption. When this option is set to false, the database cannot participate in cross-database ownership chaining as either the source or the target database. When this option is set to true, the database can participate in a cross-database ownership chain. By default, this option is false for all user databases after you apply SQL Server 2000 SP3. The following command enables cross-database ownership chaining for the Northwind database: EXEC sp_dboption 'Northwind', 'db chaining', 'true' The effects of sp_dboption are manifested only when the sp_configure Cross DB Ownership Chaining option is set to 0. Also, to enable cross-database ownership chaining at the database level, you must enable this option on both the source and the target database. Configuring cross-database ownership chaining by using SQL Enterprise Manager: ? To set this option for all databases, follow these steps: 1. Right-click <server>. 2. Click to select Properties. 3. Click Security. 4. Click to select Allow cross-database ownership chaining in the Ownership chaining section. 5. Click OK. You are prompted to stop and restart the SQL Server services. 6. Click OK . ? To enable this option at the database level, follow these steps: 1. Right-click the <database>. 2. Click to select Properties. 3. Click Options. 4. Click to select Allow Cross Database Ownership Chaining in the Settings section