sql error 15138: wizard

Showing posts with label wizard. Show all posts

Thursday, March 29, 2012

Error 18483 while configuring distributor

I want to setup a publisher, distributor and subscriber on the same machine.
While configuring my distributor using Wizard, I received the following
error:
Error 18483: Could not connect to server 'HONB" because "distributor_admin"
is not defined as a remote login at the server.
I am using SQL Server Developer version and "HONB" is my computer name. Can
someone tell what I have mis-configed?
This is usually a naming issue.
Try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'Server1'
GO
Use Master
go
Sp_Addserver 'Server1', 'local'
GO
Stop and Start SQL Services
If this is not the issue, but you are working across non-trusted doamins
there is the same error, so you might want to have a look at his article:
http://support.microsoft.com/?id=321822.
Regards,
Paul Ibison
|||I tried to run the sql, the servername is ok.
I read articale, but it seems not related to my problem, since I want to
setup the replication on a single machine.
Is it related to sql server agent? I am using system account as the startup
account in Sql server agent.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> bl
news:Oocp%23YwKEHA.4052@.TK2MSFTNGP11.phx.gbl g...
> This is usually a naming issue.
> Try:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL then try:
> Use Master
> go
> Sp_DropServer 'Server1'
> GO
> Use Master
> go
> Sp_Addserver 'Server1', 'local'
> GO
> Stop and Start SQL Services
> If this is not the issue, but you are working across non-trusted doamins
> there is the same error, so you might want to have a look at his article:
> http://support.microsoft.com/?id=321822.
> Regards,
> Paul Ibison
>
|||The system account is OK if it is all done on the same box.
You might be able to use sp_remoteoption and sp_addremotelogin to fix the
issue.
HTH,
Paul Ibison
|||can you remove replication, and then re-install it.
"gh" <ghghghgh@.FSAF.COM> wrote in message
news:uinEcarKEHA.1388@.TK2MSFTNGP09.phx.gbl...
> I want to setup a publisher, distributor and subscriber on the same
machine.
> While configuring my distributor using Wizard, I received the following
> error:
> Error 18483: Could not connect to server 'HONB" because
"distributor_admin"
> is not defined as a remote login at the server.
> I am using SQL Server Developer version and "HONB" is my computer name.
Can
> someone tell what I have mis-configed?
>
>
>

Error 18483 'distributor_admin'

I'm trying to publish a database and when I get to the end of the wizard, I
am greeted by this message:
SQL Server Enterprise Manager could not configure 'SERVERNAME' as the
Distributor for 'SERVERNAME'.
Error 18483: Could not connect to server 'SERVERNAME' because
'distributor_admin' is not defined as a remote login at the server.
The MS articles did not help me. Can someone, please?
Wack
This message will self destruct.
try to drop the server and then recreate it
sp_dropserver @.@.servername
GO
sp_addserver @.@.servername, 'local'
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ray Lewis" <dav5489@.hotmail.com> wrote in message
news:uO9gjUYJFHA.4060@.TK2MSFTNGP14.phx.gbl...
> I'm trying to publish a database and when I get to the end of the wizard,
I
> am greeted by this message:
> SQL Server Enterprise Manager could not configure 'SERVERNAME' as the
> Distributor for 'SERVERNAME'.
> Error 18483: Could not connect to server 'SERVERNAME' because
> 'distributor_admin' is not defined as a remote login at the server.
> The MS articles did not help me. Can someone, please?
> --
> Wack
> --
> This message will self destruct.
>
|||I tried this, and used MS article 818334. I found that the SQL Server Name
was not the same as the Computer Name. However, when I try and drop the SQL
server name, I get a message stating that the SQL server does not exist.
So, I cannot rename my SQL Server 2000 server. What now?
Wack
This message will self destruct.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uZUqNwaJFHA.1392@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> try to drop the server and then recreate it
> sp_dropserver @.@.servername
> GO
> sp_addserver @.@.servername, 'local'
> GO
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ray Lewis" <dav5489@.hotmail.com> wrote in message
> news:uO9gjUYJFHA.4060@.TK2MSFTNGP14.phx.gbl...
wizard,
> I
>
|||Hi,
is the login used to configure the publication a member of the 'system administrators'? I had a similar problem when creating a pull subscription and this solved it!
Bruno

Quote:

Originally posted by Ray Lewis
I'm trying to publish a database and when I get to the end of the wizard, I
am greeted by this message:
SQL Server Enterprise Manager could not configure 'SERVERNAME' as the
Distributor for 'SERVERNAME'.
Error 18483: Could not connect to server 'SERVERNAME' because
'distributor_admin' is not defined as a remote login at the server.
The MS articles did not help me. Can someone, please?
Wack
This message will self destruct.

sql

Error 18456: Login failed for user 'Distributor_admin'

