SQL Server Performance Forum – Threads Archive

SQL server cluster. What happens with msdb

There are three nodes: S1, S2, S3. S3 is designated almost for log shipping only(standby server). S1 and S2 are sql 2000 servers and they are in the cluster. They run different db’s at once(active/active) for example s1 run db1 and s2 run db2. Now s1 failover to s2. That means s2 run both dbs now: db1 and db2. S2 makes backups of db2 without problems and they are restored on s3. But what happens with db1 transaction log backups? Will server s2 know that it must do db1 backup job? If so then from where? And the second question. What happens with db1 logins after the failover proccess? haw are they transfered to s2? The info about them resides on the s1 master and msdb databases, not in db1 For any help thanks in advance. I hope you understood me

---

I am not sure if I understand everything, but I try to answer this question the best I can. First, I am assuming that there is only a two-mode cluster, and that the third server is not part of the cluster, and is used only for log shipping. If this is the case, each active instance of SQL Server is independent of each other. For example, if node 1 falls over to node 2, nothing really changes, other than were the instances of SQL Server are running. MSDB works as normal, and standard backups (data and log) occur as normal. There is no change, because each SQL Server instance resides on its own independent shared array. How the third server (log shipping) is used should also not change, assuming that you are using the virtual server names. In regard to your second question, logins are not affected by a failover. Again, each SQL Server instance is separate and runs independently, and after a failover, each instance works just as it did before the failover. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

---

Thank you for your reply. It pushed me into doubts that my knowledge isn’t right about that. So if you have some free time please correct me if I’m wrong about the basic installation of active/active sql cluster OK. Say we don’t pay attention to s3, which as you correctly understood is not a part of a cluster. There are two nodes s1 and s2 and shared disk array with RAID10.
1.I must install sql server named instances on both nodes local disks. Right? This means that system db resides on local disk not on shared disk array
2. Then I create user DBs. One (db1) on s1 and put its .mdf and .ldf files on shared disk array K disk. Second (db2) on s2 and put its files on shared disk array I disk.
3. Then on s1 I install virtual server v1(s1 would be the primary and s2 the secondary).
4. After that on s2 I install second virtual server v2 (s2 would be the primary, s1 the secondary). Now when s1 falls over to s2, s2 server takes over control of db1 and continues to write its data to K disk. Only db1 data not server s1 system dbs changes, because they left on server S1 local C disk, where was installed sql server named instance Please correct me and bring some fresh to my knowledge. Thanks in advance

---

No . Only program executables exist on local disk. All databases exist on a disk in the shared array, including system databases.
As mentioned all databsaes will reside on the shared array.
You can make the installs from any node. The SQL Server 2000 installation will treat your cluster as one single server.
There is nothing on local disk.

---

waw…
so how is it… It means that I have to install both sql server named instances on separate 2 disks on shared disk array(for example K and L)? And do nothing with these two nodes which both run only windows 2003 server? How many virtuals server do I need in that situation? One or two And the last question: when installing virtual server I must point on that disk where sql server named instance reside(for example K)? Am I right? There are so many information on net, so many that it confused me at the end. Be patience please.

---

If you are going to install more than one instance in the cluster then each need it’s own dedicated disks.

Not sure what you mean here.

How many instances to install depends on what you need. If you just need redundancy for your SQL Server one is enough. Then you have it active on one node and have the other node as passive. If you install another instance/virtual server this can either share same node (make sure to limit memory for each instance) or you could put it on the other node. Note that SQL licensing is required for each active node in the cluster. Using more than one instance is often used when you need to seperate environments but want to use the same hardware to save costs.
Each instance is connected to a virtual server name and each instance use it’s own dedicated disks in the shared array. For more information see the following topic on SQL Server Books Online:
Multiple Instances of SQL Server on a Failover Cluster

---

Ok. Thank you for your help. It looks like I’ve got what I needed

---

]]>