October 20, 2009 at 2:35 am
Hi,
I have tested a new database architecture in a dedicated server. It needs to have user-defined-datatypes set in model and in tempdb, 2 logins with public db role in tempdb, with grant on its datatypes. A job scheduled to run when sql agent starts assures that in case of server restart the users in tempdb are remapped to corresponding logins with their grants.
The sistem works fine (I know it is not a suggested configuration, but we are waiting for a new release). In the meantime, I need to migrate on production server, which is an active-active cluster. The question is: in case of switch, do I need to have the configuration of model and tempdb already set on the other instance? And also the job scheduled to run on sql agent start? Should system databases and objects of the two instances be always perfectly identic (even indipendently from this particular need) to assure continuity in case of cluster switch?
Many thanks in advance,
sb.
October 20, 2009 at 3:12 am
First question I have is why are you using user-defined types, what are you using them for? Why in model?
Second of all you need to do some research on MS clustering. It will help you with this.. While there are two seperate installs of SQL on a cluster, they share the same database files and database name. Therefore you don't setup anything in the other databases.. Because there aren't any.. Think of the different servers in the cluster as people and the SQL Server databases as a paperback book.. Only one "person" can have the "book" at a time, only one instance is active at any one time. The cluster manager makes sure of that.
I have dealt with a scenario like you are talking about and it is not only not suggested it is, IMHO, not a good design, and when I *see* bad designs in one area of a package I start to wonder about others..
CEWII
October 20, 2009 at 3:58 am
Our cluster is active-active, so in the same time there are system database running on separate instances.
The reason for datatyped in model is that the application creates temporary tables containing user-defined-datatypes which are not currently managed by the application itself (it has been projected to work in a dedicated server). So anytime a table is created, it has to find the types required and have the grants on them. I agree it is non suggested, I don't think this can cause problems, but the fact to have the datatypes in each new db I will create (in case I will have to remember to drop them), and to have the users in each temp table. Do you think there could be other problems?
With these details, how do you think I should configure system tables and jobs on the other instance?
many thanks,
sb
October 20, 2009 at 4:14 am
In the case of an active-active you have 4 SQL servers, (usually) 1 active on each host. they are entirely seperate SQL servers, you should only go through that sillyness on the database server you need to. you don't need to worry about the inactive node, or the other active node. This is all basic windows clustering.
Common Active/Active Config
Machine 1 contains database server 1 active node, and shared disks for that DB server, it also contains database server 2 in-active node, and shared disks for that DB server, it just doesn't have access to them at the time.
Machine 2 conains database server 2 active node, and shared disks for that DB server, it also contains database server 1 in-active node, and shared disks for that DB server, it just doesn't have access to them at the time.
Database Server 1 has name SQLServer1, regardless of what node it is on.
Database Server 2 has name SQLServer2, regardless of what node it is on.
Database Server 1 is the server with the UDT's. you don't need to care what node it is on because in most ways SQL hides this from you, you configure it the same as you would a non-clustered box.
Database Server 2 is an entirely seperate SQL Server, it has (really) nothing in common with Database Server 1, you don't need to do anything special.
When Database Server 1 fails over to Node 2 the SQL server on for Database Server 1 on Node 1 is shutdown. And at that point node 1 is effectively in-active. SQL Server for Database Server 1 is started on Node 2 and you now have both servers on the same box, but they are still seperate, they don't care about each other.
In this configuration though you need to set aside enough memory on each node to have enough for the case where the other fails over. What I mean by that is that if the machine has 16GB of ran on both Node 1 and Node 2, you can't give each of them 14GB because what happens when you have both on the same machine each wanting 14GB, thrashing is what.. So maybe you give each 8-9
GB, or perhaps 7GB.
You asked about jobs. They exist in msdb, so they go where the rest of the server goes. I would generally recommend against ever referencing the underlying machines if you can help it, which in SQL you usually can help it..
CEWII
October 20, 2009 at 4:23 am
Many, many thanks for your accurate reply!
sb
October 20, 2009 at 4:28 am
You are welcome.
CEWII
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply