Database Mirroring

[pie8ter]

We are implementing database mirroring for high availability. I have few questions.

1) The documentation says that only 10 databases are supported in an instance on a 32bit system. Are they referring to 32 bit windows system or 32 bit SQL server?

2)What's the best way to maintain the master and msdb consistency between the principle and mirror servers as these system databases aren't replicated (or mirrored)?

3)Is there anything similar to heartbeat in database mirroring? If so, can I force both principle and mirror to exchange their heartbeats on a private LAN? We will have two SQL servers in mirror with two NICs on each server. I would like to force the heartbeats on NIC1 and client traffic on NIC2. The NIC1 in both servers will be patched by a crossover cable.

4)For the classic ASP page, ASP.NET page, .NET apps, how the connection string should look like for the failover scenerio? I realize I need to include the Database=primary;partner = mirrorsql;... for ASP pages.

Last but not the least, can someone explain Microsoft's logic behind not mirroring or replicating the system databases? I am guessing some limitations are delibrately left unaddressed so that companies will buy the more expensive SQL cluster systems.

Thanks

 

[mrdenny]

1. When talking about limitations in 32bit systems they are refering to 32bit SQL Server. When looking at SQL docs unless specifically noted the platform will always be the SQL Server's platform not the host OS.

2. Manually deploy the changes (jobs, logins, etc) from one machine to another. Things like logins and jobs shouldn't be changing often, but when they do you need to move them manually.

3. SQL does use a heartbeat of sorts between the mirrors. Forcing the mirror traffic to a secondary NIC is easy. Add a DNS entry for each machine referencing the IP of the cross over NIC and a seperate machine name (SQL01 for the actual host and SQL01-PRIV for the private link). Then when setting up the mirror reference this DNS name for the machine name SQL01-PRIV.company.local instead of SQL01.company.local. (This can be done with the host files instead of DNS, but DNS would be better.)

4. You need to use the SQL 2005 driver for the connection, and use the parter string option as you showed.

System databases contain local system information (hostname, local jobs, security information, etc) which shouldn't be on the remote system, or isn't needed on the other machine. For example, Microsoft has no way of knowing if your sa password is supose to be the same on both machines. Also what if not all databases were mirrored? If you mirrored the master database it would look the same on the remote side, and you'd get all sorts of errors. Not to mention that you wouldn't be able to run the backup SQL server with it's master database in an unreadable state. Same goes for msdb.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[pie8ter]

Thanks Denny!

1) Got it!
2) We have a development department who would like to maintain their database on their own. This would mean the IT department and development department should co-ordinate what goes on the SQL server. It's bit of a politics.

3)If I use the crossover cable on the NIC1 of both machine, only the machine 1 and machine 2 can talk each other on that cable. A DNS server on the LAN wouldn't know about the NIC1. Plus, I am guessing, somewhere in the SQL instance or server, I need to tell which NIC to use for the heartbeat. NIC2 of both servers will be used by the clients.

4) So if I have a classic ASP page referencing a DB in the principle server, do I need to install the SQL 2005 driver on the webserver? Where would I get it?

I understand your point on the system databases. I would like to seee the mirroring of items related to mirrored DBs. For example, if I mirror the database xyz, I want the logins, jobs, etc mirrored for just that xyz database.

 

[mrdenny]

3. The DNS Server doesn't know about any NICs. All it knows is IPs and the names that they go with. The DNS server doesn't need to have direct connection to the NIC. We are need it so that the SQL Servers can resolve the host name SQL01-PRIV.company.local to the private IP. TCP routing will handle sending the traffic to the private NIC.

4. Yes you need to install the SQL 2005 driver in order to use the failover. You can download it from microsoft.com.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[pie8ter]

I have three servers for mirroring. Two are partners and one is a witness.

Partner1 NIC1 and NIC2
Partner2 NIC1 and NIC2
Witness NIC1 and NIC2

I have the NIC1 from each partner and Witness connected to the network (domain). Clients will use NIC1 to connect to the dabase on either principle or failover.

I want the NIC2 of each server connected to their own private network say 192.168.1.0/24. Can I create the mirroring endpoints using these private network IPs?

I am using a domain account as the SQL service account for all the instances (principle, failover and witness). If the mirroring endpoints use a private network, how can they authenticate the domain account?

Thanks

 

[mrdenny]

Yes you can do this.

When the request for authentication comes into the SQL Server, the host will go to talk to the domain controller. When it sends out the request to the TCP/IP stack for the connection to the domain controller the TCP/IP stack will look at the local routing table to decide which NIC to use. The routing table will tell TCP/IP that the NIC2 network doesn't have access to the IP that the domain controller is hosted on and will use NIC1 because the routing table will direct it there.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[pie8ter]

So.. let say this is my config:

principle1 NIC1(10.0.1.10) and NIC2(192.168.1.10)
failover1 NIC1(10.0.1.11) and NIC2(192.168.1.11)
witness1 NIC1(10.0.1.12) and NIC2(192.168.1.12)

The 10.0.1.0/24 network has the domain controllers and all the clients. The 192.168.1.0/24 network is detached and seperate from the 10.0.1.x network.

Let's say I have a host file in all three servers with the following maps:

192.168.1.10 principle-pri
192.168.1.11 failover-pri
192.168.1.12 witness-pri

The DNS server in the 10.0.1.x network where the domain controllers are has the following mapping:

10.0.1.10 principle1
10.0.1.11 failover1
10.0.1.12 witness1

Now I am logging into principle1 server and creating the endpoint for that instance. Also, I am creating mirroring sessions for "Sales" databse and configuring the witness:

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

ALTER DATABASE Sales
SET PARTNER =
'TCP://failover-pri:7022'
GO

ALTER DATABASE Sales
SET WITNESS =
'TCP://witness-pri:7022'
GO

Now I am logging into failover1 server and creating the endpoint for that instance. Also, I am creating mirroring sessions for "Sales" databse:

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

ALTER DATABASE Sales
SET PARTNER =
'TCP://principle-pri:7022'
GO

Now I am logging into witness1 server and creating the endpoint for that instance.

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO

Now when all three servers communicate in the 192.168.1.x private network, they authenticate the endpoints using the domain\sqlserviceacct ? Am I correct?

If that's the case, then 192.168.1.x network can't see the domain controller.

Do I need to tell the failover1 where the witness is? like

ALTER DATABASE Sales
SET WITNESS=
'TCP://witness-pri:7022'


Sorry for the long posting.Thanks for all your help mrdenny

 

[mrdenny]

Correct all authentication happens via the domain\sqlserviceacct.

Correct the 192.168.1.0 network has no direct access to the domain controllers.

I beleive that you do need to tell both servers about the witness.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com