April 17, 2012 at 6:02 am
I'm wondering what needs to be done to make a safe sql server installation where data will be stored on an iscsi target server. This is a test environment setup by our systems team and I have already encountered data corruption ( revealed with dbcc checkalloc ).
I'm not sure yet if it was introduced copying the data to this server or later but I didn't get any errors when I attached this copied database.
I know using separate storage is common and we use a netapp on our production sql cluster. But using a windows box and the Microsoft virtual disk arrangement may not be as robust and safe. First I have to determine if the service dependencies were set so sql can't start up unless the virtual disks are available. What happens if the virtual disks become unavailable, even momentarily, due to network or other issues?
When I look on the sql server I don't see the sql server service has any dependency other than the sql agent. The virtual disk service is manual startup and doesn't show any dependencies at all.
Once you have connected to your storage using the initiator (being sure to set the volume up as persistent and bound as this will ensure the storage is persistent). You will also want to set the proper service dependencies, that is, on a re-boot prevent the SQL Server service from spinning up until the iSCSI initiator service has started and reconnected to all storage volumes.
April 17, 2012 at 6:53 am
You'll definitely need to make sure SQL Server doesn't start up till the drives are available for the system databases and user databases, at the very least. Otherwise, you'll get errors at best and hidden failures at worst.
As for corruption in the files, iSCSI is dependent usually on standard-grade network hardware, and all it takes is a router/switch that's silently dropping packets to corrupt files. That's why fibre-channel is prefered (and more expensive) by enterprise-grade solutions. So, I'd check for a component along the network path that's dropping or malforming packets.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2012 at 6:56 am
When configured correctly it works well. Can you supply more detail on the set up you have, mutli path, etc?
For an idea of getting things setup see my popular guide starting at this[/url] link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 17, 2012 at 9:46 am
I don't know how its setup but when I look in task manager on the sql server I see:
Fabric A1 Port 2 160 Subnet
Fabric B1 Port 2 ISCSI
The Microsoft iSCSI Initiator Service shows no dependencies at all
The sql service shows the sql server agent as dependent on the sql service, nothing more.
April 19, 2012 at 6:42 am
The services won't list themselves as being dependent. You have to tell them they are. SQL Server assumes that the server hardware will have drives attached by the time Windows starts calling services to start themselves. Normally, with attached or dedicated external storage (usual SAN over fibre optics), that's the behavior. It defaults to the usual. Special setups require special handling.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 19, 2012 at 6:46 am
Indianrock (4/17/2012)
The Microsoft iSCSI Initiator Service shows no dependencies at allThe sql service shows the sql server agent as dependent on the sql service, nothing more.
is this a sql server cluster?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 19, 2012 at 7:18 am
no not a cluster
April 19, 2012 at 7:31 am
in that case SQL Server will not have any dependency on the disks as it does in a clustered environment.
If SQL Server tries to start and the disks are not ready the service will fail to start, the system databases will not be started. All this occurs before any user databases are started.
As GSquared as stated and as i have already mentioned, proper configuration is vital. A misbehaving switch, etc could be causing issues. Need to know more about your setup to go further really.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 19, 2012 at 8:35 am
but the system databases are on the c drive. I finally confirmed that the iscsi drives are on a separate server. Its possible the corruption was introduced in copying the database to this server, but was just curious about the reliability of this arrangement in a non-clustered environment where user database files are on a separate server and sql system databases are local.
April 20, 2012 at 6:30 am
Reliability will depend on your iSCSI set-up. It can be very reliable, or it can be a freaking nightmare, all depending on how it's set up and who's in charge of keeping it going.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply