November 10, 2003 at 2:39 pm
Dear Friends,
This is new environment to me. Therefore, I would appreciate your comments and suggestions. Notice that what I want to do has nothing to do wit clustering.
This is the scenario. I have two SQL servers 2000 (standard edition) connected to a shared storage. I moved all the database files to the shared drive.
To my surprise, I noticed that I can attach those files to each database server and perform simultaneous operations. Do I have to do anything special to assure the consistency of operations and the
resulting data? Does SQL server takes care of acccess control, therefore serializing conflicting accesses?
Still in a SAN environment, but a different problem: I also have a service running in each server monitoring the "health" of each server. In the case of failure, the service brings down the "primary" server and elects the backup as the good server. In the case of shared files, do I have to do anything special to switch from one server to the
other? What if I have more than 2 servers?
Thank you
CD
November 10, 2003 at 6:06 pm
Performing simo operations is bad, didnt think you could do it. SQL uses the "shared nothing" method, which means that only one instance of the SQL.exe should be able to open and read/write from the file at once. If you're running a cluster against a SAN you map the drive on both machines, then the cluster keeps track of who "owns" the drive.
Andy
November 11, 2003 at 3:31 pm
I think this may be related to your SAN not respecting the semaphores. Can you hole two open files from WORD or notepad or somthing on htis drive?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 12, 2003 at 3:13 pm
Hello Andy,
I am not using a cluster against my SAN. All my database files, including the master, model, and user database are located in the SAN. I have the same shared drive mapped to each server and, to my surprise, each SQL server could refer to the same set of database files. I could actually modify some data using Quary Analyzer in one server and see the updated data in the other server.
It is not my intention to allow both servers to modify data at the same time but I would like to have both servers "ready to go" but only one of the servers would be activelly transacting with the database. Do you see any problem with this scenario?
What concerns me a little is that I have my servers set up to perform some automatic operations , such as autoshrink and reindexing.
Any comments are greatly appreciated.
- CD
November 12, 2003 at 3:15 pm
Dear Steve,
With my SAN, I can open and modify the same file concurrently. I think this is what might be tricking SQL Server.
Do you think it would be an issue to have both servers running but just one of them to be activelly transacting with the databases?
Thanks
- CD
November 13, 2003 at 9:45 am
For a failover you definitely want both servers running.
Andy
November 14, 2003 at 4:24 pm
Thank you for your reply. In my setup, I do not intend to have both
servers activelly transacting with the database. I want both servers
to attach the same database files and be ready to go but just one of
them will be activelly transacting with the databases at a time. I
have a service running in each server that monitors the "health" of
each server and switches control automatically.
Nevertheles, my only concern with this setup are the transactions that
SQL server performs implicicly, such as autoshrink of logs,
reindexing, and some other background actions that I am not aware of.
Do you think those actions can result in corruption?
If such actions result in corruption, I can think of a different setup
where the sqlserver services run in only one of the servers at a time.
If my "health" monitoring service detects a failure, then it starts
the sqlserver services in the other server only after it stops
services in the first server. What do you think of this scenario?
Any comments are greatly appreciated.
- CD
November 19, 2003 at 1:46 pm
I would be very nervious doing this. If you really have two different SQL Servers accessing the same SAN drive and using the same master, etc. databases, the server names will be different (in master), etc.
Clustering gets around this by using a cluster disk locker (cludisk.sys) to control which node owns the disk). The SQL Server can be failed over between the two physical nodes since it is defined (and named) as a virtual server, with a different name then the physical nodes.
I don't see how two different physical servers could utilize the same master database, because they have different server names (@@Servername). It's kind of like changing the Physical server name out from under SQL Server.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply