Jan 20, 2012

What Do You Mean SQL Server Not Found?


This is a quick post about my last CU update on one of my SharePoint 2010 Farms.
This is one of the smaller farms I manage, only two WFE/APP servers and a SQL backend server.
I had one of the servers throw the following errors when running the Configuration Wizard.




A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)




Failed to initiate the upgrade sequence. An exception of type System.Data.SqlClient.SqlException was thrown. Additional exception information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SharePoint.Utilities.SqlSession.OpenConnection() at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock) at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, Boolean retryForDeadLock) at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command) at Microsoft.SharePoint.Upgrade.SPDatabaseSequence.GetVersion(SPDatabase database, Guid id, Version defaultVersion, SqlSession session, SPDatabaseSequence sequence) at Microsoft.SharePoint.Upgrade.SPManager.RemoveLastUpdatedProcessInformation(SqlConnectionStringBuilder scsbConfigurationDatabase) at Microsoft.SharePoint.Upgrade.SPManager.BootStrap(Guid sessionId, SPUpgradeOperationFlags flags) at Microsoft.SharePoint.PostSetupConfiguration.UpgradeBootstrapTask.Run() at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()




Now keep in mind that the 1st server ran the Config Wizard without any issues.
After poking around the event viewer and ULS I decided to run the Config Wizard one more time. The second time I noticed that the SQL Server name was in quotes and had a leading space between the first quotation mark and the first letter of the SQL server name.
I opened regedit on both servers and searched for the SQL server name and found that the DSN was incorrect. I changed it to have the correct DSN and rerun the Config Wizard. It ran without any issues.
For those who are interested, the DSN for the Config Database is located: HKLM:\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Secure\ConfigDB


I love SharePoint Administration…
I love SharePoint Administration…
I love SharePoint Administration…
I love SharePoint Administration…
I love SharePoint Administration…
Posted by at 2 comments:
Labels: , ,

Jan 12, 2012

Installing SharePoint Diagnostic Studio 2010 (SPDiag 3.0)


Blog Note:
    This is a fairly straight forward install.
Overview
This article describes the steps required to install SharePoint Diagnostic Studio 2010 (SPDiag 3.0) on a SharePoint Farm server.
Rights Requirements


  • You must have the following rights to be able to successfully execute this procedure:
  • You must be a local administrator on the SQL server for the Farm and on every SharePoint Server.
  • The Farm account should have SA rights on the SQL server for the Farm to get the full benefit out of SPDiag.
  • You must be a Farm Administrator for the Farm.
Information Requirements
  • Ensure that Usage and Health Data Collection has been configured on the target farm.
    • This is outside the scope of this article, but more information may be found in TechNet. http://technet.microsoft.com/en-us/library/ee663480.aspx
Decision Points
  • Install on a Farm server or a remote system.
    • If you wish to install on a remote system (non-Farm server), you will have to turn on Remote PowerShell on a target Farm server and the system that is hosting SPDiag. (Outside the scope of this procedure)
  • Which SharePoint Farm server to install SPDiag, if installing on a Farm server.
    • You should elect one Farm server as your administrative entry point for the farm.
    • This is usually the server that has SharePoint Central Administration site hosted.
Procedure Outline
  1. Ensure that the Farm account has SA rights on the Farm's SQL server.
  2. Download Microsoft SharePoint 2010 Administration Toolkit v2
  3. Install SPDiag from the Microsoft SharePoint 2010 Administration Toolkit v2
Procedure
  1. Ensure that the Farm account has SA rights on the Farm's SQL server.
    1. Work with a DBA if you are uncomfortable or unfamiliar with assigning roles in SQL Server.
  2. Download Microsoft SharePoint 2010 Administration Toolkit v2
    1. Download the install package from http://go.microsoft.com/fwlink/p/?LinkId=196866
    2. Save it to <Drive>:\SharePoint2010AdminToolkit\
  3. Install SPDiag from the Microsoft SharePoint 2010 Administration Toolkit v2
    1. Launch <Drive>:\SharePoint2010AdminToolkit\SharePoint2010AdministrationToolkit.exe
    2. Accept the license terms
    3. Click Next
    4. Click on the drop down menu next to "SharePoint 2010 Administration Toolkit"
    5. Select "Entire feature will be unavailable"
    6. Click on the drop down menu next to "SharePoint Diagnostic Studio"
    7. Select "Will be installed on local hard drive"
    8. Click Next
    9. Click Next
    10. Click Finished


References:
SharePoint Diagnostic Studio 2010 (SPDiag 3.0) (SharePoint Server 2010)

Configure usage and health data collection (SharePoint Server 2010)

SharePoint 2010 Administration Toolkit (SharePoint Server 2010)

Foot Note:
That is pretty much it for the install. I did get an error the first time I try to create my 1st project.
01/12/2012 12:01:34    An update conflict has occurred, and you must re-try this action. The object SPWebFrontEndDiagnosticsPerformanceCounterProvider Name=job-diagnostics-performance-counter-wfe-provider was updated by <My_Farm_Admin_Account>, in the powershell (7056) process, on machine NET-PRV-WSS-011. View the tracing log for more information about the conflict.
I just exited from the studio and tried again. It worked without a hitch, so I am guessing there were timer jobs that hadn't finished running.
Posted by at No comments:
Labels: , , , ,

Jan 11, 2012

Remove Remote BLOB Storage (RBS) from a SharePoint 2010 Environment


Blog Note:
This is a companion article to my post titled: Remove Remote BLOB Storage (RBS) from a SharePoint 2010 Environment

NOTE: This solution has never been tested. This has been purely developed based on research.
Overview
This article describes the steps required to configure RBS in a SharePoint 2010 environment.
Rights Requirements


  • You must have the following rights to be able to successfully execute this procedure:
  • You must be a local administrator on the SQL server for the Farm and on every SharePoint Server.
  • You must have SA rights on the SQL server for the Farm.
  • You must be a Farm Administrator for the Farm that needs RBS configured.
  • You must be a Site Collection Administrator on each Site Collection you are configuring for RBS support.
Information Requirements
  • You must have the following information to be able to successfully execute this procedure:
  • Name(s) of the SQL server and the SharePoint server(s)
  • The URL(s) of the Site Collection that you are configuring for RBS support
  • The names of the content database(s) of the Site Collection that you are configuring for RBS support
Placeholders Used in this Procedure
  • <CONTENT DATABASE> = The name of the SharePoint content database as it appears in the SQL Server Management Studio
  • <FQDN> = The FQDN of the SharePoint Web Application
Procedure Outline
  1. Migrate all content off RBS to SQL and disable RBS for the content database.
  2. Change the default RBS garbage collection windows to 0 on the content database.
  3. Disable the RBS Maintainer Task
  4. Run the RBS Maintainer
  5. Uninstall RBS
  6. Disable FILESTREAM on the SQL server.
Procedure
Migrate all Content off RBS to SQL and Disable RBS for the Content Database
  1. Log on to a SharePoint Web Front End
  2. Launch a SharePoint 2010 Management Shell
  3. Run the following code:
    $cdb=Get-SPContentDatabase <ContentDbName>
    $rbs=$cdb.RemoteBlobStorageSettings
    $rbs.GetProviderNames()
    $rbs.SetActiveProviderName("")
    $rbs.Migrate()
    $rbs.Disable()
Note: This might take some time depending on amount of data in your RBS store.
Change the Default RBS Garbage Collection Windows to 0 on the Content Database
  1. Log on the SharePoint Content SQL Database Server.
  2. Launch a command prompt
  3. Run the following commands:
    exec mssqlrbs.rbs_sp_set_config_value 'garbage_collection_time_window','time 00:00:00′
    exec mssqlrbs.rbs_sp_set_config_value 'delete_scan_period','time 00:00:00′
Disable the RBS Maintainer Task
  1. Launch the Windows Task Scheduler
  2. Click Start
  3. In the run field enter "Task Scheduler"
  4. Right click on the RBS Maintainer task and click Disable
Run the RBS Maintainer
  1. Open a command prompt
  2. Run the following command:
    "C:\Program Files\Microsoft SQL Remote Blob Storage 10.50\Maintainer\Microsoft.Data.SqlRemoteBlobs.Maintainer.exe" -connectionstringname RBSMaintainerConnection -operation GarbageCollection ConsistencyCheck ConsistencyCheckForStores -GarbageCollectionPhases rdo -ConsistencyCheckMode r -TimeLimit 120
Uninstall RBS
  1. Run c:\masters\rbs.msi
  2. Walk through the wizard selecting the Remove option.
Disable FILESTREAM on the SQL server.
  1. Launch the SQL Server Management Studio
  2. Open a new query window
  3. Run the following script:
    use [<CONTENT DATABASE>]
    ALTER TABLE [mssqlrbs_filestream_data_1].[rbs_filestream_configuration] DROP column [filestream_value]
    ALTER TABLE [mssqlrbs_filestream_data_1].[rbs_filestream_configuration] SET (FILESTREAM_ON = "NULL")
  4. Open a new Query window
  5. Run the following script:
    ALTER DATABASE [<CONTENT DATABASE>] Remove file RBSFilestreamFile;
    ALTER DATABASE [<CONTENT DATABASE>] REMOVE FILEGROUP RBSFilestreamProvider;
  6. Launch the SQL Server Configuration Manager
  7. Right Click on the SQL Service and click Properties
  8. Click the FILESTREAM tab
  9. Deselect all check boxes
  10. Click OK
  11. Restart the SQL Service
  12. Run the IISRESET command on each of the SharePoint Web Front End Servers
Posted by at No comments:
Labels: , , ,

Configure Remote BLOB Storage (RBS) in a SharePoint 2010 Environment

Blog Note:

There are a ton of blogs that go over different aspects of setting up RBS for SP 2010. This is the procedure that I follow for setting up RBS for a single site collection. Many of the steps and code was taken from Microsoft TechNet, MSDN and other blogs.

Overview

This article describes the steps required to configure RBS in a SharePoint 2010 environment.

Rights Requirements

  • You must have the following rights to be able to successfully execute this procedure:
  • You must be a local administrator on the SQL server for the Farm and on every SharePoint Server.
  • You must have SA rights on the SQL server for the Farm.
  • You must be a Farm Administrator for the Farm that needs RBS configured.
  • You must be a Site Collection Administrator on each Site Collection you are configuring for RBS support.

Information Requirements

  • You must have the following information to be able to successfully execute this procedure:
  • Name(s) of the SQL server and the SharePoint server(s)
  • The URL(s) of the Site Collection that you are configuring for RBS support
  • The names of the content database(s) of the Site Collection that you are configuring for RBS support

Placeholders Used in this Procedure

  • <CONTENT DATABASE> = The name of the SharePoint content database as it appears in the SQL Server Management Studio
  • <FQDN> = The FQDN of the SharePoint Web Application
  • <Task_Account> = The task account that has Farm and Site collection administrator access to run the RBS Maintainer scheduled task.

Procedure Outline

  • Enable and change FILESTREAM settings on the SQL server
  • Enable content database(s) to use FILESTREAM
  • Install the RBS Provider on all servers
  • Enable RBS on the specific web application(s)
  • Enable FILESTREAM I/O access

Procedure

Enable and change FILESTREAM settings on the SQL server

  1. Log on to the SQL server for the Farm
  2. Launch the SQL Server Configuration Manager
  3. Right click on the MSSQLSERVER instance
  4. Click Properties
  5. Click the FILESTREAM tab
  6. Check Enable FILESTREAM for Transact-SQL access
  7. Click Apply and OK
  8. Launch SQL Server Management Studio
  9. Open a new query window
  10. Copy and Paste the following code:

    EXEC sp_configure filestream_access_level, 2

    RECONFIGURE

  11. Click Execute

Enable Content Database(s) to Use FILESTREAM

  1. Launch SQL Server Management Studio
  2. Open a new query window
  3. Copy and Paste the following code:

    use [<CONTENT DATABASE>]
    if not exists (select * from sys.symmetric_keys where name = N'##MS_DatabaseMasterKey##')
    create master key encryption by password = N'Admin Key Password !2#4'

  4. Click Execute
  5. Open a new query window
  6. Copy and Paste the following code:

    use [<CONTENT DATABASE>]
    if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')
    alter database [<CONTENT DATABASE>]
    add filegroup RBSFilestreamProvider contains filestream

  7. Click Execute
  8. Open a new query window
  9. Copy and Paste the following code:

    use [<CONTENT DATABASE>]
    alter database [<CONTENT DATABASE>]
    add file (name = RBSFilestreamFile, filename = 'c:\RBS_STORE')
    to filegroup RBSFilestreamProvider

  10. Click Execute

