Following up on this, I was able to set up the mirroring on SQL 2005 between
2 servers using he High Availability mode with a 3rd witness server to
provide the automatic failover. The SQL side of things works great. If I
take the Primary DB server "DBServer1", offline (by stopping the SQL
Server), the "DBServer2" which was the Mirror becomes the primary. The code
below also works by automatically contacting the DBServer2 when the
DBServer1 can not be reached.
However it is a bit slow, because it tries to contact DBServer1 first and
then uses DBServer2 - but I think this is because the connection info is not
being cached because the connection is being opened and closed and that this
may not happen when it is set up as a regular datasource.
The only real problem is this; When the DB the was originally the Primary
(the one that went down) comes back up, it assumes the role of mirror, and
the failover server REMAINS the primary. This is apparently how the
mirroring works in SQL Server 2005. The problem with this is two fold. For
starters, it means you would need to have a fairly robust mirror, since it
may take over as the primary some day. And the bigger problem is that once
the first server server comes back on-line and starts actings as the Mirror
the code below returns an error because it can contact the first server, but
fails when opening the DB because the Mirror DB is inaccessible as it is in
RESTORE mode and can not be accessed.
It would be nice if once the Original Primary DB Server came back online
that it would resume Primary Duties and switch with the Mirror currently
acting as the Primary.
<cfobject type="JAVA" action="Create" name="Class" class="java.lang.Class">
<cfset Class.forName("macromedia.jdbc.MacromediaDriver")>
<cfobject type="JAVA" action="Create" name="DriverManager"
class="java.sql.DriverManager">
<cfset con =
DriverManager.getConnection("jdbc:macromedia:sqlserver://DBServer1:1433;data
baseName=testDB;SelectMethod=direct;sendStringParametersAsUnicode=false;MaxP
ooledStatements=1000;alternateServers=(DBServer2:1433)", "username",
"password")>
<cfset DatabaseMetaData = con.getMetaData()>
<cfoutput>
#con.getWarnings()#<BR><BR>
Driver Name: #DatabaseMetaData.getDriverName()#<BR>
Driver Version: #DatabaseMetaData.getDriverVersion()#<BR>
Database Name:#DatabaseMetaData.getDatabaseProductName()#<BR>
Database Version: #DatabaseMetaData.getDatabaseProductVersion()#<BR>
</cfoutput>
-----Original Message-----
From: blists [mailto:[EMAIL PROTECTED]
Sent: October 5, 2006 12:05 PM
To: CF-Talk
Subject: RE: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo
ver' to the mirror?
Hi Dave,
Thanks for the feedback, after some more searching last night, I found a
thread that showed how to add an alternateServers=(server2:1433) to the
connection string which would configure the datasource to automatically
contact the second / alternate server is the first was down.
http://forums.datadirect.com/ddforums/message.jspa?messageID=3685
Adobe also has an article about this and about how those settings when
inputed directly in the datasource config in cfadmin would be lost and need
to be manually added to the neo-query.xml file
http://www.macromedia.com/go/25b0de5e
I was able to add this parameter and have been testing it but am not quite
there yet. I don't actually even have two database servers set up yet..so..
I did manage to hack together this code to test the datasource configuration
and see if the failover works. This is basically the same code from the
article above combined with some insights gained from an adobe article:
ColdFusion MX: Creating a pure Java connection to a data source
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_19607
<cftry>
<!--- This cfobject creates a java object of type Class. Leave this
statement as it is. --->
<cfobject type="JAVA" action="Create" name="Class"
class="java.lang.Class">
<!--- The java class name for your driver is the argument for
Class.forName --->
<cfset Class.forName("macromedia.jdbc.MacromediaDriver")>
<!--- This cfobject creates a DriverManager java object which
provides your connection --->
<cfobject type="JAVA" action="Create" name="DriverManager"
class="java.sql.DriverManager">
<!--- The getConnection method takes the JDBC URL for your driver as
its arguement --->
<cfset con =
DriverManager.getConnection("jdbc:macromedia:sqlserver://myServerName:1433;d
atabaseName=myDB;SelectMethod=direct;sendStringParametersAsUnicode=false;Max
PooledStatements=1000;alternateServers=(failoverDBName:1433)", "username",
"password")>
<cfset DatabaseMetaData = con.getMetaData()>
<cfoutput>
#con.getWarnings()#<BR><BR>
Driver Name: #DatabaseMetaData.getDriverName()#<BR>
Driver Version: #DatabaseMetaData.getDriverVersion()#<BR>
Database Name:#DatabaseMetaData.getDatabaseProductName()#<BR>
Database Version: #DatabaseMetaData.getDatabaseProductVersion()#<BR>
</cfoutput>
<cfcatch>
<h2>Error - info below</h2>
<cfdump var="#cfcatch#"><cfabort>
</cfcatch>
</cftry>
<cftry>
<cfset con.close()>
<cfcatch>
<h2>Close Error - info below</h2>
<cfdump var="#cfcatch#"><cfabort>
</cfcatch>
</cftry>
I'm gonna keep trying..
Blists
-----Original Message-----
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: October 4, 2006 8:37 PM
To: CF-Talk
Subject: RE: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo
ver' to the mirror?
> I am just doing some research into this. With SQL Server 2005
> and mirroring, the mirror will automatically take over if the
> primary/principle server fails. But if you the datasource
> references the principle SQL Servers name, and that server
> was down, how would it failover?
>
> The only thing I can think of is that you may need to setup
> two datasources for the principle and mirror and set up a
> connection check within the CF application code that checks
> the server and changes the #datasource# var used in the app
> to the mirror if the principle is down.
>
> But this seems incorrect, because in which state would we
> consider the princible down? And would those states match the
> mirrored states? And what about client variable storage,
> would that need the same type of thing? Any have a mirrored
> SQL Server and coldfusion running together? How do you do it?
SQL Server 2005's mirroring is somewhat like log shipping in SQL Server
2000. With that, you'd have a primary and a standby server, and transaction
logs would be shipped periodically from the primary to the standby. If the
primary failed, you'd have to manually reconfigure everything to point to
the standby - you'd typically do this by changing the IP address of the
standby to that of the primary, and restart things as necessary. You would
lose whatever data had been added to the primary since the last log shipping
operation. SQL Server 2005's mirroring includes replication functionality as
well - in SQL Server 2000, replication and log shipping are completely
separate, although many DBAs would use them together. Mirroring is also
easier to implement than replication or log shipping in 2000, although I
don't know (yet) whether it's more or less reliable and/or fragile.
SQL Server 2005 has an additional feature which I've only read a bit about -
the ability to have a monitor server that's used to automatically flip
connections to the standby when the primary fails. I have no idea how that
works exactly, because I haven't used it.
Finally, SQL Server 2000 and 2005 both support clustering, which presents a
single virtual IP to your database clients. This requires some pretty
specific hardware support, and if that hardware fails, you get a big-time
failure - cluster members essentially lose all network connectivity. Ouch!
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255895
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
