Failed to create a database. An exception of type System.Data.SqlClient.SqlException was thrown. Additional exception information: Could not find stored procedure ‘sp_dboption’. – Error Occurred when Installing SharePoint 2010 in SQL 2012.

got a new server for my Test environment and started creating a lab of SharePoint 2010 in it with SQL Server 2012 R2. everything went smoothly until I start Configuration Wizard. It came to the 3rd Level and Error Occurred suddenly saying – Failed to create a database. An exception of type System.Data.SqlClient.SqlException was thrown. Additional exception information: Could not find stored procedure ‘sp_dboption’.

image

After thinking a while realized that my setup was just SharePoint 2010 RTM which is None SP. since my lab is brand new thought of trying on installing SP1. just downloaded and installed the SP1 for SharePoint 2010 and started the wizard again.

Everything Gone perfect after SP1. further when reading understood that it’s must to have SP1/above for SharePoint to install in SQL 2012.

Download SharePoint SP1 here – http://support.microsoft.com/kb/2460045

All SharePoint Updates are here – http://technet.microsoft.com/en-us/sharepoint/ff800847.aspx

Upgrade SQL Server Edition–SQL Server 2008 R2

There will be some incidents that you need to upgrade Existing SQL Edition to another Such as Standard to Enterprise. in my scenario I had a SharePoint Foundation Farm Installed on a SQL Express 2008 R2 and I needed to upgrade it in to Enterprise due to huge growth of Databases. finally it became crucial while giving me an error when restoring a Database of 50 GB as SQL Express 2008 R2 allowed only 10GB Max and that’s where this upgradement decided.

nothing much to do as you have got the feasibility for this on SQL installer. just take below few steps to complete you Edition upgrade.

1. Run SQL Server Installer Setup from your media.

image

2. Direct to – Maintenance –> hit on Edition Upgrade

image

3. Place License Key and click next.

Capture

4. Agree to the License Agreements here

image

5. Select the instance which you need to upgrade as below and click next.

image

6. Make sure on everything by looking at the summary and hit Next to proceed with upgrading.

image

7. Give it a moment and you will see the below result as success shortly.

image

that’s it and your instance is upgraded for selected edition and there will be no harm to Databases or any settings you have done in the instance. Enjoy !

Error When Creating new Database on Clustered SQL Environment

Recently I got an Clustered SQL 2012 environment created with two nodes as I needed to deploy SharePoint 2013 on it. SharePoint Installation gone well but I got below error when I’m tying to create a new web application.

“Cannot use file ‘D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\WSS_Content_7777.mdf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the SQL Server does not have a dependency on it. CREATE DATABASE failed. Some file names listed could not be created. Check related errors.”

tried though SQL Management Studio to create a new DB in order to verify and it gives the same error. so it should be from SQL cluster side for sure.

image

 

 

 

 

anyway, we love these kind of little damns because always we learns something though them. let’s find out what it is.

when I look at the error it was clearly mentioning something about Dependency. so yes obviously my cluster is brand new. didn’t fine tune well because was bit hurry on deployment of SharePoint.

So it’s all about Configuring Dependency from Windows cluster.

  1. SQL Server depends on the Network Name and a Disk Resource.
  2. SQL Network Name depends on the IP Address.
  3. SQL Agent depends on SQL Server.
  4. The IP Address and the Disk Resource do not depend on anything.

generally SQL prevents the creation of Databases on volumes which are not Dependency of SQL Server resources. So The point of having dependencies is to prevent situations where SQL Server comes online before the disk containing the data goes on line. Therefore the term “DEPENDANCY.”

Let’s see how we can add the dependencies to get this fixed. by the way you don’t have to do this once you got a new clustered environment created as The dependencies are automatically established when you create the SQL cluster. but if you substitute any disk resources don’t wait till the error remind you for it, you have to add the dependencies.here in my case it was the point, I added some disk resources and I forgot to re-add the dependencies Winking smile.

Open Failover Cluster Manager –> Select your clustered application (SQL) –> Right-click on your SQL Server resource then click Properties –> Click on the Dependencies tab –> Click the Insert button to add an additional row, then select the shared disk that contains your SQL databases –> Ensure that all resources are required (there is an AND next to subsequent resources) then click OK.

The Following Illustrate the Resource Settings of SPLAB which is my testing environment.

clip_image001

  • The Name Depends on the IP Address

clip_image002

  • IP Address does not depends on anything

clip_image003

  • If Analyses services are Installed, it depends on the Network Name

clip_image004

  • Cluster Disks has no Dependencies

clip_image005

  • SQL Server has Dependencies of Disks and Network Name

clip_image006

  • And Finally Agent depends on SQL Server

clip_image007

once all these added. you done !

SharePoint Config Database gone Suspect Mode..

I had my SQL and SharePoint in VM Nodes. just while a go realized that SharePoint Config DB suddenly gone suspect after restarting my host which was unexpected.

image

Option 1.

The very first option came to my mind is to restore the database through a previous backup but I didn’t had a one since this is a new farm.

Option 2.

luckily there was an another option which is just an query as simple as below.

— Use the Master database
Use Master

— Verify that database has issues
EXEC sp_resetstatus ‘SharePoint_Config’

— Put the database in emergency mode
ALTER DATABASE SharePoint_Config SET EMERGENCY
DBCC checkdb(‘SharePoint_Config’)

— Set the database in single user mode
ALTER DATABASE SharePoint_Config SET SINGLE_USER WITH ROLLBACK IMMEDIATE

— Repair the database with data loss
DBCC CheckDB (‘SharePoint_Config’, REPAIR_ALLOW_DATA_LOSS)

— Set the database in multi-user mode
ALTER DATABASE SharePoint_Config SET MULTI_USER

— Verify that database is reset
EXEC sp_resetstatus ‘SharePoint_Config’

after running this the issue was fixed and I was able to get in to the Central Administration and other Web applications

image

Note: This can happen on any SQL DB and as per most of the blogs on web, SharePoint DB get corrupted occasionally when VM s restarts unexpectedly

so make sure that you save your VM before restart your host though it’s on staging or development because recovering takes time so better to avoid it without wasting.