The ultimate way to delete SharePoint application service

i.e. Search Service Application:
stsadm.exe -o deleteconfigurationobject -id "[GUID]"

And that way you can get to know the id:
Get-SPServiceApplication |?{$_.name -eq "[ServiceApplicationName]"}

How to rename Search Service Application databases

There are 3 databases related to Search Service Application:

1. Administration
2. Crawl
3. Property

Go to Central Administration > Application Management > Manage service applications. Find on the list application of type “Search Service Application” and click on its name. Then click on the Modify button.

Now you can see these databases.

From Central Administration you can rename only the Crawl and Property databases.

For each database select Edit Properties and change the database name. “Pending update” message appears in the Pending Changes column. Then click on Apply topology changes button. Wait patiently, on my machine it took above 10 minutes to complete.

You cannot rename Administration database from Central Administration. Run Sharepoint Managemet Shell for this purpose.

Before renaming the database, you have to pause the Search Service. But first step is to get to know Search Service identity. You can read the id of your Search Service Application when you run this command:

Get-SPEnterpriseSearchServiceApplication

Pause the service:

Get-SPEnterpriseSearchServiceApplication -Identity " service_id " | Suspend-SPEnterpriseSearchServiceApplication

Rename the database:

Set-SPEnterpriseSearchServiceApplication -Identity " service_id " -DatabaseName "new_database_name" -DatabaseServer "sql_server_name"

Resume the service:

Get-SPEnterpriseSearchServiceApplication –Identity "service_id" | Resume-SPEnterpriseSearchServiceApplication

In case there were any problems with renaming the database, after suspending the service run the Sql Server Management Studio. From here you can rename the database or, what is much safer , make a copy of it. After you have rename the database "manually", run the Set-SPEnterpriseSearchServiceApplication command.

How to find out what is the site template?

First, run the Power Shell script:

$web = Get-SPWeb <Site URL>
write-host "Web Template:" $web.WebTemplate " | Web Template ID:" $web.WebTemplateId
$web.Dispose()


You now know the site template name and id. But to know the template title, as you see it when creating a new site from web interface, run this command:


Get-SPWebTemplate | Sort-Object "Name"


Here is a list of most popular site templates:

Site Definition	Site Template Name & ID
Team Site	STS#0
Blank Site	STS#1
Document Workspace	STS#2
Blog	BLOG#0
Group Work Site	SGS#0
Visio Process Repository	VISPRUS#0
Basic Meeting Workspace	MPS#0
Blank Meeting Workspace	MPS#1
Decision Meeting Workspace	MPS#2
Social Meeting Workspace	MPS#3
Multipage Meeting Workspace	MPS#4
Assets Web Database	ACCSRV#1
Charitable Contributions Web Database	ACCSRV#3
Contacts Web Database	ACCSRV#41
Issues Web Database	ACCSRV#6
Projects Web Database	ACCSRV#5
Document Center	BDR#0
Records Center	OFFILE#1
Business Intelligence Center	BICenterSite#0
My Site Host	SPSMSITEHOST#0
Personalization Site	SPSMSITE#0
Enterprise Search Center	SRCHCEN#0
Basic Search Center	SRCHCENTERLITE#0
FAST Search Center	SRCHCENTERFAST#0
Enterprise Wiki	ENTERWIKI#0
Publishing Portal	BLANKINTERNETCONTAINER#0
Publishing Site	CMSPUBLISHING#0

Export / Import site, list or document library

To export a site, list, or document library, you can use either Windows PowerShell or Central Administration. But to import them, you can use only Windows PowerShell. So let's focus on the Power Shell.

Export command:
Export-SPWeb -Identity <Site URL> [-ItemUrl <Object URL>] -Path  <Export file name> [-IncludeUserSecurity] [-IncludeVersions] [-Force <True|False>] [-NoFileCompression] [-Verbose]
Import command:
Import-SPWeb -Identity <Site URL> -Path <Export file name> [-Force] [-NoFileCompression] [-Verbose]
-Force overwrites the export package if it already exists. During import use it to overwrite the list or library that you specified.

-NoFileCompression disables file compression in the export package. The export package is stored in the folder then. Use this parameter for performance reasons. If you used this option when exporting,  you have to specify it during import  as well.

-Verbose is used it to view the progress of the operation.

The site that you are importing to must have a template that matches the template of the exported!


Here you can read how to find out what is the site template?

The site that you are importing to must have the same default language as the exported site!

Access denied to "Configure service accounts"

I was completely surprised to get "Access denied" message when trying to open "Configure service accounts" option in Central Administration > Security. My account was in the farm administrators group, wasn't it!

But to be in the farm administrators group was not enough. To get access to the "Configure service accounts" I had to add my account to the local Administrators group on the machine running Sharepoint server.

That's all!

Quering tables residing on different servers and collation problem

If you want to query tables residing on another server first step is to link this server. You will do this in SQL Server Management Studio. Just expand the Server Objects node and you will see the Linked Servers node on which you can select "New Linked Server..." command.

Another option is to run these commands:

