|
Home > Archive > MS SQL Server > April 2006 > Re: help!I have some question about sql server2005 database mirror
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: help!I have some question about sql server2005 database mirror
|
|
| lansehai-chen@hotmail.com 2006-04-06, 7:25 am |
| but the message's means i am not configured for this database!!
The message is "Msg 1416, Level 16, State 31, Line 1
Database "Mirrortest" is not configured for database mirroring."
Please help me!!Thank you!!
--
Study everyday!
“Tibor Karaszi”编写：
> According to the sys,messages table, it means that you are already mirroring this database.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
> news:BD052D60-72CC-439B-8DE0- F328867FD3B5@microso
ft.com...
>
>
| |
| Tibor Karaszi 2006-04-06, 7:25 am |
| Sorry, I misread the error message.
Did you start SQL Server with trace flag 1400?
Where do you execute the command? On the principal or the mirror?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
news:D6696B04-3169-47D3-A5B8- 5C41B1BA228A@microso
ft.com...[color=darkred]
> but the message's means i am not configured for this database!!
> The message is "Msg 1416, Level 16, State 31, Line 1
> Database "Mirrortest" is not configured for database mirroring."
> Please help me!!Thank you!!
> --
> Study everyday!
>
>
> “Tibor Karaszi”编写：
>
| |
| lansehai-chen@hotmail.com 2006-04-06, 9:23 am |
| But i already enable trace flag 1400 in the sql server configuration manager
properties page.
--
Study everyday!
“Tibor Karaszi”编写：
> Sorry, I misread the error message.
>
> Did you start SQL Server with trace flag 1400?
>
> Where do you execute the command? On the principal or the mirror?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
> news:D6696B04-3169-47D3-A5B8- 5C41B1BA228A@microso
ft.com...
>
>
| |
|
|
| lansehai-chen@hotmail.com 2006-04-06, 9:23 am |
| I execute the command on the mirror server and principal!!
here is my principal executed T-sql:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test1'
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate'
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5024,L
ISTENER_IP=ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert,ENCRYPTI
ON = REQUIRED ALGORITHM
RC4
,ROLE = ALL
);
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer'
USE MASTER
CREATE LOGIN hostb WITH PASSWORD = 'test1'
CREATE USER mhostb FOR LOGIN hostb
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION mhostb
FROM FILE = 'C:\HOST_B_cert.cer'
GRANT CONNECT ON ENDPOINT::Mirroring TO hostb
CREATE DATABASE Mirrortest
ALTER DATABASE Mirrortest
SET RECOVERY FULL;
USE Mirrortest
BACKUP DATABASE Mirrortest
TO DISK = 'C:\Mirrortest.bak'
WITH FORMAT
ALTER DATABASE Study
SET RECOVERY FULL;
USE Study
BACKUP DATABASE Study
TO DISK = 'C:\Study.bak'
WITH FORMAT
backup database Mirror
ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://192.168.4.252:5024'
here is my mirror executed T-sql:
USE MASTER
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test2'
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = 'HOST_B certificate for database mirroring'
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT = 5024,LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_B_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
)
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';
USE MASTER
CREATE LOGIN hosta WITH PASSWORD = 'test2'
CREATE USER mhasta FOR LOGIN hosta
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION mhasta
FROM FILE = 'C:\HOST_A_cert.cer'
GRANT CONNECT ON ENDPOINT::Mirroring TO hosta
ALTER DATABASE Mirrortest
SET RECOVERY FULL
use Mirrortest
ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://192.168.4.91:5024'
--
Study everyday!
“Tibor Karaszi”编写：
> Where do you execute the command? On the principal or the mirror?
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
> news:BE8D4AF5-395C-4575-88A4- 44C43284EA3A@microso
ft.com...
>
>
| |
| lansehai-chen@hotmail.com 2006-04-06, 9:23 am |
| Hello!!Can you tell me your MSN or other TM!!I come from China!!My name is
chenliang!!My english is not very good!!Thank you for you support to me!!
--
Study everyday!
“lansehai-chen@hotmail.com”编写：
[color=darkred]
> But i already enable trace flag 1400 in the sql server configuration manager
> properties page.
> --
> Study everyday!
>
>
> “Tibor Karaszi”编写：
>
| |
| Tibor Karaszi 2006-04-06, 11:24 am |
| I won't comment on the certification stuff, as this isn't my strongest point.
Did you do the steps in the below order?
Backup database on principal
Restore database on mirror, using the NORECOVERY option
ALTER DATABASE to set mirroring on the mirror server
ALTER DATABASE to set mirroring on the principal server
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
news:649761E4-040D-4B74-A937- A6333AE3D681@microso
ft.com...[color=darkred]
>I execute the command on the mirror server and principal!!
> here is my principal executed T-sql:
>
> CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test1'
>
> CREATE CERTIFICATE HOST_A_cert
> WITH SUBJECT = 'HOST_A certificate'
>
> CREATE ENDPOINT Mirroring
> STATE = STARTED
> AS TCP (
> LISTENER_PORT=5024,L
ISTENER_IP=ALL)
> FOR DATABASE_MIRRORING (
> AUTHENTICATION = CERTIFICATE HOST_A_cert,ENCRYPTI
ON = REQUIRED ALGORITHM
> RC4
> ,ROLE = ALL
> );
>
> BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer'
>
>
> USE MASTER
> CREATE LOGIN hostb WITH PASSWORD = 'test1'
>
> CREATE USER mhostb FOR LOGIN hostb
>
> CREATE CERTIFICATE HOST_B_cert
> AUTHORIZATION mhostb
> FROM FILE = 'C:\HOST_B_cert.cer'
>
> GRANT CONNECT ON ENDPOINT::Mirroring TO hostb
>
> CREATE DATABASE Mirrortest
>
> ALTER DATABASE Mirrortest
> SET RECOVERY FULL;
>
> USE Mirrortest
> BACKUP DATABASE Mirrortest
> TO DISK = 'C:\Mirrortest.bak'
> WITH FORMAT
>
> ALTER DATABASE Study
> SET RECOVERY FULL;
> USE Study
>
>
> BACKUP DATABASE Study
> TO DISK = 'C:\Study.bak'
> WITH FORMAT
> backup database Mirror
> ALTER DATABASE Mirrortest
> SET PARTNER = 'TCP://192.168.4.252:5024'
>
> here is my mirror executed T-sql:
> USE MASTER
> CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test2'
>
> CREATE CERTIFICATE HOST_B_cert
> WITH SUBJECT = 'HOST_B certificate for database mirroring'
>
> CREATE ENDPOINT Mirroring
> STATE = STARTED
> AS TCP (
> LISTENER_PORT = 5024,LISTENER_IP = ALL)
> FOR DATABASE_MIRRORING (
> AUTHENTICATION = CERTIFICATE HOST_B_cert
> , ENCRYPTION = REQUIRED ALGORITHM RC4
> , ROLE = ALL
> )
>
> BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';
>
>
> USE MASTER
> CREATE LOGIN hosta WITH PASSWORD = 'test2'
>
> CREATE USER mhasta FOR LOGIN hosta
>
> CREATE CERTIFICATE HOST_A_cert
> AUTHORIZATION mhasta
> FROM FILE = 'C:\HOST_A_cert.cer'
>
> GRANT CONNECT ON ENDPOINT::Mirroring TO hosta
>
> ALTER DATABASE Mirrortest
> SET RECOVERY FULL
> use Mirrortest
>
> ALTER DATABASE Mirrortest
> SET PARTNER = 'TCP://192.168.4.91:5024'
> --
> Study everyday!
>
>
> “Tibor Karaszi”编写：
>
| |
| lansehai-chen@hotmail.com 2006-04-06, 1:23 pm |
| Thank you Tibor!!I alreadly resolve this question,because error that i
restoring the principal database on the mirror database!!I'm not use the
T-sql,
I using managment!!Now i want to moinitor the database mirroring!!How can i
do??
--
Study everyday!
“Tibor Karaszi”编写：
> I won't comment on the certification stuff, as this isn't my strongest point.
>
> Did you do the steps in the below order?
>
> Backup database on principal
> Restore database on mirror, using the NORECOVERY option
> ALTER DATABASE to set mirroring on the mirror server
> ALTER DATABASE to set mirroring on the principal server
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
> news:649761E4-040D-4B74-A937- A6333AE3D681@microso
ft.com...
>
>
| |
| Tibor Karaszi 2006-04-06, 1:23 pm |
| > Now i want to moinitor the database mirroring!!How can i
> do??
There are some dynamic management views as well as catalog views that has information about the
mirroring process. Read about them in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
news:CF6C94E2-10CE-41BE-8798- 09ED9290D2FA@microso
ft.com...[color=darkred]
> Thank you Tibor!!I alreadly resolve this question,because error that i
> restoring the principal database on the mirror database!!I'm not use the
> T-sql,
> I using managment!!Now i want to moinitor the database mirroring!!How can i
> do??
> --
> Study everyday!
>
>
> “Tibor Karaszi”编写：
>
| |
| lansehai-chen@hotmail.com 2006-04-06, 1:23 pm |
| Thank you Tibor!!Does the database mirroring setup in sql server managerment
no use t-sql??
--
Study everyday!
“Tibor Karaszi”编写：
>
>
> There are some dynamic management views as well as catalog views that has information about the
> mirroring process. Read about them in Books Online.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
> news:CF6C94E2-10CE-41BE-8798- 09ED9290D2FA@microso
ft.com...
>
>
| |
| Tibor Karaszi 2006-04-06, 1:23 pm |
| > Thank you Tibor!!Does the database mirroring setup in sql server managerment
Yes, it does. I prefer to use TSQL commands directly, and it is also much easier to communicate over
media like this using TSQL commands.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
news:DA269E87-C3CE-4437-86C0- 65CD0C804FE4@microso
ft.com...[color=darkred]
> Thank you Tibor!!Does the database mirroring setup in sql server managerment
> no use t-sql??
> --
> Study everyday!
>
>
> “Tibor Karaszi”编写：
>
| |
| lansehai-chen@hotmail.com 2006-04-06, 1:23 pm |
| Sorry,I have another question when i enable trac flag 1400 using the "DBCC
TRACEON (1400,-1) command,but i can't enable it!!I only enable trac flag on
the sql server configuration manager!!Why??
--
Study everyday!
“Tibor Karaszi”编写：
>
> Yes, it does. I prefer to use TSQL commands directly, and it is also much easier to communicate over
> media like this using TSQL commands.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
> news:DA269E87-C3CE-4437-86C0- 65CD0C804FE4@microso
ft.com...
>
>
| |
| Tibor Karaszi 2006-04-06, 1:23 pm |
| I don't know. My guess is that you need to have it as a startup parameter, and not start it using
DBCC TRACEON.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"lansehai-chen@hotmail.com" < lansehaichenhotmailc
om@discussions.microsoft.com> wrote in message
news:71BE2DD8-1D67-4DFA-9DAC- 87A6FA7856AD@microso
ft.com...[color=darkred]
> Sorry,I have another question when i enable trac flag 1400 using the "DBCC
> TRACEON (1400,-1) command,but i can't enable it!!I only enable trac flag on
> the sql server configuration manager!!Why??
> --
> Study everyday!
>
>
> “Tibor Karaszi”编写：
>
|
|
|
|
|