Sqljdbc DriverManager.getConnection Hangs After Mirror Failover.
Aug 24, 2006
When performing a failover the DriverManager.getConnection method never returns from it's call. I used a modified version of the test program included in the sqljdbc help file (below). If the principle server is server2 and a failover is performed, the program works just fine. If the priciple server is server1 and a failover is performed, the hang occurs. The sql servers are not on a domain, are in the same workgroup, have certificates setup between them. Sqljdbc version used is 1.1.1501.101. I rolled back to version 1.1.1320.0 and the DriverManager.getConnection exceptioned. Not sure if this is a setup issue or a common problem. Anyone have any suggestions or seeing similar issues?
Thanks
- tc
Version 1.1.1501.101 - System hangs on DriverManager.getConnection method.
Version 1.1.1320.0 - Exception:
java.nio.channels.UnresolvedAddressException
at sun.nio.ch.Net.checkAddress(Net.java:30)
at sun.nio.ch.SocketChannelImpl.connect(SocketChannelImpl.java:451)
at java.nio.channels.SocketChannel.open(SocketChannel.java:146)
at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithFailover(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:193)
at testconnection.Main.Execute(Main.java:115)
at testconnection.Main.main(Main.java:35)
/////////////////////////////////////////////////////////////////////////////
package testconnection;
import java.sql.*;
public class Main
{
/** Creates a new instance of Main */
public Main()
{
}
/**
* @param args the command line arguments
*/
public static void main(String[] args)
{
int i = 0;
while(true)
{
Execute(++i);
try
{
Thread.sleep(5000);
}
catch(Exception e)
{}
}
}
private static void Execute(int iteration)
{
// Create a variable for the connection string.
String connectionUrl = "jdbc:sqlserver://server1;" +
"databaseName=TestDb;integratedSecurity=true;" +
"failoverPartner=server2";
// Declare the JDBC objects.
Connection con = null;
Statement stmt = null;
try
{
System.out.println("(" + iteration + ") Connection Test");
// Establish the connection to the principal server.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
System.out.println(" Get Connection.");
con = DriverManager.getConnection(connectionUrl);
System.out.println(" Connected to the principal server.");
// Create and execute an SQL statement that inserts some data.
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM TestTable");
System.out.println(" SUCCESS");
while(rs.next())
{
System.out.println(" Record: " + rs.getString("Field1"));
}
}
// Handle any errors that may have occurred.
catch (SQLException se)
{
try
{
// The connection to the principal server failed,
// try the mirror server which may now be the new
// principal server.
System.out.println(" Connection to principal server failed, " +
"trying the mirror server.");
con = DriverManager.getConnection(connectionUrl);
System.out.println(" Connected to the new principal server.");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM TestTable");
System.out.println(" SUCCESS");
while(rs.next())
{
System.out.println(" Record: " + rs.getString("Field1"));
}
}
catch (Exception e)
{
System.out.println(" FAILED");
e.printStackTrace();
}
}
catch (Exception e)
{
e.printStackTrace();
}
// Close the JDBC objects.
finally
{
if (stmt != null) try { stmt.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}
View 15 Replies
ADVERTISEMENT
Apr 11, 2007
i download JDBC (JDBC service Pack 1) on Solaris, to connect to Microsoft sql 2000 (Developer Edition, Service Pack 2).
i used the code like that:
Connect=DSN=jdbc:microsoft : sqlserver://x.x.x.x:1433;UID=sa;PWD=*******
but the program show this error:
java.sql.DriverManager.getConnection threw an exception
PLZ can any one help me.
View 3 Replies
View Related
Aug 3, 2007
I'm trying setup a proof of concept for a client but I€™m having issues with mirroring and replication.
With the help of http://technet.microsoft.com/en-us/library/ms151799.aspx I€™ve done the following..
The setup is as follows, I have several instances of SQL Server
A+B are mirrored with each other, with A being the principle and B the mirror
C is the distributor
E is subscribes to A, with parameters for PublicationFailoverPartner set to B
The mirroring between A+B works fine, I can manually fail each and the databases keep in step.
Without testing the mirroring failover, the replication between A+C+E also works.
However when I test the mirror to failover between A+B no transactions are replicated though to E. The publication moves from A to B in the Server Management Studio.
The logs state that the log reader agent can not gain access to A, however nothing is recorded regarding any attempts to contact B. In the replication monitor the error message
"The concurrent snapshot for publication ABCXYZ is not available because it has not been fully generated of the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Spapshot Agent for the publica..." (cut off).
"the process could not execute 'sp_replcmds' on 'A'
I have checked that the SQL Server Agents are running on all agents,
I have checked that that server B is set at the PublisherFailoverPartner on server C agent configuration; I'm now at a lost as to what is wrong,
Any help or suggestions are greatly appreciated.
View 14 Replies
View Related
Jun 28, 2006
Hello All,
I've tried to replicated exactly what it says in the online documentation. At the end of the post are the SQL statements issued.
1. Enable encrypted outbound connections on the primary server
2. Enable encrypted outbound connections on the mirror server
3. Enable encrypted outbound connections on the witness server
4. Enable encrypted inbound connections on the primary server
5. Enable encrypted inbound connections on the mirror server
6. Enable encrypted inbound connections on the witness server
7. Set mirror''s partner to the primary
8. Set primary''s partner the mirror
9. Set principal€™s witness.
Every step run''s normally,but why can''t auto failover.
I used the following Script:
/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */
use master;
go
DROP USER PrincipalLogin1
DROP LOGIN PrincipalLogin1
DROP USER PrincipalLogin2
DROP LOGIN PrincipalLogin2
GO
Create login PrincipalLogin1
with password='PrincipalLogin'
go
Create user PrincipalLogin1
from login PrincipalLogin1
go
Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1
go
---stop
create certificate MirrorCertForPartnerPub
authorization PrincipalLogin1
from file = 'd:BackupCertMirrorCertForPartner.cer';
go
Create login PrincipalLogin2
with password='PrincipalLogin'
go
Create user PrincipalLogin2
from login PrincipalLogin2
go
Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2
go
create certificate WintnessCertForPartnerPub
authorization PrincipalLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO
ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.1.40.158:5022'
GO
ALTER DATABASE Mirrortest
SET witness = 'TCP://10.10.11.30:7000'
GO
select * from sys.certificates
/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */
--On Mirror
--Remove the database
--Connect to either partner.
--Issue the following Transact-SQL statement:
ALTER DATABASE Mirrortest SET PARTNER OFF
--Optionally, you can recover the former mirror database. On the server instance that was the mirror server, enter:
--RESTORE DATABASE Mirrortest WITH RECOVERY;
RESTORE DATABASE [Mirrortest] FROM
DISK = N'D:BackupCertMirrortest.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
select * from sys.endpoints
drop endpoint Mirror_Endpoint_Mirroring
select * from sys.certificates
Create certificate MirrorCertForPartner
with subject ='this is the certificate for mirror',
start_date='06/25/2006'
backup certificate MirrorCertForPartner
to file='d:ackupcertMirrorCertForPartner.cer'
CREATE ENDPOINT Mirror_Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (
Authentication=Certificate MirrorCertForPartner
,ROLE=ALL)
GO
/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */
--On Witness
USE [master]
GO
select * from sys.certificates
drop certificate CertForDBRole
GO
create master key
encryption by password='asdf'
Create certificate CertForDBRole
with subject='this is a certificate for pricipal role',
start_date='06/25/2006'
backup certificate CertForDBrole
to file='c:CertForDBRole.cer'
select * from sys.endpoints
drop endpoint Witness_Endpoint_Mirroring
CREATE ENDPOINT Witness_Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7000)
FOR DATABASE_MIRRORING (
Authentication=Certificate CertForDBRole
,ROLE=ALL)
GO
/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */
use master;
go
DROP USER PrincipalLogin1
DROP LOGIN PrincipalLogin1
DROP USER PrincipalLogin2
DROP LOGIN PrincipalLogin2
GO
Create login PrincipalLogin1
with password='PrincipalLogin'
go
Create user PrincipalLogin1
from login PrincipalLogin1
go
Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1
go
---stop
create certificate MirrorCertForPartnerPub
authorization PrincipalLogin1
from file = 'd:BackupCertMirrorCertForPartner.cer';
go
Create login PrincipalLogin2
with password='PrincipalLogin'
go
Create user PrincipalLogin2
from login PrincipalLogin2
go
Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2
go
create certificate WintnessCertForPartnerPub
authorization PrincipalLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO
ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.1.40.158:5022'
GO
ALTER DATABASE Mirrortest
SET witness = 'TCP://10.10.11.30:7000'
GO
select * from sys.certificates
/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */
use master;
go
DROP USER MirrorLogin1
DROP LOGIN MirrorLogin1
DROP USER MirrorLogin2
DROP LOGIN MirrorLogin2
GO
Create login MirrorLogin1
with password='MirrorLogin'
go
Create user MirrorLogin1
from login MirrorLogin1
go
Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin1
go
--stop
create certificate PrincipalCertForPartnerPub
authorization MirrorLogin1
from file = 'd:BackupCertPrincipalCertForPartner.cer';
go
Create login MirrorLogin2
with password='MirrorLogin'
go
Create user MirrorLogin2
from login MirrorLogin2
go
create certificate WitnessCertForPartnerPub
authorization MirrorLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO
Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin2
go
ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.10.116.42:5022'
GO
ALTER DATABASE Mirrortest
SET WITNESS = 'TCP://w7mis02:5024'
GO
/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */
use master;
go
DROP USER WitnessLogin1
drop login WitnessLogin1
DROP USER WitnessLogin2
drop login WitnessLogin2
Create login WitnessLogin1
with password='WitnessLogi*n@'
go
create user WitnessLogin1
from login WitnessLogin1
go
create certificate PrincipalCertForPartnerPub
authorization WitnessLogin1
from file = 'c:ackupcertPrincipalCertForPartner.cer';
go
--***************************************
Create login WitnessLogin2
with password='WitnessLogi*n@'
go
create user WitnessLogin2
from login WitnessLogin2
go
create certificate MirrorCertForPartnerPub
authorization WitnessLogin2
from file = 'c:ackupcertMirrorCertForPartner.cer';
go
Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin1
go
Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin2
go
All runs normally,But how can't be auto failover?
View 5 Replies
View Related
Oct 16, 2007
Hello everybody,
I'am currently testing a MSSQL 2005 mirroring implementation with automatically failover.
1 principal server ====> Server A
1 mirror server ====> Server B
1 witness server ====> Server C
When server A fails Server B becomes Principal. ( which is the general idea ) This is working perfectly.
The problem is when i try to connect to the mirror Server B ( actually the new principal )
I use the .NET connection string with the failover parameter ( even without the parameter ) the connection fails.
( the general error : unable to connect, or connection property has not been initialized )
Data Source=ServerA; Failover Partner=ServerB;Initial Catalog=DBMTest;uid=dbuser;pwd=dbpassword;
This connection property works fine when server A (original principal) is online.
When i run the SQL profiler with the mirror db as profile, i dont see the traffic.
So you think the mirror server has a network problem or was not connfigured for remote access.
i thought that also, but when i create a 'stand alone - non mirrored' database the same application with the same connetion string ( well.....different db) works fine.
The problem must be in the whole mirroring implementation....... ( my idea )
Does anyone had the same problem, or better, doed anyone know how to solve it?
Hope to hear from you soon!
Thank you in advance!
Regards,
W.schmitt
The netherlands
View 6 Replies
View Related
Feb 2, 2008
Hello,
I wanted to instal sql server 2005 on our sql 2000 failover cluster.
The first thing i did was deinstal sql 2000.
The installation hangs on the dialog screen configuring components and does not present een error.
If i install the cluster with one node there's no problem.
But if i want to add the second node the installion hangs.
When i go to the second node and stop the scheduled task the installations stops.
Do you have any suggestion
View 1 Replies
View Related
Feb 18, 2015
I am trying to install SQL Server 2012 onto an already configured and validated windows failover cluster (server 2012) but the process is hanging after installing the setup files.
The last entry in the log is:
running discovery on remote machine
and I've left it hanging like this for 4 hours and nothing happens.
View 2 Replies
View Related
Sep 26, 2007
I have a Microsoft Cluster running on Server 2003 Entrprise. SQL 2000 8.00.2039 (SP4). 5gb physical memory installed.
With the databases online we run a test failover from the Cluster administrator. It takes about 30-40 seconds and completes without generating any Server Event log errors nor SQL log errors. Everything looks good from an administrative stand point.
However, when we test with running a series of queries to the databases, then failover, we notice that it can take up to 3 or 4 minutes before some of the databases will respond. Connections are not refused, they just sit there.
How can we troubleshoot this or does anyone have a similiar experience with this scenario?
View 5 Replies
View Related
Jun 14, 2015
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
View 4 Replies
View Related
Nov 26, 2007
Look into sqljdbc.jar: it contains inside a zero-length file named sqljdbc.jar, and the file named sqljdbc.jar.old, with a size of about 280kb. This actually doubles the size of the jar.
Was it done on purpose (e.g., old jar is called in some way), or just a mistake of packaging?
View 8 Replies
View Related
Sep 18, 2007
Are there any compatibiltiy isses with these two products?
TIA,
Barkingdog
View 3 Replies
View Related
Sep 14, 2007
Hello, i used sqljdbc (driver jdbc microsoft 1.1)
Erreur code sql server 3971
Connction status 0001
com.microsoft.sqlserver.jdbc.SQLServerException: Le serveur n'a pas pu reprendre la transaction, desc. : 9f00000040.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doCloseServerCursor(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$CloseServerCursorRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.closeServerCursor(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.close(Unknown Source)
An idea?
View 1 Replies
View Related
Feb 2, 2007
Hello;
I failed to connect with my database, it give me the massage as following;
Cannot load the SQL driver. Include it in your classpath
Could anyone tell me why? Thanks
And i am using the Microsoft SQL server 2000, Java 2 runtime enviroment and Microsoft SQL Server 2005 JDBC Driver now.
In the user varible, i set a path for the java run time as "c:Program FilesJavaj2re1.4.2javaws"
and CLASSPATH =.;C:Program FilesMicrosoft SQL Server 2005 JDBC Driversqljdbc_1.1enusqljdbc.jar for the JDBC Driver.
and i also tried
1)
-classpath =.;"C:Program FilesMicrosoft SQL Server 2005 JDBC Driversqljdbc_1.1enusqljdbc.jar"
2)i moved the file of the sqljdbc.jar to a path as c: emp
then i try it again as following;
varible name: -classpath
value: ",;C empsqljdbc.jar"
They are still not work
but it does not work
and my code as following;
public MyTest()
{
//information of our connection
//the url of the database: protocol:subprotocol:subname:computer_name:port:database_name
String strUrl = "jdbc:sqlserver://localhost;user=xx;password=xxxx";
//user name and password
String strUser = "xx";
String strPass = "xxx";
//try to load the driver
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}
catch (ClassNotFoundException e) {
System.out.println( "Cannot load the SQL driver. Include it in your classpath.");
System.exit( -1);
}
//a null reference to a Connection object
c = null;
try {
//open a connection to the database
c = DriverManager.getConnection( strUrl);
}
catch (SQLException e) {
System.out.println("Cannot connect to the database. Here is the error:");
e.printStackTrace();
System.exit( -1);
}
//create a statement object to execute sql statements
displayInformation();
}
By the way, how i can delete a system variable?
i set the classpath for the sqljdbc in system variable section, but i want to delete it, but when i click the delete button, it does not has reaction. Do you known i i can solve this problem? Thanks
View 1 Replies
View Related
Oct 29, 2015
1. Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. after fail over done to 2nd secondary node what mode in production(readonly or read write).
4. how to rollback to production primary ,will change data in secondary will get updated in primary.
View 5 Replies
View Related
Mar 3, 2007
We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.
However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.
The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.
Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.
The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.
Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.
Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.
We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?
Are we missing a service pack on the client?
Thanks,
Kimball
View 1 Replies
View Related
Oct 29, 2015
1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. After fail over done to 2nd secondary node what mode in production(readonly or read write).
4. How to rollback to production primary ,will change data in secondary will get updated in primary.
View 3 Replies
View Related
Jan 31, 2008
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
Thanks,
View 9 Replies
View Related
Dec 27, 2006
I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.
View 1 Replies
View Related
Dec 4, 2007
Hi,
I am doing some research on sqljdbc 1.2. I have run into a few problems with the jdbc driver. I think it would be so much easier just to ask the expert here.
1. when to use CallableStatment, PreparedStatement, Statement for stored proc and why?
http://msdn2.microsoft.com/en-us/library/ms378046.aspx
talks about using Statements with Stored Procedures.
It says for stored Proc with no param, use Statement; for stored proc with only input param, use PreparedStatement; and for stored proc with output param, return status or update count, use CallableStatement.
Is there any special considerations to select which one to use, is there any performance differences? I assume we can always use CallableStatement for stored proc.
2. mutliple statements on one Connection
The JDBC spec allows multiple Statement on one Connection with no need to close the previous Statment. When Connection.close() is called, all the Statements and ResultSets will be closed too.
I tried with regular stored proc, it is fine of have a CallableStatement, then a Statement, without close the CallableStatment, all the Statements are executed with no error.
However, when I change the stored proc, using explicit transaction, i.e BEGIN TRANSACTION, and COMMIT TRANSACTION, the Statement failed and throw an error:
com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3500000001.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
When I add a CallableStatement.close() before the create the second Statment, then it worked. Is this a bug or something special needs to be set for stored proc with Transaction?
3. Statement.getGeneratedKeys
Under the hood, is it making another round trip to get the generatedkeys, or when the Statement created with Statement.RETURN_GENERATED_KEYS is excuted, the identity field is already retured with the Statement?
This method call works with regular insert Statement, but does not work for PreparedStatement calling a stored proc. Is this a bug?
Thanks in advance,
jessica
View 1 Replies
View Related
May 16, 2007
Hi!
We are porting a database from DB2 to SQL-Server 2005. We use WAS 5.1. We have problems with insert staments followed by an SELECT @@IDENTITY. The sql error is that no result set is returned. The syntax is "<insert statment>;SELECT @@IDENTITY.
We have no problems with this in the 1.1 version (there we have the known "read date" error).
Joachim
View 9 Replies
View Related
Mar 17, 2008
The following exception is thrown with sqljdbc.jar (not with jtds0.9.jar)
com.microsoft.sqlserver.jdbc.SQLServerException: New request is not allowed to start because it should come with valid transaction descriptor.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source)
at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:265)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:288)
While using the query :
if not exists ( select 1 from sysindexes where id = object_id('aaa') and name = 'aaa_pk')create unique nonclustered index aaa_pkon aaa(id)using Statement.executeBatch()at conection.commit()
Sample Code
conn = getConnection();
// create the statement and execute the query
Statement stmt = null;
try
{
conn.setAutoCommit(false);
stmt = conn.createStatement();
for ( String sql : sqlList )
{
stmt.addBatch( sql );
}
results = stmt.executeBatch();
stmt.clearBatch();
conn.commit(); //throws the exception
}
catch ( SQLException e )
{
try
{
conn.rollback();
}
catch ( SQLException e1 )
{
throw new DataSourceException( e1 );
}
throw new DataSourceException( "Error executing sql: %1", e, sqlList.toString() );
}
View 1 Replies
View Related
Mar 15, 2006
We have a new server that is 64-bit and I have been approached about using the old 32-bit SQL Server (after a complete format and reinstall) for a mirrored server.
Either that, or buy a new Server with less power that would be used in times of need.
View 4 Replies
View Related
Dec 7, 2007
I have two sql 2005 servers. I want to do mirroring. My question is does mirroring supports store procedures, ssis packages, user log in ids also? I know mirroring supports database level. In this case, mirroring should supports sp and indexes also. Am I right?
View 3 Replies
View Related
Jun 21, 2007
My client wants to provide a redundant server at a remote location. They want to have db access in a worst case "building burns down" scenario. Can I use mirroring to a remote location? Do I want to use mirroring to a remote location? What happens to my mirror set when some knucklehead with a backhoe digs up my high speed cable?
Am I better off, in this case, sticking with log shipping and manually bringing up the stand by server?
View 1 Replies
View Related
Dec 19, 2006
Hi all,
I feel happy to post my first thread at dbforums.com, and I think it's the community of DB professionals.
my question is:
I have two servers in my organization, one of them is running an SQL server database. Is there any way to make the second server a mirror of the first one and to make it runs automatically as the main server when the first server is down?
Thanks for replying soon.
Haytham
View 4 Replies
View Related
Aug 6, 2007
Hi folks,
I am creating an external database for a legacy application and one of the requirements is that every table has to be declared twice in the database:
TABLE_READ and TABLE_WRITE.
Now the idea is that as soon as a change is made to TABLE_WRITE, these
changes should be reflected in TABLE_READ.
Now there are dozens of these tables, and I dont want to have to go
through and write AFTER UPDATE/INSTERT/DELETE triggers for all of
them. Is there an easier way?
- can you use replication within the same database instance (ie, replicating tables instead of instances)
- some kind of table mirroring within a single database?
- is there a good solution using tsql/stored procedures?
Thanks in advance,
Alex
View 2 Replies
View Related
Feb 20, 2007
Hello All!
I have a very simple question.
Im studying for 40-431 and just finished reading about Log Shipping.
It says in the book that when using Log Shipping you cannot run any other log backups because that would affect the LSNs. Can one do log backups with mirroring - principle and mirror?
Because mirroring uses log to keep the mirror up-to-date and synchs on LSN.
Thank you
View 1 Replies
View Related
Jun 21, 2007
Hi,
I've got a problem with the DB mirroring in SQL 2005.
Configuration:
Server 1 Name: ABC001 NIC#1: IP 10.1.1.1 no DNS Server, no Gateway NIC#2: IP 192.1.1.11
Server 2 Name: ABC002 NIC#1: IP 10.1.1.2 no DNS Server, no Gateway NIC#2: IP 192.1.1.12
NIC#1 is an cross-over between the 2 server, NIC#2 is related to the local network.
I cannot setup the mirror with the built-in tool nor by hand.
With the tool I receive the message:
"
One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroring again.
The syntax for a fully-qualified TCP address is:
TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>
"
By hand with (Endpoints are ready, added by the tool):
-- Specify the partner from the principal server
ALTER DATABASE [MyDB] SET PARTNER =
N'TCP://ABC002:5022';
also
ALTER DATABASE [MyDB] SET PARTNER =
N'TCP://10.1.1.1:5022';
makes a problem like "other server cannot be reached"
I can ping the other server and use windows sharing. I have create an host entry for ABC002 to use the NIC#1 alltime.
Has anyone an idea how to resolve this?
Thanks!
View 1 Replies
View Related
Feb 21, 2007
Hi,
Trying to build an replication and subscription beteween db in SQL 2000. The db holds our business system (Dynamics NAV, earlier Navision) and several of the tables include $, spaces and even :
We can take some of this away when creating a new db or make scripts for some actions but I wonder if the mirroring in 2005 could handle this?
Thanks,
Mikael
View 3 Replies
View Related
Jan 26, 2007
Im pretty new to DBA world
We have a SQL2005 Standard setup with mirror and witness
I create a Database in the Principle, create a SQLLogon account and give it permission to the database. All works.
I then fail the databse over to SQL2 and the database is there, it has the SQLAccount I create at the database level, but a logon does not work. I notice there is not login account at the database level and If I attempt to create one, I am told there is one already. I try to assign permission to that account for the database and it again replys that there is already on.
Is this refered to as an orphaned logon?
I was a post on Moving logins from on server to another, is that what I must do?
THank you
View 7 Replies
View Related
May 30, 2007
I have a question and excuse me if I look dumb ... but I can't MIRROR MASTER or other dBs. If I lose the server then (primary) ... will the db still be open? do I not require some of these non-mirrorable database to run?
View 3 Replies
View Related
Jul 11, 2007
Hello,
I'm trying to setup database mirroring between 3 servers and the details are as follows:
1. All 3 machines reside in the same domain.
2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.
3. All the 3 machines are having their individual service domain accounts.
4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.
5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.
6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.
7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.
8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.
9. On Server 2 I'm executing this statement
ALTER DATABASE <dbname>
SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'
10. On Server 1 I'm executing this statement
ALTER DATABASE <dbname>
SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'
ALTER DATABASE <dbname>
SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'
On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."
I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.
Thanks
View 13 Replies
View Related
Mar 26, 2007
Hope this is the right thread,
When you backup with Mirror?
Is that concurrent to those devices and if so, what happens if one fails does the entire backup fail?
Cheers,
Casey
View 1 Replies
View Related