Hi,
When going through the replication wizard to setup transactional replication
the following error is returned when it starts to setup replication.
SQL Server Enterprise Manager could not configure '<four part servername>'
as the distributor for '<four part servername>'
Error 18456: Login failed for user 'Distributor_admin'
Can someone please help. Is it because of the four part server name?
Thanks
It could be. What happens when you create an alias using the client network
utility. After doing this de-register your server in EM, and then reregister
it with the new one part alias.
Is the distributor local?
"mm" <postto@.news.com> wrote in message
news:eIM2gdb$EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Hi,
> When going through the replication wizard to setup transactional
> replication
> the following error is returned when it starts to setup replication.
> SQL Server Enterprise Manager could not configure '<four part servername>'
> as the distributor for '<four part servername>'
> Error 18456: Login failed for user 'Distributor_admin'
>
> Can someone please help. Is it because of the four part server name?
> Thanks
>
>
|||Hi Hilary,
There is no alisa defined in the the client network utility.
This happens when trying to setup a local distributor. However there is a
database on the SQL server that subscribes to a remote distributor.
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eiLeJZi$EHA.3472@.TK2MSFTNGP14.phx.gbl...
> It could be. What happens when you create an alias using the client
network
> utility. After doing this de-register your server in EM, and then
reregister[vbcol=seagreen]
> it with the new one part alias.
> Is the distributor local?
>
> "mm" <postto@.news.com> wrote in message
> news:eIM2gdb$EHA.2196@.TK2MSFTNGP14.phx.gbl...
servername>'
>

Thursday, March 22, 2012

Error 15457

I recently set up a maintenance plan using the MS SQL
Maintenance plan wizard. Every thing seems to be working
fine with one exception I keep getting the following error:
"Error 15457, Severity 0, Configuration option '%ls'
changed from %ld to %ld. Run the RECONFIGURE statement to
install"
Does anyone know how to solver this?
It's not a "real" error, since the severity is 0. It's safe to ignore it.
It's just a PITA.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"john Schuster" <schuster@.llnl.gov> wrote in message
news:2277b01c45d4f$190760d0$a101280a@.phx.gbl...
I recently set up a maintenance plan using the MS SQL
Maintenance plan wizard. Every thing seems to be working
fine with one exception I keep getting the following error:
"Error 15457, Severity 0, Configuration option '%ls'
changed from %ld to %ld. Run the RECONFIGURE statement to
install"
Does anyone know how to solver this?
|||It's not a "real" error, since the severity is 0. It's safe to ignore it.
It's just a PITA.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"john Schuster" <schuster@.llnl.gov> wrote in message
news:2277b01c45d4f$190760d0$a101280a@.phx.gbl...
I recently set up a maintenance plan using the MS SQL
Maintenance plan wizard. Every thing seems to be working
fine with one exception I keep getting the following error:
"Error 15457, Severity 0, Configuration option '%ls'
changed from %ld to %ld. Run the RECONFIGURE statement to
install"
Does anyone know how to solver this?

Error 15123

When trying to use the generate SQL Wizard, I get an error

Error 15123: The configuration option 'user options' does not exist, or it may be an advanced option. Valid configuration options are:

This just started happening this week. Does anyone have any ideas?

That error normally pops up when you're trying to use the xp_cmdshell function, but it doesn't seem like you're doing that. Can you think of anything that may have changed just before it quit working?|||To my knowledge there have been no changes to that server for awhile. It is a development server and has more hands in it than Carter has pills. I was hoping for a "oh, you shot yourself in the foot this way". We did have an event where we were keeping too many backups and filled the data disk and caused some hiccops in another area. Is there a possibility that something got corrupted? I know the values are in the tables, I traced that back and compared with this server's test counterpart.|||To my knowledge there have been no changes to that server for awhile. It is a development server and has more hands in it than Carter has pills. I was hoping for a "oh, you shot yourself in the foot this way". We did have an event where we were keeping too many backups and filled the data disk and caused some hiccops in another area. Is there a possibility that something got corrupted? I know the values are in the tables, I traced that back and compared with this server's test counterpart.
|||It's possible. Sometimes I try to move the error around or force it, so one strategy might be to back up the DB and restore it on the same or another server. Alternately, you could use the database copy wizard to copy to a new DB and see if the problem forces itself out somewhere.|||The error happens on all databases, so I did detach one of the dbs and copy it to another server. The problem did not follow the db. I figure, we get through this one project and then rebuild the box.|||

Wow - very odd. I wonder if it's a file-locking issue at the OS. In any caes, I'm glad that worked for you. Let me know if I can help in any way.

Wednesday, March 21, 2012

Error 14258 while creating a publication

Hi all,
I am trying to create a merge-publication (SQL 2000 SP4).
Unfortunately, it is not working because the wizard pops up with error
14258: Cannot perform this operation while SQL Server Agent is
starting. Try again later.
Checked both services, they seem correct. Both are using (the same)
domain-user with sufficient privs to access the SQLServer box. They
both run fine (all in the green) and there are no errors in the logs.
SQL itself runs fine and I can access all data from any computer in the
network.
Just to be sure, I've stopped and restarted both services but the same
error comes back again. I've checked all settings but as far as I can
see, the box is configured properly.
Any ideas?
Thx,
FS
Check out this MS article:
http://support.microsoft.com/?id=303287
John Steen
"Ferry" wrote:

> Hi all,
> I am trying to create a merge-publication (SQL 2000 SP4).
> Unfortunately, it is not working because the wizard pops up with error
> 14258: Cannot perform this operation while SQL Server Agent is
> starting. Try again later.
> Checked both services, they seem correct. Both are using (the same)
> domain-user with sufficient privs to access the SQLServer box. They
> both run fine (all in the green) and there are no errors in the logs.
> SQL itself runs fine and I can access all data from any computer in the
> network.
> Just to be sure, I've stopped and restarted both services but the same
> error comes back again. I've checked all settings but as far as I can
> see, the box is configured properly.
> Any ideas?
> Thx,
> FS
>
>
|||Hmm, I guess I was too fast... Solved the problem already by googling a
bit further. As a thank you for reading I will share the solution :-)
When having MSDTC switched on _and_ using NT-fibres in the SQL
configuration, you can encounter this error. Switching the NT-fibres
off (and stopping and starting the services again) resolved the
problem. MSKB article, aaaaargh, how could I missed it in the first
place...
From Ferry :
> Hi all,
> I am trying to create a merge-publication (SQL 2000 SP4). Unfortunately, it
> is not working because the wizard pops up with error 14258: Cannot perform
> this operation while SQL Server Agent is starting. Try again later.
>

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

Error: 1474, Severity: 16, State: 1

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

b) On mirror server:

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.'.

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

What is the account that SQL server is running as?|||The account that SQL runs as is the one that it tries to login onto the other server as.|||Same issue here in our environment. Two database servers, one domain, one user, full permissions... Yet the connection is forcably terminated under an anonymous user.

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

Message
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.16.6.14]

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

I've tried the wizard, I've tried TSQL, I've done all the troubleshooting steps you recommend. Can you please be very specific on what to do and what to put as logins when both principal and mirror run as each machines local service account.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

Could you send me the documentation on how to do this (create certificates), I have read a few but having problems on the mirror node.|||i too would like to create certificates i have the same problem|||

I met the same problem.

This issue is due to the wrong login authority.when we excute the TSQL "ALTER DATABASE WorkDataBase SET PARTNER ='TCP://URL:7022'",this operating lead the SQL Server send a request to the mirror point with NT AUTHORITY\ANONYMOUS enven if the SQL Server service is running as the same domain user as the mirror point does.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

IMPORTANT! To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machinename.domain.com.Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)

FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN) ensure your 3 mssqlservices are running under NETWORK SERVICE account

|||Hi all,

I'd like to add something to this however it's beyond the scope of a basic mirror. When adding a Witness to your mirror to allow for automatic failover you may experience the error on the Principle and Mirror stating

Database Mirroring login attempt by user 'Domain\SQLExpressMachineName$.' failed with error: 'Connection handshake failed. The login 'Domain\SQLExpressMachineName$.' does not have CONNECT permission on the endpoint.

This is deceiving as the Mirroring Wizard creates endpoints on all 3 hosts. Basically the error above is stating that the Witness server does now have access to the Master database endpoints on the Principle and Mirror, therefore you'll need to add the host name of the Witness Server running SQL Server Express. This is done using the GRANT tsql command

master
go
create login [Domain/SQLExpressMachineName$]
go
grant connect on endpoint::mirroring to [Domain/SQLExpressMachineName$]

I had to do this even though all SQL Services were running as a network service.

Cheers,
Damian
sql

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

Error: 1474, Severity: 16, State: 1

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

Error: 1474, Severity: 16, State: 1

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

Error: 1474, Severity: 16, State: 1

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

Error: 1474, Severity: 16, State: 1

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

Error: 1474, Severity: 16, State: 1

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian
sql

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

Error: 1474, Severity: 16, State: 1

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

Error: 1474, Severity: 16, State: 1

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Thursday, March 29, 2012

Error 18483 while configuring distributor

Error 18483 'distributor_admin'

Error 18456: Login failed for user 'Distributor_admin'

Thursday, March 22, 2012

Error 15457

Error 15457

Error 15457

Error 15123

Wednesday, March 21, 2012

Error 14258 while creating a publication

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

sql error 15138

Blog Archive

About Me