hi guys , does anyone of you have a good solution on the Error 1413: Communications to the remote server instance failed before database mirroring was fully started issue?
Best Regards,
Hans
I had a problem with mirroring that I only was able to resolve when I attempted to replicate instead. Replication gave me an error message that expained the problem; mirroring simply failed.
Is there any place where mirroring logs the details of what it is doing & why it is failing?
BTW, the problem was that the server name was changed after SQL was installed. Although i knew this, the forums indicated taht SQL 2005 automatically picks up the new name after it is restarted. This apparently is not true...
Thanks
Dave S
I attempted to setup database mirroring using a High Availability scenario but when I installed SQL is chose to use local system accounts for all the services. Consequently, I stubled upon a microsoft article explaining how to setup mirroring using local system accounts and certificate authentication but I am stil not able to get it to work. When I try ti initiate the mirror from the mirror server I receive an error stating "Neither the partner nor the witness server instance for database "EDENLive" is available. Reissue the command when at least one of the instances becomes available." I have checked all the endpoints and everything seems to be in order. I even checked to make sure that each server was listening on the appropriate ports and I AM able to telnet to the ports. Please help!
We've implemented mirroring between two identical servers. Sporadically, the mirroring session will drop and the ERRORLOG reflects the errors below at the exact time the mirroring session becomes suspended. We do not manage our back end network since we use a dedicated hosting environment at a remote location. Is this issue solely caused by network connectivity issues, or are there other factors at work?
2007-07-16 04:24:37.24 spid23s Error: 1453, Severity: 16, State: 1.
2007-07-16 04:24:37.24 spid23s 'TCP://192.168.215.92:5022', the remote mirroring partner for database 'evestment', encountered error 1204, status 4, severity 19. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.
2007-07-16 04:24:48.46 spid23s Error: 1479, Severity: 16, State: 1.
2007-07-16 04:24:48.46 spid23s The mirroring connection to "TCP://192.168.215.92:5022" has timed out for database "evestment" after 10 seconds without a response. Check the service and network connections.
I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
Operating system is Windows 2012.
Is there a way to specify a given Schema as the currently active one for a given database session ? i.e. im looking for somthing like:
Use Schema=<name>
Execute Queries / Stored Procs, etc
.....
I want all the Queries and Stored Procs to execute on the Schema set initially.
I can't use the Default_Schema on the User to do this due to certain restrictions in the way we create users and Schemas.
I know that i can always qualify the objects in the Queries with a Schema Name, but i have a requirement where the Queries need to be generic in nature and need to run on one of many identical schemas in the Database. Is this Possible in Sql Server?
Any help will be greately appreciated.
Thanks
I have a Windows NT group that is used to delegate certain database responsibilities to other members of staff and I am trying to grant permissions for the members of the group to be be able to establish database mirroring sessions, as in run the following:
ALTER DATABASE <database>
SET PARTNER = 'tcp://principal_server.domain.com:port';
Although the group has db_owner role membership to the user database which grants the ALTER permission on the database, the following is being generated in the error log when they get to this step on the intended Mirror instance after restoring the database correctly in preperation:
SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 10/29/15 11:16:15 spid 59
*
*
* Exception Address = 00007FF9A6AF838C Module(sqlmin+000000000003838C)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000000000D8
* Input Buffer 210 bytes -
* alter database <redacted> set partner = '<redacted>';
As you can see, the statement is denied to the user. There are no issues with the database as I am able to run the same query successfully using my own sysadmin account after the failed attempt. What other minimum permissions the group might need to successfully enable them to setup a mirroring session?
Hi:
I like to know how I may store some user-specific data at the beginning of a session and retrieve the same data later during the session in a SQL command. For those of you familiar with ORACLE, I am trying to mimic the SET_CLIENT_INFO and GET_CLIENT_INFO functions of ORACLE's DBMS_APPLICATION_INFO package on SQL Server.
Any suggestions?
thanks,
Peter
Hi,
I would like to demonstrate mining temporary models in an ASP.NET application.
Creating, trainning, predicating actions are all witten at C# codes as follows:
Code Snippet
using (AdomdCommand cmd = new AdomdCommand())
{
// Build temporary mining model
cmd.Connection = asConn;
cmd.CommandText = "CREATE SESSION MINING MODEL " + modelName +
" (" +
"HCVS_MemberId Text KEY," +
"HCVS_MeasureDate DATE KEY TIME, " +
"SysPressure LONG CONTINUOUS PREDICT, " +
"DiaPressure LONG CONTINUOUS PREDICT," +
"Pluse LONG CONTINUOUS PREDICT" +
") " +
"USING Microsoft_Time_Series(Missing_Value_Substitution='Mean' ) "; // Periodicity_Hint = '{12}'
cmd.ExecuteNonQuery();
// Train Data
cmd.CommandText = "INSERT INTO " + modelName + " (HCVS_MemberId, HCVS_MeasureDate, SysPressure, DiaPressure, Pluse) " +
"OPENQUERY([Healthcare], " +
" 'SELECT HCVS_MemberId, HCVS_MeasureDate, SysPressure,DiaPressure,Pluse" +
" FROM v_VitalSignForecast WHERE HCVS_MemberId=''" + id + "'' AND HCVS_MeasureDate>=''" + from.ToShortDateString() + "'' AND HCVS_MeasureDate<=''" + to.ToShortDateString() +"'' ')";
cmd.ExecuteNonQuery();
// Predict upon the Train Data. In addition, the standard deviation of each predicated value is retrieved
cmd.CommandText = "SELECT FLATTENED " +
"( SELECT *, " +
" SysPressure + PredictStdev(SysPressure) AS [SysPressure_PlusStdev], " +
" SysPressure - PredictStdev(SysPressure) AS [SysPressure_MinusStdev] " +
"FROM PredictTimeSeries(SysPressure, " + fDays + ") AS SysTable " +
") " +
"FROM " + modelName ;
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
DataSet sysDS = new DataSet();
adapter.Fill(sysDS);
The problem is that I do not know how to configure my Analysis Service Server to let ASP.NET account can utilize it. And ASP.NET account in trun impersonates the account who is authorized to use Healthcare DB in the Openquery. Please give a help. Thanks a lot.
Ricky.
In my environment Mirroring is set up and the Principal/Primary and secondary are set up as well. I am having trouble added my mirror as a logshipping primary.
BOL says to create the Mirror/Primary I need to use the Transact-SQL commands. But sp_add_log_shipping_primary_database checks to make sure the status of the database is "Online". So when I run sp_add_log_shipping_primary_database to add the mirror db as a Primary I get the following error:
Msg 32008, Level 16, State 1, Procedure sp_add_log_shipping_primary_database, Line 58
Database TestMirror is not ONLINE. Cannot proceed with log shipping primary processing.
Am I doing something wrong? I am following the BOL page http://msdn2.microsoft.com/en-us/library/ms187016.aspx .
Hi all,
I have configured some Mirroring alerts based on Micosoft's documents. Mirroring.
If you can help me in setting some of the "Thresholds" for the Mirroiring alerts. That will be helpful, because i didnt see anywhere about the actual threshold limits setting for alerts ( In Production). Any approx. also OK.
At Principal:
(a) Unsent Log = ------ KB
(b) Oldest Unsent Transaction = ---- Minutes
At Mirror:
(c) Unrestored Log = ---- KB.
(d) Mirror Commit Overhead = --- MilliSeconds
Thanks again.
I have setup database mirroring with witness server. To prevent unnecessary failover because of network slow or other issue, I changed the timeout setting as
ALTER DATABASE <Database Name>
SET PARTNER TIMEOUT 120
Which I understand if connection is broken between principal and mirror, principal database will wait for 120 second and after that only automatic failover will happen.
If this is true, it does not happen in my case. Failover happens before120 second.
I have written a stored procedure that calls a C# class. The sp is called by a trigger on table A. The class has 2 methods. Method 1 is the main call from the stored procedure. Inside it, it sets a connection to the db. Then it calls another private method which has it's own connection and returns a dataset so that I can close that connection by the end of the method. When I execute the sp from within sql management studio, it runs just fine. But when being called from the trigger, I get the "transaction context in use by another session error" error. I read that I needed to wrap my 2nd connection in ...
using (new TransactionScope(TransactionScopeOption.Suppress)){ open conn here, do work, etc.}
I've tried that and I still get the same error from the trigger. Anyone know what else I can try? Thanks.
When i am connecting Report Manager report through our ASP.NET 2.0 application using Report Viewer control frequently we get message ASP.Net Session expired .
How can we avoid this error.While i directly opens Reports through the Report Manager reports are opening without any problem.
My .Net application Code.
ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://SERVER/reportserver");
ReportViewer1.ServerReport.ReportPath = @"/myreport/reportname";
I am programming in ASP and SQL server.
I am using this tag <%Transaction=Required%> to do the transaction.
And it woks well, but the problem is that sometimes I get this error: "transaction context in use by another session"...
It's really weird because it only happens in the same row. If choose this row to be shown I get that message, but if choose another row I get result with no error. And if take out the tag <%Transaction... I don't get any error.
I've searched in other forums about this error, and one guy claims that there is a bug in the MTS and there is no way to solve the problem.
I have a c# stored procedure that is being called from a trigger. When I execute it from management studio, it works just fine. But, when I update a record in the table that has the trigger that calls the sp, I get the error "transaction context in use by another session error". I've tried a few of the "fixes" that I found through searching, but so far nothing seems to work. What I've tried so far is...
Removing the transaction from my code
making sure my code is only using 1 connection
setting XACT_ABORT ON
Any other ideas? Thanks.
I downloaded a copy of sql server and when I run the following query in the sql management studio after choosing new query , DMX I get the above error
CREATE MINING MODEL [NBSample]
(
CustomerKey LONG KEY,
Gender TEXT DISCRETE,
[Number Cars Owned] LONG DISCRETE,
[Bike Buyer] LONG DISCRETE PREDICT
)
Using Microsoft_Naive_Bayes
Also in the pane Mining Model I get the message No mining models found. The only error I get when I installed SQL server was a COM plus registration error. I am running Windows XP SP2. Can someone please help.
Vijay Mukhi
HOD Helios
I have an ASP.Net (C# 2.0) application that has been using SQL Server 2005 Standard Edition with Service Pack 1 to hold the session state in a testing environment. Currently, the session state is being stored in TempDB, rather than the ASPState database. This has worked very well for us until yesterday. We installed SQL Server 2005 Service Pack 2, as well as the Critical Update for Service Pack 2 (KB933508). Once the SQL server was rebooted, I got the following error message when I tried to access the web application.
The SELECT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.The SELECT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.The INSERT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.
In the web.config file for the application, I have a SQL username and password defined that can access the ASPState database. To correct this issue, I had to give this user db_datareader and db_datawriter access to tempDB.
Has anyone else run across this problem, and is it related to SQL Server 2005 Service Pack 2?
I have one drop and create procedure script in one sql session and execute the procedure in other session. Now when i am compiling the procedure in first session, it gets completed successfully.
Now when i move to other session and try to execute the procedure, it gives me error saying :
"Invalid column name 'ColumnName'."
Now when I execute the procedure in same session in which procedure was compiled, it runs successfully. Once run, I go back to my other session and it runs again.
Hello,
We are running SQL2K5 and have a Web server with a family of sites all sharing an identical connection string to enable ADO connection pooling between them. Today for about 20 minutes we had several (all?) connections from one site that uses a specific DB get a connection reuse error which showed in out SQL logs:
DESCRIPTION: The client was unable to reuse a session with SPID #, which had been reset for conection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
We also have SQL Server slowdown and log in problems from other applications that seemed a symptom of this, or some third unknown cause. Note, the # means the run-time spid number was inserted. The misspelling "conection" comes right out of sys.messages (it is not our custom error):
select top 10 * from sys.messages where message_id = 18056
The immediately preceeding error in the SQL Log was always:
Message
Error: 18056, Severity: 20, State: 29.
Where Severity and State vary, but "Error: 18056" is consistent, although I can find no documentation on "Error: 18056" through Google or MSDN.
Also, the "The client was unable to reuse a session ..." error seems not to be referred to anywhere.
In our IIS logs, the matching entries are of the form:
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
and
Invalid connection string attribute
My questions: Does anyone have experience with this error? We have real good history with ADO connection pooling, but can a "bad" connection be pooled, and if so can it be "flushed" or the pool "drained"?
Thank you for anything you have to offer!
Since I had a bad error in replication, my replication monitor won't show the tab "Error details or messages of the select session".
I am not able to activate or to show this "tab", to get all information about my error.
I added two screeshots, one with the 3 tab, and the other, were the information about my error are missing. How i can my default view back?
Replication Monitor OK:
My Replication Monitor with missing tab:
I had to know, which ID is causing a primary key violation. Instead of Replication Monitor, I would use t-sql.
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
Thanks.
Hi,
I am trying to mirror the databse with a witness server.
I have sql server 2005 with SP1 or both my mirror and principle and sql express on my witness.
The problem is when I click start mirroring, I get the following error
The server network address"TCP://serveraddress:7026" can not be reached or does not exist. Check the network address name and that the ports for the local and remote epoints are operational.
(Microsoft SQL server, error:1418)
I have pinged the address it works fine . The telnet comes up with a blank screen.
When I look at the the server log file viewer, I get the following message
An error occured while receiving data: 10054 (An existing connection was forcibly closed by the remote host)
for 'TCP://serveraddress:7026'.
Please help me soon
When I use database mirroring, I get the following error when the mirroring monitor runs.
Incorrect syntax near '-'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.
This is the stored procedure that mirroring creates automatically. It isn't a user sproc. Several other people have posted this error and it seems to be a Microsoft bug.
Any ideas.
Jeff
I got the following error log from SQL profiler when I tried to start mirroring
"
2006-11-10 11:54:47.28 Logon
Database Mirroring login attempt by user 'NT AUTHORITYANONYMOUS LOGON.' failed
with error: 'Connection
handshake failed. The login 'NT
AUTHORITYANONYMOUS LOGON' does not have CONNECT permission on the endpoint.
State 84.'. [CLIENT: 10.77.100.86]"Anybody knows the solution for the same please let me know
Hello again,
This is further to my previous post, which has had over thirty viewing but as yet no replies. Is there really no solution to this problem?
I have now given up trying to use the wizard to get mirroring running, as although I'm not entirely sure, some stuff I've read implies that the wizard only works using Windows authentication. Maybe someone can confirm this. Either way, the wizard doesn't work for me (see my previous post).
So instead of using the wizard I've now tried to set up mirroring manually using SQL statements. Following are the steps I've taken. 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 (EXPLOSION)
There might be more stuff to do after this, but here is where it breaks down. Again, the error is the same as before when using the wizard:
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://10.152.58.243:7024" can not be reached or does not exist. Check the network address name and reissue the command.
Which is **INCORRECT** or at least highly unhelpful because:
i) netstat -abn shows the sql server process listening on port 7024; no other processes are listening on this port
ii) I can telnet to port 7024 on this machine and issue the 16 keystrokes
Please, someone help, I am crying tears of despair. SQL below.
Cheers,
Mike
/*
The following is a complete list of the SQL statements issued.
Please assume they are issued on the relevant master databases.
*/
/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE BILL_PRIMARY_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '**************' -- real password used
GO
CREATE CERTIFICATE BILL_PRIMARY_CERT
WITH SUBJECT = 'BILL_PRIMARY_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BILL_PRIMARY_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO
BACKUP CERTIFICATE BILL_PRIMARY_CERT
TO FILE = 'C:certificatesBILL_PRIMARY_CERT.cer'
GO
-- then copy certificate to other two machines
/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE BILL_SECONDARY_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '**************' -- real password used
GO
CREATE CERTIFICATE BILL_SECONDARY_CERT
WITH SUBJECT = 'BILL_SECONDARY_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BILL_SECONDARY_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO
BACKUP CERTIFICATE BILL_SECONDARY_CERT
TO FILE = 'C:certificatesBILL_SECONDARY_CERT.cer'
GO
-- then copy certificate to other two machines
/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE BILL_WITNESS_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '**************' -- real password used
GO
CREATE CERTIFICATE BILL_WITNESS_CERT
WITH SUBJECT = 'BILL_WITNESS_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BILL_WITNESS_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO
BACKUP CERTIFICATE BILL_WITNESS_CERT
TO FILE = 'C:certificatesBILL_WITNESS_CERT.cer'
GO
-- then copy certificate to other two machines
/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */
/* enable inbound from the mirror */
DROP CERTIFICATE BILL_SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO
CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE CERTIFICATE BILL_SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'c:certificatesBILL_SECONDARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO
/* enable inbound from the witness */
DROP CERTIFICATE BILL_WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = '****************' -- real password used
GO
CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE CERTIFICATE BILL_WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:certificatesBILL_WITNESS_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO
/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */
/* enable inbound from the primary */
DROP CERTIFICATE BILL_PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO
CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE CERTIFICATE BILL_PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:certificatesBILL_PRIMARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO
/* enable inbound from the witness */
DROP CERTIFICATE BILL_WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = '****************' -- real password used
GO
CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE CERTIFICATE BILL_WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:certificatesBILL_WITNESS_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO
/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */
/* enable inbound from the mirror */
DROP CERTIFICATE BILL_SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO
CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE CERTIFICATE BILL_SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'c:certificatesBILL_SECONDARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO
/* enable inbound from the primary */
DROP CERTIFICATE BILL_PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO
CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE CERTIFICATE BILL_PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:certificatesBILL_PRIMARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO
/* -------- 7. SET MIRROR'S PARTNER TO THE PRIMARY SERVER -------- */
ALTER DATABASE failover_test
SET PARTNER OFF
GO
ALTER DATABASE failover_test
SET PARTNER = 'TCP://10.152.58.242:7024';
GO
/* -------- 8. SET PRIMARY'S PARTNER TO THE MIRROR SERVER -------- */
ALTER DATABASE failover_test
SET PARTNER OFF
GO
ALTER DATABASE failover_test
SET PARTNER = 'TCP://10.152.58.243:7024';
GO
/*
Response:
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://10.152.58.243:7024" can not be reached or does not exist. Check the network address name and reissue the command.
*/
Hi,
when I tried to mirror database giving principal and mirror server names every details I am getting the error
'Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments.To enable database mirroring for evaluation purposes, use trace flag 1400 during startup.'
How to user trace flag 1400 during startup?
Since I am newbie Kindly guide me with step by step procedure.
thanks
When I configured database mirroring between two servers in separate DMZ regions, I get the following error on the principle server when I execute the ALTER DATABASE xxxxxxxx SET PARTNER = '****':
The server network address %%% can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)
Ports have been opened on both machines and I can TELNET both without any problems. I have included the steps that I used.
DATABASE MIRRORING TEST CONFIGURATION
SQL Server 2005 STD
Principal: AA-AAA-AA01
Mirror: BB-BBB-BB07
A. Create certificates for outbound connection on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.
1. In the master database, create a database Master Key.
M:DBADMMIRRORINGCREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO
2. In the master database, create an encrypted certificate on the server instance.
M:DBADMMIRRORINGCREATE_AA01_MIRRORING_CERT
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
WITH SUBJECT = 'AA-AAA-AA01 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO
3. Create an endpoint for the server instance using its certificate.
M:DBADMMIRRORINGCREATE_AA01_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
4. Back up the certificate to a file and securely copy it to the other system or systems.
M:DBADMMIRRORINGCREATE_AA01_CERT_BKUP
BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
TO FILE = 'M:DBADMMIRRORINGAA_AAA_AA01_MIRRORING_CERT_BKUP.cer';
GO
B. Create certificates for outbound connection on mirror server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.
1. In the master database, create a database Master Key.
M:DBADMMIRRORINGCREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO
2. In the master database, create an encrypted certificate on the server instance.
M:DBADMMIRRORINGCREATE_BB07_MIRRORING_CERT
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
WITH SUBJECT = 'BB-BBB-BB07 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO
3. Create an endpoint for the server instance using its certificate.
M:DBADMMIRRORINGCREATE_BB07_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = PARNTER
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
4. Back up the certificate to a file and securely copy it to the other system or systems.
M:DBADMMIRRORINGCREATE_BB07_CERT_BKUP
BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
TO FILE = 'M:DBADMMIRRORINGBB_BBB_BB07_MIRRORING_CERT_BKUP.cer';
GO
C. Configure server instances inbound mirroring connections on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.
1. Create a login for other system.
M:DBADMMIRRORINGCREATE_BB07_LOGIN
USE master;
CREATE LOGIN BB07_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
2. Create a user for that login.
M:DBADMMIRRORINGCREATE_BB07_USER
USE master;
CREATE USER BB07_MIRROR_ADMIN FOR LOGIN BB07_MIRROR_ADMIN;
GO
3. Obtain the certificate for the mirroring endpoint of the other server instance.
4. Associate the certificate with the user created in step 2.
M:DBADMMIRRORINGCREATE_BB07_USER_CERT_LINK
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
AUTHORIZATION BB07_MIRROR_ADMIN
FROM FILE = 'M:DBADMMIRRORINGBB_BBB_BB07_MIRRORING_CERT_BKUP.cer'
GO
5. Grant CONNECT permission on the login for that mirroring endpoint.
M:DBADMMIRRORINGGRANT_BB07_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO BB07_MIRROR_ADMIN;
GO
D. Configure server instances inbound mirroring connections on master server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.
1. Create a login for other system.
M:DBADMMIRRORINGCREATE_AA01_LOGIN
USE master;
CREATE LOGIN AA01_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
2. Create a user for that login.
M:DBADMMIRRORINGCREATE_AA01_USER
USE master;
CREATE USER AA01_MIRROR_ADMIN FOR LOGIN AA01_MIRROR_ADMIN;
GO
3. Obtain the certificate for the mirroring endpoint of the other server instance.
4. Associate the certificate with the user created in step 2.
M:DBADMMIRRORINGCREATE_AA01_USER_CERT_LINK
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
AUTHORIZATION AA01_MIRROR_ADMIN
FROM FILE = 'M:DBADMMIRRORINGAA_AAA_AA01_MIRRORING_CERT_BKUP.cer'
GO
5. Grant CONNECT permission on the login for that mirroring endpoint.
M:DBADMMIRRORINGGRANT_AA01_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO AA01_MIRROR_ADMIN;
GO
E. Back up the Principal Database and copy backup files to mirror server.
F. Restore the Principal Database on the standby database server (use NORECOVERY).
G. Configure the mirroring partners. Refer to http://msdn2.microsoft.com/en-us/library/ms191140.aspx.
1. On the mirror server instance on BB-BBB-BB07, set the server instance on AA-AAA-AA01 as the partner (making it the initial principal server instance).
M:DBADMMIRRORINGACTIVATE_MIRROR_FOR_AA01
--At BB-BBB-BB07, set server instance on AA-AAA-AA01 as partner (principal server):
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://AA-AAA-AA01.mycompany.com:5999';
GO
2. On the principal server instance on AA-AAA-AA01, set the server instance on BB-BBB-BB07 as the partner (making it the initial mirror server instance).
M:DBADMMIRRORINGACTIVATE_MIRROR_FOR_BB07
--At AA-AAA-AA01, set server instance on BB-BBB-BB07 as partner (mirror server).
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://BB-BBB-BB07.mycompany.com:5999';
GO
3. Execute the following SELECT on both servers to review mirroring information.
SELECT * FROM SYS.DATABASE_MIRRORING
Hi guys, does anyone get this snapshot on mirroring. My mirror server had been down for 1 month+ and just up again. Then my principal server will automatic synchronizing with it. Once i create snapshot on the mirror server for checking whether it get the latest record, there's an error msg which shows 'The database must be online to have a database snapshot.' Then I leave it for 1 hour+ for synchronizing and then still the same. Can I get any solution of it? Thx for the assistance.
Best Regards,
Hans
I am trying to do mirroring using SQL Server Ent. 2005 SP1 on two machines. I was able to create end points on both the machines at the port no.s 5091 and 5092 respectively. I have all the security setting settings for both the users. I am getting error no. 1418 while starting mirroring sessions.
Can anybody tell me how to troubleshoot this error?
OS:Windows XP Professional Edition
SQLServer: 2005(DE).SP2(Have installed 2 instances of 2005 in my local system)
I am trying to do database mirroring,via SQL Server Management Studio.
1)configure security setup,(showed success).
2)when i start mirroring,i am getting the following error.
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'MIRROR_DEMO'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://tp190.DELL.servers.corp:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I have followed the microsoft link and i have enabled the port numbers aswell.
We have recently set up two SQL Server 2005 Standard Edition serverswith database mirroring. The mirrors function and fail over without aproblem, but the Database Mirroring Monitor Job fails every time withthe following error message:Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrectsyntax near the keyword 'with'. If this statement is a common tableexpression or an xmlnamespaces clause, the previous statement must beterminated with a semicolon. [SQLSTATE 42000] (Error 319). The stepfailed.Both SQL Servers are running SP2 with the latest patches.Can anyone help with the resolutionto this this issue?Thank you!Bosko
View 5 Replies View RelatedCan I know is it possible that if the database servers' collation in both principal and mirror server might unable to get the mirroring? Because one of database collation is Latin1_General_CI_AS while another server (mirror) is SQL_Latin1_General_CP1_CI_AS. I did several type of troubleshooting but still unable to solve the Error 1418. All the ports are stated as started, able to use telnet to connect to the server's port but still unable to get a connection for the database mirroring. I do really need help on it. Thx for the assistance and kindness.
Best Regards,
Hans