EXEC p_addlinkedserver    
  @server='<machine name>', 
  @srvproduct='',
  @provider='SQLOLEDB',
  @datasrc='<server name>',
  @catalog ='<database name>'
  
EXEC sp_addlinkedsrvlogin 
  '<server name>', 
  'false', 
   NULL, 
  '<login name>', 
  '<login password>'

Now you can query tables and views from the databases residing on the server you linked. They should be addressed this way:

[server].[database].[schema].[table]


It may happen that there are different collations in your host database and in linked database. As result, when linking tables from multiple servers in one query, you will get error:

Cannot resolve the collation conflict between "Polish_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

My first guess was to change the collation. I choose the host database as it was still in the development phase.

ALTER DATABASE [database-name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [database-name] COLLATE Polish_CI_AS
ALTER DATABASE [database-name] SET MULTI_USER


But it hasn't helped. The above commands changed the default collation of database and tables, but not the collation of existing columns.

Below you can find the code of the stored procedure generating ALTER TABLE statements for changing the columns collations all over the database.

But watch out! If there is any key or index on the column, the statement will fail. You have to drop the key, run the statement and recreate the key.

CREATE PROCEDURE [dbo].[ChangeCollation]

(

    @collation_name varchar(25)

)

AS

BEGIN

    SET NOCOUNT ON;

      

    DECLARE @TableName nvarchar(100)

    DECLARE @ColumnName nvarchar(100)

    DECLARE @ColumnType nvarChar(100)

    DECLARE @ColumnLengh float

    DECLARE @SQL nvarchar(1000)

    DECLARE @IsNullAble nvarchar(50)

    DECLARE cursor_tables CURSOR FOR

        select sysobjects.name from sysobjects where xtype='u'

             

    OPEN cursor_tables

    FETCH NEXT FROM cursor_tables INTO @TableName

    WHILE @@FETCH_STATUS = 0 BEGIN

      

        DECLARE cursor_columns CURSOR FOR

            select  syscolumns.name,

                    systypes.name as Type,

                    syscolumns.length,

                    syscolumns.isnullable

            from syscolumns

            inner join sysobjects on sysobjects.id=syscolumns.id

            inner join systypes on syscolumns.xtype = systypes.xtype

            where

                sysobjects.xtype='u' and

                (systypes.name='nvarchar' or systypes.name='varchar') and

                sysobjects.name=@TableName

             

        OPEN cursor_columns

        FETCH NEXT FROM cursor_columns 

            INTO @ColumnName, @ColumnType, @ColumnLengh, @IsNullAble

        

        WHILE @@FETCH_STATUS = 0 BEGIN

            IF (@IsNullAble='1')

                SET @IsNullAble = ' NULL'

            ELSE

                SET @IsNullAble = ' NOT NULL'

                    

            SET @SQL = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' '

                     + @ColumnType + '(' + CAST(@ColumnLengh as NVARCHAR) + ') COLLATE '

                     + @collation_name + @IsNullAble

            PRINT(@SQL)

            --EXEC(@@SQL)

            FETCH NEXT FROM cursor_columns 

                INTO @ColumnName, @ColumnType, @ColumnLengh, @IsNullAble

        END

        CLOSE cursor_columns

        DEALLOCATE cursor_columns

        FETCH NEXT FROM cursor_tables INTO @TableName

    END

    CLOSE cursor_tables

    DEALLOCATE cursor_tables

END

GO

Tips for configuring search center

1. People searching does not work...
... while searching global scope ("All Sites") returns proper results.


Go to the "CA > Manage service applications > your Search Service Application > Content Sources". Make sure that one of the content sources being crawled (most probably "Local SharePoint sites") has the following entry in start addresses: "sps3://server-name" - where server-name is your web application URL.

You now have to check the Search Crawling Account permissions. Go to the "Security > Specify web application user policy" and select your web application. Make sure that crawling account has Full Read permission.

Search Crawling Account also needs to have permissions to the User Profile Service Application. To check it, open "Manage service applications", highlight User Profile Service Application and click on the Administrators button at the ribbon. The required permission is "Retrieve People Data for Search Crawlers".

Run full crawl on the content source containing "sps3://server-name" start address.

Now your people searching should work...

2. Searching within contextual scope does not work...
... while searching global scope ("All Sites") returns proper results.

Go to the Central Administration and check the alternate access mappings for the zone Default. Then go to the "Manage service applications > your Search Service Application > Content Sources". One of the content sources defined there (most probably "Local SharePoint sites") pertains web application where search in contextual scope is not working. Check the start addresses: the host URL must be the same as defined in alternate access mappings for the zone Default.

SharePoint creates the entry in the content source whenever you create a web application. But you must remember that changing  alternate access mappings can cause the URLs listed in content source stop matching and then your contextual searching stops working.

3. You want to have searching results from contextual scope displayed on the same, customizable page, as searching results from global scope.


That's pretty easy. Go to the Site Collection Administration settings and choose "Search settings".
In the "Site Collection Search Dropdown Mode" select mode that allows contextual scope, otherwise the next setting - "Site Collection Search Results Page" - will be disabled.
Now you have to enter address of the page that the searching result will be displayed on - preferably the search results page from your Search Center: