Tuesday, April 14, 2009

Got irritated by the Sharepoint GUID naming conventions!!!!!!!

Give your own naming conventions to the sharepoint databases instead of the GUID names generated on its own.

There is always a creation of the sharepoint admin content database when we configure MOSS 2007. If we are using a single database server and have multiple MOSS farms, then problems comes up related to manageability of the databases. There is always confusion among the sharepoint content DBs as they are differenciated from each other by the GUIDs mentioned in their names. To avoid such confusions, follow the below steps-
For example,
1> Take a backup of the admin content database "SharePoint_AdminContent_046d584c-2f4b-49a3-93df-a7bd0dde771f" and restore it as "Servername_Sharepoint_AdminContent" on the same database server.

2> create a command file with the below details and name it as renameDB.cmd. Please note the the text between [] should be provided by you before you run the command file.
Rem splpri = SQL server
Rem mossdb_old = old Moss content Database
Rem mossdb_new = new Moss content Database
Rem mosssite = Moss URLRem
sspadm = SSP login acct
Rem ssppass = Password for ssp login
Rem domain = domain for sspadmin login

SET sqlpri= [Database Server Name]
SET mossdb_old=SharePoint_AdminContent_046d584c-2f4b-49a3-93df-a7bd0dde771f
SET mossdb_new=ServerName_Sharepoint_AdminContent
SET mosssite=http:// [Central Administration URL]
SET sspadm= [Service Account]
SET ssppass= [Service Account pwd]
SET domain= [Service account Domain]
--------------------------ECHO.
ECHO Preparing %mosssite% Content Databasestsadm -o preparetomove -contentdb %sqlpri%:%mossdb_old% -site %mosssite%

ECHO Removing content database for %mosssite%
stsadm -o deletecontentdb -url %mosssite% -databasename %mossdb_old% -databaseserver %sqlpri%

ECHO Stopping and restarting IISECHO.

iisreset /noforce

ECHO.

ECHO Adding content database for %mosssite%

stsadm -o addcontentdb -url %mosssite% -databasename %mossdb_new% -databaseserver %sqlpri%

ECHO Stopping and Restarting IIS

ECHO.

cd\iisreset

Create a CMD File

1> Create a new text document.
2> Copy paste the highlighted text as mentioned above into the text file
3> Rename the text file as RENAMEDB.cmd
4> This shall create the command file.

Once the command file is created, Copy the command file to the BIN folder of the sharepoint i.e. "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN" in the web server. Then from the command prompt go to the above path and type RENAMEDB.cmd and press enter. This should be it.

You can verify the same by going to application management--> content databases--> then select the central administration. Here you can see your new content database name coming up.

You can go back to the database server and delete the "SharePoint_AdminContent_046d584c-2f4b-49a3-93df-a7bd0dde771f".

Thursday, April 9, 2009

Integrating SQL 2008 SSRS with MOSS 2007

Pre-requisites -

a> MOSS 2007 installed on the machine.
b> SQL 2008 installed with the report server configuration to be done.

Today I will be discussing the configuration of Reports 2008(as i selected to configure it later) which can be integrated with MOSS 2007. The steps are as follows -

1> Go to Start --> All Programs --> Microsoft SQL Server 2008 --> Configuration Tools --> Reporting services Configuration Manager. The page opens up as shown in the image below. See that the report Service status is started.


2> Fill up the details about the service account that will be used to the report server database and also the sharepoint config and the content database as shown in the screenshot below.


3> Provide the details of the report server web service URL. you can keep it as default or you can provide details as shown in the screenshot below.


4> Coming to the database, we need to click on Change database button inorder to configure the report server databases.

5> After Clicking on the Change Database button, the Change Database page comes up. Here we can either connect to an existing Reporting Server Database or create a new Report Server database. Then click on Next after selecting the Create new Report Server Database as shown below.


6> Then provide the Database server name with the authentication type as Integrated Security. Then click on Next.


7> we then come to the database page where we mention the name of the report server database and also where the report server should be configured in Native mode or in Sharepoint Integrated mode. As we are integrating SSRS 2008 along with MOSS 2007, we select the latter option. Then we click on Next.



8> Finally the configuration process takes place and once done, we click on finish.


9> Once the configuration is done, we come back to the main page of Reports Services Configuration Manager where all the details are reflected as per the setting we did in the above steps.

10>Next we do the configuration of the Report manager URL as shown in the screenshot below. We can keep the defaults also.


11> Then we do the email settings as shown in the screenshot below.


12> we can keep the execution account, encryption keys and scale out deployment as it is without any changes unless and until our requirement makes these mandatory for implementation.


Once this is done, we are ready for the sharepoint integration from SQL 2008 side.


In the next Blog, I will be explaining about the configuration from Sharepoint (MOSS 2007) side.

Thursday, April 2, 2009

Moving the tempDB files once the disk is full

Problem Description

If we come across following errors in log file Source:
MSSQLSERVER Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL. Back up the TRANSACTION LOG FOR the DATABASE TO free up SOME LOG SPACE.

Solution Description

we need to make sure that TempDB is set to autogrow and we do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then we can arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow. )

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to other drive.
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.
We need to follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer or SSMS and connect to your server.

Run this script to get the names of the files used for TempDB.

USE TempDBGOEXEC sp_helpfileGO

Results will be something like:
name fileid filename filegroup size------- ------ ---------------------------------------tempdev 1 C:\Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

along with other information related to the database.
The names of the files are usually tempdev and templog by default.
These names will be used in next statement. Run following code, to move mdf and ldf files.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'e:datatemplog.ldf')
GO
The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

Windows Server 2003 Event ID 8 and source crypt32

Problem Description

There are errors coming up in the Event viewer "Failed auto update retrieval of third-party root list sequence number from: with error: This network connection does not exist." This causes the eventviewer filled up unnecessarily

Solution Description

This can be troubleshooted as follows -
1> Go to "Add/Remove programs" in the server.
2> Click on "Add/remove Windows Components".
3>uncheck the "Update Root Certificates".

This will resolve the crypt32 logging issue in the event viewer

SQL Server databases are not supported on compressed volumes

Problem Description

This is related to the MS SQL server administration. There is a common mistake of compressing the drives instead of shrinking the files to gain more space in the server. It is important to understand that SQL Server database on compressed volumes are not supported. There are two scenarios one for SQL 2000 and other one is for SQL 2005.

SQL 2000
In SQL Server 2000, you could create a new database with its .mdf and .ldf files located on NTFS or FAT compressed drives. The interface doesn't restrict you from doing this

SQL 2005
As opposed to SQL 2000, SQL 2005 doesn't even allow you to place data and log files of Read/Write databases on compressed volumes.
For example, Let's create a database called Compressed_DB with .mdf and .ldf files located on a compressed file system (E drive in my case here). You will get an error: Create failed for Database 'Compressed_DB'. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The file "E:\Compressed_DB.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5118) This is because a read/write databases primary .mdf and .ldf files cannot be created on a compressed volumes in SQL 2005. Also, if you try to restore a database backup (.bak) file of a read/write database with its .mdf or .ldf or both pointed on a compressed drive, you will again get an error as follows: Restore failed for Server ''. (Microsoft.SqlServer.Smo) System.Data.SqlClient.SqlError: The file "e:\Db.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. (Microsoft.SqlServer.Smo)

Solution Description

Log files should never be placed on compressed file systems. System database (master, msdb, model and tempdm) cannot be placed on compressed drives. If you want to restore or take a backup please be sure that your drive is not compressed.

Incorrect syntax near COLLATE. Error When Trying to Join a SharePoint Farm

Problem Description

I encountered another nasty bug while rebuilding our Microsoft Office SharePoint Server (MOSS) 2007 Development environment (DEV). Since the time I originally created DEV, I installed SQL Server SP2 and also restored several legacy databases to support the development of the next version ("v2") of our solution. After running the SharePoint Products and Technologies Configuration Wizard on the SSP server to create the farm, I ran the wizard on the first front-end Web server in order to join it to the new farm. On the second step of the configuration wizard, I left the default option selected (Yes, I want to connect to an existing server farm) and then clicked Next. On the Specify Configuration Database Settings step, I typed the name of the SQL Server and then clicked Retrieve Database Names. At this point, I was presented with the "Unhandled exception" dialog displayed by the .NET Framework when something very bad happens. Error message: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Incorrect syntax near 'COLLATE'. You may need to set the compatibility level of the current database to a higher value to enable this feature.

See help for the stored procedure sp_dbcmptlevel. Details: See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box. ************** Exception Text ************** System.Data.SqlClient.SqlException: Incorrect syntax near 'COLLATE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SharePoint.PostSetupConfiguration.SqlSession.ExecuteNonQuery(SqlCommand command) at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.DatabaseTableWithColumnExists(String table, String column) at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.GetV3WSSConfigurationDatabases() at Microsoft.SharePoint.PostSetupConfiguration.ConnectConfigurationDbForm.GetDatabasesButtonClickEventHandler(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


Solution Description

whenever we try to join the server farm, the web server scans for the databases in the SQL server. If it finds any legacy database in that instance of SQL server, the connection breaks throwing this error. It is important the the compatibility level of these databases are raised to the recent most before we try to join the sharepoint to a farm.

Check for the compatibility using the queries mentioned below. And once we find the database we upgrade it to the latest compatibility level.

sp_dbcmptlevel [ [ @dbname = ] dbname ] [ , [ @new_cmptlevel = ] version ]

example- suppose the db DBTEST is 6.0 compatible then we are changing its compatibility by using the query

sp_dbcmptlevel [ [ @dbname = ] DBTEST ] [ , [ @new_cmptlevel = ] 80 ]
version

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

SSIS and SQL Server Named Instance issue

Problem Description

There are deployment issues when we try to deploy SSIS package on to a named instance SQL server with no default instance of it and store it in the SQL server. The errors that basically come out are the timeout errors. Even the event viewer would shows the same error.

Solution Description

SSIS runs as a service, and only one instance of SSIS can run on on platform. If we are working with one Named instances of SQL Sever the SSIS service will by default use the default instance to store packages (if we chose to store in the SQL sever). If we don't have a default instance or if we want to use a different SQL server to store your packages, we must alter the SSIS configuration file. we can find the file in: the SQL Sever install directory \90\DTS\Binn (e.g. c:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml) we change the ServerName attribute and this should work. Check the image for the same.




Let me know if this post helps anyone.