Is it indeed a requirement that database mirrors have the same name on a server? The reason I ask is that I have several servers with the same database name, and I'd like to mirror all of them to a single (disaster) server and, by necessity, have each mirror to a distinct db name.
Thanks
The database MUST have the same name on both the Principal and the Mirror. Mirroring will not work in your scenario unless you provide distinct names for the databases.
We have recently set up two SQL Server 2005 Standard Edition servers with database mirroring. The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message:
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.
Both SQL Servers are running SP2 with the latest patches.
Can you please let me know what we have to do to resolve this issue?
Thank you!
Bosko
Is this been like this since the invocation of Database mirroring on these instances?
If not see what has been changed since the last successful occurence.
|||The problems started upon implementation of the database mirroring|||I'm also facing the same issue.
Did anyone find a solution or workaround?
|||I posted this in a couple different forums/newsgroups and never got an answer. We are still having this problem.
Bosko
We have recently set up two SQL Server 2005 Standard Edition servers with database mirroring. The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message:
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.
Both SQL Servers are running SP2 with the latest patches.
Can you please let me know what we have to do to resolve this issue?
Thank you!
Bosko
Is this been like this since the invocation of Database mirroring on these instances?
If not see what has been changed since the last successful occurence.
|||The problems started upon implementation of the database mirroringsqlWhen 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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_AA01_CERT_BKUP
BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\AA_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_BB07_CERT_BKUP
BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\BB_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:\DBADM\MIRRORING\CREATE_BB07_LOGIN
USE master;
CREATE LOGIN BB07_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO
2. Create a user for that login.
M:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_BB07_USER_CERT_LINK
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
AUTHORIZATION BB07_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer'
GO
5. Grant CONNECT permission on the login for that mirroring endpoint.
M:\DBADM\MIRRORING\GRANT_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:\DBADM\MIRRORING\CREATE_AA01_LOGIN
USE master;
CREATE LOGIN AA01_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO
2. Create a user for that login.
M:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_AA01_USER_CERT_LINK
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
AUTHORIZATION AA01_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer'
GO
5. Grant CONNECT permission on the login for that mirroring endpoint.
M:\DBADM\MIRRORING\GRANT_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:\DBADM\MIRRORING\ACTIVATE_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:\DBADM\MIRRORING\ACTIVATE_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
Can ping both IP addresses and DNS names in both servers? If no, please try to add record in both networks DNS servers or add both SQL servers IP addresses in local hosts files (e.g. C:\Windows\System32\Drivers\Etc\hosts). Moreover, is it opened all SQL required ports in firewall? Detail information please refer to "How to: Configure a Firewall for SQL Server Access http://msdn2.microsoft.com/en-us/library/ms175043.aspx". Active Directory ports are required too.|||Thanks for the info. During the meanwhile, I set up transactional replication.
|||By the way, the servers have been added to the HOST files on each server. The ports appeared to be opened. I did a netstats -adn in the command prompt. I may stick with replication since I am on a tight deadline. I was hoping to get database mirroring to work in a short time since it appears to be easier in switching back and forth for failover scenerios.
Thanks again.
|||Cause and Resolution of Database Engine Errors
http://msdn2.microsoft.com/en-us/library/aa337361.aspx
http://msdn2.microsoft.com/en-us/library/ms365262.aspx|||Did you try to install two SQL servers in same network before?
|||I wanted to first try going from server to server within the network but I could not find two server with similar configurations. So, I ended up testing database mirroring for the first time between two servers in their own DMZ space.
Thanks.
|||I suggest you setup two testing servers in Virtual Environment, such as MS Virtual and VMware Server. It's free download and use. It easy to learn and test Database Mirroring.
Database Mirroring in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Database mirroring setup in SQL Server 2005
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.html?bucket=ETA&topic=301326
|||Thanks. I will review the posted website.
|||I finally got pass the 1418 error message. The problem was with the way the server name was configured and the HOSTS file on the C drive.
sqlWhen 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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_AA01_CERT_BKUP
BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\AA_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_BB07_CERT_BKUP
BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\BB_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:\DBADM\MIRRORING\CREATE_BB07_LOGIN
USE master;
CREATE LOGIN BB07_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO
2. Create a user for that login.
M:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_BB07_USER_CERT_LINK
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
AUTHORIZATION BB07_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer'
GO
5. Grant CONNECT permission on the login for that mirroring endpoint.
M:\DBADM\MIRRORING\GRANT_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:\DBADM\MIRRORING\CREATE_AA01_LOGIN
USE master;
CREATE LOGIN AA01_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO
2. Create a user for that login.
M:\DBADM\MIRRORING\CREATE_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:\DBADM\MIRRORING\CREATE_AA01_USER_CERT_LINK
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
AUTHORIZATION AA01_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer'
GO
5. Grant CONNECT permission on the login for that mirroring endpoint.
M:\DBADM\MIRRORING\GRANT_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:\DBADM\MIRRORING\ACTIVATE_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:\DBADM\MIRRORING\ACTIVATE_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
Can ping both IP addresses and DNS names in both servers? If no, please try to add record in both networks DNS servers or add both SQL servers IP addresses in local hosts files (e.g. C:\Windows\System32\Drivers\Etc\hosts). Moreover, is it opened all SQL required ports in firewall? Detail information please refer to "How to: Configure a Firewall for SQL Server Access http://msdn2.microsoft.com/en-us/library/ms175043.aspx". Active Directory ports are required too.|||Thanks for the info. During the meanwhile, I set up transactional replication.
|||By the way, the servers have been added to the HOST files on each server. The ports appeared to be opened. I did a netstats -adn in the command prompt. I may stick with replication since I am on a tight deadline. I was hoping to get database mirroring to work in a short time since it appears to be easier in switching back and forth for failover scenerios.
Thanks again.
|||Cause and Resolution of Database Engine Errors
http://msdn2.microsoft.com/en-us/library/aa337361.aspx
http://msdn2.microsoft.com/en-us/library/ms365262.aspx|||Did you try to install two SQL servers in same network before?
|||I wanted to first try going from server to server within the network but I could not find two server with similar configurations. So, I ended up testing database mirroring for the first time between two servers in their own DMZ space.
Thanks.
|||I suggest you setup two testing servers in Virtual Environment, such as MS Virtual and VMware Server. It's free download and use. It easy to learn and test Database Mirroring.
Database Mirroring in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Database mirroring setup in SQL Server 2005
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.html?bucket=ETA&topic=301326
|||Thanks. I will review the posted website.
|||I finally got pass the 1418 error message. The problem was with the way the server name was configured and the HOSTS file on the C drive.
I have set up 3 servers - Primary, Mirror and Witness. When I run the database mirroring wizard all my endpoints are configured, but when i start the mirroring service i get a 1418 error - in the logs in says -
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.20.1.5]
I have looked through the BOL and can't find anything helpful. - I'm pretty new to SQL server, so any help is very much appreciated!
Thanks
Kerpoise
This is not a SQL problem but an authentication issue. The right approach is to follow the guidance of the authentication troubleshooting whitepaper: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
HTH,
~ Remus
I can't find a simple solution in the white paper. Isn't there a simple way to either - authenticate as a sysadmin account, or to allow NT AUTHORITY/ANONYMOUS to have CONNECT privileges to all servers?
It seems like this should be a very quick thing to do, but I really can't figure it out.
Please help!|||
Do not grant connect permission to ANONYMOUS LOGON.
When connecting the two instances what gets authenticated is the 'service account', the Windows account that runs the SQL Server instance. Depending on how you insnstalled the instances, the appropiate action is:
- if installed as 'LOCAL SERVICE': you must change the service account, it will never work. The SQL Management console in mmc has an option to change the service account, selecta different account.
- if installed as 'NETWORK SERVICE' or 'LocalSystem': you have to register the service SPN for Kerberos authentication to succeed. Use a tool like setspn.exe (available at microsoft download center) and register the SPN. Mirroring will use an SPN on the format 'MSSQLSvc/<partnername>:<partnerport>'. Also, make sure the AUTHENTICATION option on the mirroring endpoint is WINDOWS, WINDOWS NEGOTIATE or WINDOWS KERBEROS (in other words NTLM will not work). After registering the two SPNs (both for mirror and principal!), the authentication will resolve to the machine account ('DOMAIN\MachineName$'), you must create a login for this account and grant connect permission to it.
- if installed as a local account ('PrincipalMachine\LocalAccount' and/or 'MirrorMachine\LocalAccount'): it will not work, you must change the service account(s).
- if installed as domain account ('DOMAIN\UserName'), then it should work as long as both the mirror and the principal are in the same domain, or there is a trust relationship between their domains.
An alternative is to use certificate based authentication, then the whole domain/user/SPNs issue vanishes as the authentication will use a diferent protocol (SChannel's TLS).
HTH,
~ Remus
we are having 2 prodction servers in our environment.
and we are planning to perform database mirroring between them.i want to mirror server 1 databases to server2 and server 2 databases to server 1.
is this possible?
if possible , can any one give me a link to document where i can find more info on this, or send me the steps what i need to done for this...
thanks
Haven't we discussed this on SSP forums 
|||The process of setting up database mirroing is documented in Books online, have you looked there?
hi sathya,
i am really confused with the concept in my scenario.that's why i am posting everywhere...
i can establish the mirroring between the 2 servers with the principal and the mirror script you posted to me. like creating the certificate,master key and dbmirrorendpoint for principal and the mirror server seperately...
now my scenario is different , i alredy mirrored the prodcution databases to server2, and failed over to server 2(which is now acting as principal m server).
now we want to mrror thoese databases to our new 64 bit server, in that server shall i need to run mirror script or principal script....
because server 2 already having mirror script..
please help me out in this situation...
|||Priya, from your question this is what i understood..........you have mirroring configured from server 1 to server 2 and you have failed over to server 2 so it has become the principal now..........then from server 2 you want to configure mirroring to someother server sql server .......it is possible.......i dunno abt the scripts you are talking abt......
|||Yes this is possible ; For more information on configuring Database Mirroring refer Books Online.
HTH
Vishal
sqlHello,
I'm having some troubles to set up database mirroring on Itanium II based servers.
I have 3 servers registered in the same domain (windows authentication)
I have created manually the endpoint and there are all 3 started (port 5022)
When I do the alter database set partner on the mirror site it works.
When I do it on the principal instance, I have the following error: Msg 1418 the server instance "TCP:itanium8.sql.net:5022" is not running or does not exist.
If I telnet to the port 5022 I have a reply.
SAme error if I try to set up the mirroring using the GUI (wizard). I am using CTP June.
Any ideas?
Thanks
Jerome
OK, here we go:
1. Testers have confirmed that they have setup mirroring on Itanium II servers.
2. the fact that the mirror suceeds is normal. all that command does is put the server in a "wait to be contacted" mode.
3. this is different from what Pamb sees in the other thread. there, mirroring was able to log in, but the server name information didn't correctly compare. this is different from what you have. in your case, the server wasn't allowed to log in at all.
so, can you telnet from each server to the other server? you imply that it works one way, but does it work both ways? you should be able to type 16 characters and the connection should close. that tells you that you are able to connect to the port.
your error message implies that you didn't specify the '//' on the input string. it is important that you include the '//' on the server name setup.
what is in the errorlog?
check security again.
thanks,
mark|||Mark,
Ok .. it seems first of all that there is no way to do the setup using the GUI/Wizard. I always get the error described above even if the endpoints are already defined and started. I have discover that for strange reason if I create endpoint using three differents port (I have three independant server so according to the doc I should be able to use the same port) it works but only using command line:
These are the steps I have to go through to have the mirror configured:
1- backup on the principal
2- restore on the mirror with norecovery
3 - create endpoint
principal: state= started port=5022 role=partner
mirror: state=started port=5023 role=partner
witness: state=started port=5024 role=witness
4 - setup the mirror
mirror site: alter db set partner='TCP://itanium8.sql.net:5022'
principal site:
alter db set partner='TCP://itanium9.sql.net:5023'
alter db set partner='TCP://itanium10.sql.net:5024'
But again no way to set it up with the wizard....
Thanks
Jerome
|||so, you should be able to setup and manage mirroring through the GUI/UI. i watch 800 attendees do it today at a conference.
you do have to backup/restore through a different channel.
you have to setup certificates through t-sql.
glad you got it to work.
mark|||Hello Mark,
1 -You do have to backup/restore through a different channel.
What do you mean by that?
2- you have to setup certificates through t-sql.
Event if all the three instances are registered in the same domain? The wizard does not do that for you? In the BOL: "For communication accross non-trusted domain you must use certificate ....." ... in the BOL "If you are setting up database mirroring on a domain, and all SQL Server instances use the same service login and password, you do not need to create logins on the server"
I just want to understand....
Thanks
Jerome
|||1. "by a different channel" means that you have to have backed up and restored the database before you begin the wizard.
2. sorry, what i meant is that if you plan on using certificates, then you must use t-sql. using the domain account in the domain is probably the best way. but if you want to run as a non-domain account or accross non-trusted domains, then you MUST use certificates.
mark