Install the RBS Provider on all servers

  1. Download the RBS Provider install package from http://go.microsoft.com/fwlink/?LinkId=177388 to C:\masters on every server in the farm including the SQL server.
  2. NOTE: Do not run the MSI file directly!
  3. Change directory to c:\masters
  4. On the SQL Server and then the first WFE server run the following command:

    msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="<CONTENT DATABASE>" DBINSTANCE="" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1

  5. Run the following command on all other SharePoint servers:

    msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi DBNAME="<CONTENT DATABASE>" DBINSTANCE="" ADDLOCAL="Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer"

  6. Check c:\masters\rbs_install_log.txt on each server after installation:
  7. Search for "Installation completed successfully"

Enable RBS on the specific web application(s)

  1. On one of the SharePoint Farm servers
  2. Open the SharePoint Management Shell on run these commands:

    $cdb = Get-SPContentDatabase –WebApplication http://<FQDN>

    $rbss = $cdb.RemoteBlobStorageSettings

    $rbss.MinimumBlobStorageSize = 1048576

    $rbss.Installed()

    $rbss.Enable()

    $rbss.SetActiveProviderName($rbss.GetProviderNames()[0])

    $rbss | Format-List

Check to see if the content database has been configured

  1. Launch SQL Server Management Studio
  2. Open a new query window
  3. Copy and Paste the following code:

    USE [<CONTENT DATABASE>]

    SELECT *

    FROM dbo.DatabaseInformation

  4. Click Execute
  5. You should see two rows titled "RbsEnabled" and "RbsProvider" with values of "1" and "FilestreamProvider_1" respectively.

Enable FILESTREAM I/O access

  • Log on to the SQL server for the Farm
  • Launch the SQL Server Configuration Manager
  • Right click on the MSSQLSERVER instance
  • Click Properties
  • Click the FILESTREAM tab
  • Check Enable FILESTREAM for file I/O streaming access
  • Check Allow remote clients to have streaming access to FILESTREAM data
  • Click Apply and OK

Grant the SharePoint Application Pool Account Access to the Share and File System

  1. Launch Computer Manager
    1. Expand tree and click Shares
    2. Right click on MSSQLSERVER and click Properties
    3. Click on Share Permissions tab
    4. Click Add…
    5. Enter the SharePoint Application Pool account and click OK
    6. Make sure that Full Control, Change, and Read are selected and click OK
  2. Open Windows Explorer and navigate to the C:\ drive
    1. Right click on RBS_STORE and click Properties
    2. Click the Security tab
    3. Click Edit…
    4. Enter the SharePoint Application Pool account and click OK
    5. Make sure that the account has Full Control rights
    6. Click OK

Setup RBS Maintainer

Note: Ensure that the task account tsk_sp_tasks has Log on as a batch job through GPO

Note: Ensure that the task account has full control rights of the RBS share and directory

  1. Log on to the SharePoint SQL Content Database Server
  2. Launch the Windows Task Scheduler
    1. Click Start
    2. In the run field enter "Task Scheduler"
  3. Click Action
  4. Click Create Task…
    1. Enter "RBS Maintainer" in the Name field
    2. Select the Run whether the user is logged on or not radio button
    3. Click Change User or Group…
    4. Enter the credentials for the account <Task_Account>
  5. Click the Triggers tab
    1. Click New…
    2. Setup a daily task to run at 0'dark:30
  6. Click the Actions tab
  7. Click New…
  8. In the Program/script: field enter the following:

    "C:\Program Files\Microsoft SQL Remote Blob Storage 10.50\Maintainer\Microsoft.Data.SqlRemoteBlobs.Maintainer.exe" -connectionstringname RBSMaintainerConnection -operation GarbageCollection ConsistencyCheck ConsistencyCheckForStores -GarbageCollectionPhases rdo -ConsistencyCheckMode r -TimeLimit 120

  9. Click OK
  10. Click Yes to the confirmation pop up
  11. Click OK

References:

http://technet.microsoft.com/en-us/library/ee748638.aspx

http://msdn.microsoft.com/en-us/library/cc949109(v=sql.100).aspx

http://blogs.technet.com/b/wbaer/archive/2011/02/22/filestream-and-sharepoint-2010.aspx

http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/55a8be1e-00f4-4def-bbd6-79a79987663e/

http://bkemmler.blogspot.com/2010/03/enable-filestream-rbs-for-sharepoint.html

http://alipka.wordpress.com/2010/06/19/how-to-disable-rbs-in-sharepoint-2010/
Posted by at No comments:
Labels: , , ,
Subscribe to: Posts (Atom)