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)
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");
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,
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
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?
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.
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?
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.
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?
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)
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
//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
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.
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?
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.
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?
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.
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?
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).
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() ); }
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.
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?
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?
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?
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?
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.
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.
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?
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?
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?
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.