Can I use the Failover Partner in my SQL join string without mirroring?

Question

I admit that I am a little cheap, but I do not want spring for the three servers needed to automatically switch to a backup resource for SQL Server through Mirroring, especially since one of the three will do nothing but be a witness server.

If I set up normal SQL replication (a snapshot, say) from the primary server to the backup server, can I just set the connection string in my ASP.NET application to enable "Failover Partner = backup"? It seems that ADO.NET on the client should just try the primary and then restore the backup if the primary file is not available.

Or does it only work with mirroring?

(I like the idea of ​​mirroring, but if you don't want to pay for the witness server, you need to manually switch the backup server to the primary one.)

Thanks!

Answer 1

I also looked for the answer to the original question of whether FailoverPartner is supported in non-mirrored environments. Our environment has a secondary database that is synchronized through replication.

To check, I install the following connection string in the OLEDB SSIS source:

Data Source=PRIMARY;FailoverPartner=SECONDARY;Provider=SQLNCLI10.1; Network=dbmssocn;Database=myDB;User ID=user;Password=password; 

When I started, I see the following error

The connection attempted to crash in a database that is not configured to mirror the database.

Thus, this seems impossible if the secondary server is actually in a mirrored configuration.

Answer 2

Under Microsoft licensing terms for SQL Server 2005/2008, you only need one license (per processor) for the primary SQL server.

You do not need to buy an additional SQL server license for the mirror, because the mirror server only communicates with the main one.

If you switch to another resource, you have 30 days to return to the first one, otherwise you will need to purchase additional licenses.

Here is a link to licensing information, see Passive Server / Failover Support on page 2.

Microsoft Licensing Overview ...

Answer 3

Gregor, if I remember correctly, your witness server may be SQL Express running on very cheap hardware (desktop PCs, even if you need to). As far as I know, there is no way to automatically switch to another resource without using clustering or mirroring with a witness.

See http://msdn.microsoft.com/en-us/library/ms189590.aspx and http://msdn.microsoft.com/en-us/library/ms175191.aspx for more information.

A snapshot taken from the second URL: “We strongly recommend that the witness be on a separate computer from partners. Database mirroring partners are supported only with standard and later versions of SQL Server 2005 and SQL Server 2005 Enterprise Edition and later versions of Witnesses SQL Server 2005 Workgroup and later, and SQL Server 2005 Express Edition and later are also supported, and Witness can run on any reliable computer system that supports any of these SQL Server editions. Smiling to each instance of the server, which was used as a witness, meet the minimum configuration that is required for the version of SQL Server Standard, you are using. "

Answer 4

I was looking for something like this; Is it possible to specify a transition partner in one and only one connection string; - apparently, as long as there is a partner for fault tolerance; - db engine is smart enough to balance in case of failure on the host (fortunately);