[sims] SIMS & Discover on SQL 2008R2 Cluster Not Supported by Capita?

[Taunton81]

Hi All

 

We have recently moved our 2 SIMS Databases over to a 2008R2 SQL Cluster and they are working fine.

 

However... We can't get Discover to work, it keeps coming up with 'There has been an issue loading the catalog. This has been logged. Would you like to try and load the catalog again?'

 

I logged a case with Capita, however they closed the case as they don't support SQL Clusters!!

 

Anyone else know how to fix the problem and also had issues with Capita not support cluster environments?

 

Thanks!

 

Tom

[synaesthesia]

seriously? industry standard for SQL installs is on dedicated clusters; in fact from SQL 2008 upwards that's Microsoft's preferred method. I hope you're just been given some false info from someone new!

[Taunton81]

Originally the person I spoke to said that they would support it however I got the following response back:

 

Just been to development and they have confirmed that I have miss informed you. We do not support SQL server in cluster mode. It has been known to work for some schools but officially it is not supported. I apologise for the mis-direction

 

:-(

[synaesthesia]

/me head hits desk.

These people are supposed to be at the forefront, yet in reality lagging well behind industry standard. Main interest is that with the increase of use of SQL, I've been considering a similar (small scale) setup to help drive SQL dependant software such as SIMS/FMS/Discover and SCCM. This may well have some impact on that.

[PhilNeal]

I will check out the response you got but I think what the desk is saying is they don't have the knowledge to support complex installations.

 

There is nothing about the design of Discover that would prevent it running in this environment in fact I'm sure many LAs have done this. I've spoken to one of the developers and this is his answer:

 

Given they've "recently moved it over" my suspicion is that they have not granted rights to the Discover database within the new servers. This can be fixed by running the command line tools that deploy with Discover. It can also be verified by a quick remote desktop session onto their server.

 

I don't understand a word of this!

[Taunton81]

I wouldn't go as far to say that a SQL Cluster is a complex installation considering it is a common standard today. The initial setup of the cluster maybe complex however from a support point of view it appears as a single instance and the support desk should be in the position to deal with that.

 

2nd line support is now connected to our server (and have been for 2hrs 30mins!) trying to rectify the fault.

 

However I have been told again that if they can't fix it they WON'T escalate it to the developers as it is an unsupported platform!

Edited February 22, 2013 by NortyUK

[Taunton81]

OK, Well after 3.5hrs on a remote support session and re-installing the database and services 3 times it is now working.

 

It turns out that SOLUS 3 has issues deploying a correct config to the SQL Cluster, so we had to use the ISO file to do a manual install.

 

Why does Capita insist on using their own software distribution methods and not utilising the inbuilt Windows methods? Why do they feel the need to re-invent the wheel? I know at the Capita South West User Group Meeting a year ago, people were complaining about the reliability of SOLUS 3 and why can't they use standard MSI files.

 

SOLUS 3 has caused us hours, if not days or troubleshooting as it just doesn't work half the time. The way it sends out updates to people using terminal services is appalling. See the following post: http://www.edugeek.net/forums/mis-systems/87832-solus-agent-flashing.html - I spoke to Capita about the issue posted over a year ago now and still the problem exists!

[PhilNeal]

I'm glad we've been able to resolve the issue and that Discover did not need adjustment to work on the cluster arrangement. The arguments on why we have produced SOLUS3 have been well rehearsed on this site and I can't add anything more than Matt40K has done. I accept SOLUS3 has not been as easy as it should have been and we're working on improving it.

 

There is always an ISO version available if you want to do the install directly.

[matt40k]

seriously? industry standard for SQL installs is on dedicated clusters; in fact from SQL 2008 upwards that's Microsoft's preferred method.

 

Seriously? You're telling me Microsoft wants every secondary school to have a MS-SQL cluster? This wasn't someone in the sales team was it who's working on commission? Can't personally justify the cost - plus the extra support if it goes wrong - and there is more to go wrong. Having said that the read-only node idea (AlwaysOn??) in SQL 2012 sounds pretty cool.

[Taunton81]

The main issue I have is that you HAVE to use tools such as DBAttach to move databases between servers etc. These simply don't work well with SQL Clusters. I shouldn't have to go installing 'database tools' into the BINN folder on a SQL Server in order for the database to work correctly.

 

We migrated 15 databases from 2 standalone 2 SQL Servers to a single highly available SQL Cluster. The only databases that we had issues with were SIMS and Discover because you have to install and use dbattach in order to move or create the database.

 

Why can't you simply just attach the database using the SQL tools provided by Microsoft, and if you need to create SQL logons etc, run an SQL Script?

[vikpaw]

Why can't you simply just attach the database using the SQL tools provided by Microsoft, and if you need to create SQL logons etc, run an SQL Script?

 

You can i thought. unless dbattach does some other magic.

There is a stored procedures for importing the logins into the db, which you can then backup and restore elsewhere, and run another proc to export logins out again.

[matt40k]

Your right vikpaw - you can, we do, others do (mostly LA), OK, you still need to install the SQL apps rubbish on each node, but you don't need to use dbattach.

 

You should always create logins via the application - it has extra security, auditing etc. For example, in SIMS when you create a teachers a SIMS account, you link it to the personnel record.

[Taunton81]

You can i thought. unless dbattach does some other magic.

There is a stored procedures for importing the logins into the db, which you can then backup and restore elsewhere, and run another proc to export logins out again.

 

If found that if you just detached, moved and reattached the database you couldn't logon as it didn't create the users.

 

Is there some documentation on SupportNet I am missing with regards to store procedures?

 

The Capita Support Desk say that you have to use dbattach as it is the only supported method.

[matt40k]

Add to your maintenance plan - add exec sims.db_p_transfer_login - so it transfers the logins from the MASTER database to SIMS database

Then after you restore - run exec sims.db_p_install - transfers the logins back and does the SIMS "magic"

If you get passed first line support @ Capita, they'll be able to tell you this.

 

Other (non-SIMS) are to using something like SSIS to transfer the logins.

[vikpaw]

If found that if you just detached, moved and reattached the database you couldn't logon as it didn't create the users.

 

Is there some documentation on SupportNet I am missing with regards to store procedures?

 

The Capita Support Desk say that you have to use dbattach as it is the only supported method.

 

i like to think of @matt40k as the missing parts of supportnet documentation.

[Taunton81]

Add to your maintenance plan - add exec sims.db_p_transfer_login - so it transfers the logins from the MASTER database to SIMS database

Then after you restore - run exec sims.db_p_install - transfers the logins back and does the SIMS "magic"

If you get passed first line support @ Capita, they'll be able to tell you this.

 

Other (non-SIMS) are to using something like SSIS to transfer the logins.

 

Thanks for the info!

[PhilNeal]

Sorry to resurrect this one but we've been through all of our cases to locate the advice that you've been given that we "won't support Discover on a cluster" but can't find it. It would be really helpful to have a case reference so that we can speak to the support staff.

 

Here's what I have got back from our desk:

 

The team have all confirmed that they would support SIMS, Discover etc. on clustered solutions as they do that everyday.

 

With regard to not referring a cluster problem to a BA. This is the response:

SQL Server clustering is transparent to the application, i.e. applications should not be aware they are running against a cluster, i.e. if it works against a SQL Server, it works against a SQL Server cluster.

 

We don’t test Discover against SQL clusters, again because it should be transparent to the application, so we shouldn't have to.

[TheScarfedOne]

I was gonna jump in on this...but you've answered! I've been running Sims and Discover on an SQL 2008r2 cluster for the past 12 months without issue so can confirm what @PhilNeal says.

[CAM]

Will that affect the master database in any way?

[vikpaw]

It shouldn't it's all transparent

[CAM]

Sorry didn't spot page 2 when I posted and caused a bit of confusion. I meant the exporting users from the master DB to SIMS DB that Matt was talking about.

[vikpaw]

It doesn't do anything to the master. it just copies the info you need out, then back into the destination system. Using the db as a transport mechanism.

[vikpaw]

not really sure how it all works. but if you look at your sims db and expand the security / users you'll find the logins. these are also in master.sys.server_principals view and probably other places too.

[vikpaw]

probably worth looking at those stored procs to get a better idea.

[Taunton81]

Sorry to resurrect this one but we've been through all of our cases to locate the advice that you've been given that we "won't support Discover on a cluster" but can't find it. It would be really helpful to have a case reference so that we can speak to the support staff.

 

Here's what I have got back from our desk:

 

The team have all confirmed that they would support SIMS, Discover etc. on clustered solutions as they do that everyday.

 

With regard to not referring a cluster problem to a BA. This is the response:

SQL Server clustering is transparent to the application, i.e. applications should not be aware they are running against a cluster, i.e. if it works against a SQL Server, it works against a SQL Server cluster.

 

We don’t test Discover against SQL clusters, again because it should be transparent to the application, so we shouldn't have to.

 

I am glad that you are now supporting SQL Clusters, however you may want to make your support desk aware of that! Here are two emails in which I was told that you were not supporting it:

 

Hi Tom,

 

Just been to development and they have confirmed that I have miss informed you. We do not support SQL server in cluster mode. It has been known to work for some schools but officially it is not supported. I apologise for the mis-direction

 

Many Thanks,

 

Second Tier IT Technical Engineer

Chelmsford Service Desk

 

Second E-mail:

 

Hi Tom,

 

As I have said before SQL server 2008 R2 in cluster mode is not supported. However, I am willing to remote into the environment and do as much as I can. Be advised that, if the issue is unresolved I am unable to escalate the issue to development simply because of the unsupported product. I will contact you at 12:45pm.

 

 

Many Thanks,

 

Second Tier IT Technical Engineer

Chelmsford Service Desk

 

I have removed the name of the Capita representative and case number, however I can forward all the details on to you Phil if you would like.

 

Thanks

 

Tom

Edited March 5, 2013 by NortyUK