SQL Server Mirroring

[melbert2002]

Hello all,
I thought I would ask the question about DB mirroring in SQL server 2005. I get the whole concept of how it is supposed to work, but I do have a question on a couple of things.

1. Where is the endpoint put? Is it in the master db, or is it assigned for each db? I am confused on this issue, because I see references to the instance and just allowing the wizard to create it. Also is there only one endpoint per server instance?

2. Are there any security considerations? Like now I am gettting a 1418 error when I try to impliment mirrroring. Using profiler, I see that I am getting a TCP 10054 error (forced connection closure).

Any help on making mirroring a little easier would be great.

Thanks in advance.

Mel

 

[mrdenny]

The endpoint defination is physically stored in the meta data store that SQL 2005 maintains. End points are unique per instance.

The two machines make a secure connection over the endpoint and encrypt the data using what ever encryption you specify when you setup the endpoint.

What's the text for error 1418?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[melbert2002]

MrDenny,
Here is the text of the error:

TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'cc.number10_staging'. (Microsoft.SqlServer.Smo)

For help, click:

http://go.microsoft.com/fwlink?Prod...ceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://SQL02:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

For help, click:

http://go.microsoft.com/fwlink?Prod...47&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I can ping the server, and port 5022 is allowed, but I still cant see what is going on.

Just so that I am getting you on the end point, there is only one endpoint created for the instance, meaning that one instance could have 100 db's and the one end point would cover all of the db's. So if I were to create an endpoint, I would go into QA adn write it against which db?


Thanks as always.

Mel

 

[melbert2002]

One other thing that I was wondering if you could help me out with. Do you know of any good SQL Server 2005 Books out there that are actually worth.

 

[mrdenny]

Books, not a clue. Hate the things for the most part. Havn't read a single one on SQL 2005 (except Books OnLine).

Correct, endpoints are instance specific.

Do you have an endpoint on SQL02 that is listening on port 5022? Has the endpoint been started?

Once you've got an endpoint and you connect via that endpoint, you can access any database that you login has access to on that instance via that endpoint.

When you run the CREATE ENDPOINT command you can be in any database, I usually run them from master since that's my default database on all my servers.

Sounds like the endpoint either isn't setup or it isn't started.

Query the sys.endpoints system view and see what it tells you.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[timscronin]

as far as books you migh try the sql2005 Bible which comes out next monh, most of this series has been worth it

 

[melbert2002]

Mrdenny,
Thanks again for all your help. I checked the status of the mirror that I created and it says that it is running, and Im still getting the same error.

Thanks

Mel

 

[mrdenny]

Can you connect to port 5022 from the other SQL Server?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[melbert2002]

well I was able to telnet to the system via IP and port 5022. Unless there is another way of connecting that I dont know about? Is there something in 2003 that might be blocking it?

On a side note, I think that Im going to go back to using DTS to pull the data over in the meantime.

Thanks

Mel

 

[mrdenny]

Mirroring isn't a way to get data from one host to another. It's a DR stragity. When you mirror a database the mirrored copy is not readable.

If you are looking for a way to move data from one system to another so that you don't have to query accross systems for it, mirroring isn't what you want. Either moving the data via DTS / SSIS or Replication are the correct methods.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com