where is SSRS 2008 web service?

When you configure SSRS's web service URL in RS Configuration Manager:

Have you ever wondered where this web service is or tried to go to IIS and look for a virutal directory called ResportServer? I did, but could not find it of course. As the RS server is also a SharePoint WFE (in sharepoint integrate mode), could it be a Managed Path defined in the sharepoint? No, it is not either. Then how could this url be resolved?

The answer is,  this url is reserved in HTTY.sys, and SSRS 2008 is not using IIS any more, for more details see this post.

Install SharePoint 2010 RTM with a named sql Instance

I am installing a sharepiont 2010 farm with its configuration database in a remote sql server, and am using a named sql instance for its configuration database. During  running SharePoint Product Configuration Wizard, I keep getting the following clueless error message:

Cannot connect to database master at SQL server at ..... The database might not exist, or the current user does not have permission to connect to it.

This happened even after I opened firewall exceptions for sqlservr.exe or whatever port the named instance is running at! As that was what I did to the default instance and I never had problem connecting to the default instance. So what is the issue with the named instance?

In SQL server, I run sql profile and found no connection even being attempted from the sharepoint wizard, so it seems most likely a firewall issue.  As a testing, I turned off window firewall, and it works immediately!

But I can't leave the firewall off, so I have to find out what else I need to open in the firewall. After some googling, I understand how sql named instance works differently from default instance: the named instances need handshaking when a connection is requested (as client requests normally don;t include port number, but just instance name), and it is SQL Browse Service which helps client find port number at which the named instance is running at. The service name is sqlbrowser.exe and it is running at UDP port 1434.

With that, opening firewall for either .exe or UDP port 1434, the problem goes away, and I got a first look at SharePoint 2010 RTM after so many months with Beta. It is so exciting!

Colon a SharePoint web application

I was asked to colon a web application, essentially this is just to backup/restore content db. sounds very simple? but a couple very costly pitfalls:

First, if new web app resides in the same farm, the challenge is, each collection Id has to be unique.

• use stsadm addcontentdb to assign a different id, but as warned by MS, the site collection very likely becomes an orphan;
• use stsadm backup/restore for each individual site collection;

Second, it is very tempting to take this shortcut: create a web application and then do content database backup/restore. This is an absolute failure path since SharePoint Config DB is left out totally.

The working path is:

• Restore the content DB (if in the same SQL instance, file names need to be changed)
• creating a new web application in a different farm, use the restored db to replace the one randomly generated by SP

or

• after web application being created, use stsadm -o addcontentdb cmd line or UI to add the restored db, and then remove the one auto-generated by SP.

whether stsadm or UI, the credential needs to have access to the database. In case of UI, it is farm account(running central admin pool) and of course it is logon user account for stsadm. SharePoint will grant all other pool ids (such as application portal pool id) for the database access.

SharePoint databases part 2

part 1 outlines sharepoint databases and database backup. This part will focuse on secrity: what sharepoint accounts have access to sharepoint databases and in what roles.

SharePoint_config database:

• install account is its dbo
• farm account (and local admin) in db_owner role
• application pool account in WSS_Content_ApplicationPoolid role

Central_Admin database:

• same as config except that local admin is not in db_owner role

Content database:

• farm account is dbo
• app pool account and ssp service account are in db_owner role

SSP (and SSP Search DB):

• same as content database, plus search service account is in db_owner

Server Roles:

• Install account has dbcreator fixed server role & securityadmin fixed server role.
• Farm account has the same fixed server role, but it is automatically configured.
• other service only has public server role.

Understanding those and sharepoint application pool id (see this) can help to solve a lot sharepoint database permission issues such as :EXECUTE permission denied on object 'proc_putObject' in event log tells that the application pool id doesn't have write permission on configure database.

SharePoint databases part 1

It is long time since I started to wonder what databases SharePoint is using, and today I finally spend some time on this and get some clarifications:

1) SSP database and SSP search database. These 2 databases are tied with your individual SSP, and they are backup and restored with SSP. For backup and restore SSP, see This KB;

2)Content Database for SSP site and/or MySite: When you create your SSP,if you setup individual web applications for your SSP site and/or MySite , you will have 2 more extra content databases. But they need to be backuped and restored individually. They are content databases.

3) Config database: this is most importanat database, and if moved to a different SQL server, the target sql server must have exact the same version number (SPs and KBs) applied to backup/restore operation, seems Log Shipping can overcome this restriction.

4)Content Database for the Center Admin Site: This database name can't be modified and must be backup and restored with Config database. (Config DB name can be changed in registry: My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB)

5)WSS search database. I am not sure what this datbase is used in MOSS since there is SSP Search database, is this for WSS Search as a legacy from WSS 2.0?
but anyway, this database need to be backuped and restored individually. Actually every time when you restart Search Help in the central Admin, you need to recreate this database.

6)Content database for portal sites. This is the database normally referred as "content databse".

You can pretty much manually move (via SQL backup and restore) all databases but SSP related dbs, which will end with SSP related dbs stay in the old SQL server, and all others live in a new SQL Server. To move all sharepoint database? see this KB http://technet.microsoft.com/en-us/library/cc512725.aspx for SSP backup/restore and SQL Server Connection Alias. Be cautious, SQL Connection Alias can cause a lot confusions in the future.

update: there is another db which draws more and more attentions as performance killer: TempDB. This db is particularly related with sharepoint indexing/crawling. For db related performance recommendation, see this post.

Part 2 will focuse on sharepoint database permission.

very large SharePoint Database Log files

It is not unusual that you may find sharepoint databases have very large log files, sometimes they can be hundreds times of size of data file. The cause of this is:

• the databases in "Full" recovery model, and
• the log files have never been backed up ( this happens when doing sharepoint native backup, it only backups data, not log)

When databases in a Full recovery model, neither full nor differential backup will truncate inactive segments in log files. Those segments thus can't be reused in the future, which causes the log file to continue growing without bound.

The solution is,

• turn recovery model to 'Simple' (for non-prod environment only), or
• specifically backup Log files by using the following sql statement:
• BACKUP LOG WSS_Content_80 TO DISK='z:\temp\80.log.bak'. The result of this is: when log backup is done, sql server automatically truncates all inactive transactions and allow those segments to be reused in the future.\
• do Transaction Log back up on database. The T-log backup will automatically truncate log.

However, the log file size will not be reduced until you shrink the log file size by sql statement:

• DBCC SHRINKFILE ('Log_Logic_Name')

you can find the log logical name by issue sp_helpdb or from UI.

So to summary, the best practice is

• Turn on "Simple" recovery model for Dev, and Keep "Full" model for Prod
• do sql backup rather than sharepoint backup
• full backup complemented by daily partial backup and then by hourly T-log backup