SharePoint 2013 Foundation: Remove GUIDs from Search database

How to scrub those GUIDs to rename your search service application databases in SharePoint 2013

One of the limitations of provisioning a search service application in SharePoint Foundation 2013 is that you have to use the Farm Configuration Wizard in Central Administration.

When using this method to provision the search service application, Microsoft is kind enough to append a GUID to the end of all the search database names. This is rather annoying for those of us that like to keep our SQL environments looking clean by using well formatted naming conventions for our database names.

So, how do we make those database names less messy?

I wish I could say that we could use the normal go-to, PowerShell, to provision search for Foundation, but no such luck…

Database names

Unfortunately, the ability to provision 2013 search service application through PowerShell is reserved for SharePoint 2013 Server Standard and Enterprise editions. If you have a Foundation server farm, then your options are limited. THE GOOD NEWS: If you are looking to automate the build, then there are some other options out there.

Gary Lapointe found a way to script the process using reflection, but his script effectively just provisions search the way the configuration wizard does, and does not provide a way to provide DB names. Jasjit Chopra took Gary’s script a little further and figured out how to specify the database names.

The biggest potential concern with using reflection to call the internal methods is that it’s not something that will be supported by Microsoft. So if you break your farm or if your search service application breaks then you’ll be on your own. I also would not do it on an existing production environment before testing it first.

There is another method to scrub those GUIDs from your search database names, and it comes straight from Technet’s article “Rename the Search service application databases in SharePoint 2013.” So today I am going to walk through those directions and add in some additional information.

Disclaimer: As always, I would recommend doing this on a test environment before attempting on a production environment. I also would only do this on a brand new search service application that has had no crawls run.

The first thing that we need is a registered managed account to run the search service. (You can do that in Central Administration -> Security -> Configure Managed Accounts).

The next thing we need to do is provision the search service application. I’ll provision it Microsoft’s way and use the Farm Configuration Wizard in Central Administration.

Farm Configuration Wizard in Central Administration

There are a couple of things to note here. Even though I’ve only selected the Search Service Application, the wizard will still provision the Usage and Health data collection service since that’s actually needed for the search application. Also, it will attempt to provision a new web application on port 80. (So if you receive an error about the port already being in use, then that is why).

Application Discovery and Load Balancer Service Application

search service application

Now that the search service application is in place, we can begin the steps to change the database names.

First, we need to make sure that the necessary pre-requisites are in place:

  1. The user account that you will be running PowerShell with needs to be in the “Administrators” group of the server that you will be running the PowerShell commands on.
  2. Your user account needs to be in the Farm Administrators group in SharePoint (You can verify this in Central Administration -> Security -> Manage the farm administrators group)
  3. Your user account has to have the database owner role on all of the search databases.
    (You can set this role in SQL Server Management Studio (SSMS) -> Security -> Logins -> right click and select the properties of your user account -> User Mapping -> Check the box next to each search database -> select db_owner -> OK)database owner role
  4. Your user account must also at least have db_creator and securityadmin roles in SQL.
  5. The search service account will need to have db_owner role on the Search Administration, Crawl, and Link databases. It will also need the SPSearchDBAdmin role on the Analytics Reporting database.SPSearchDBAdmin role
  6. The SQL instance will also need the max degree of parallelism set to 1.max degree of parallelism set to 1

Now that we have verified that all the necessary requirements are in place, we need to pause the search service application. It must remain paused until we’re finished renaming the databases. So it’s time to open a PowerShell console (whichever one you prefer) and run the following (Note: If your search service application is named differently, then you’ll need to make an adjustment to the name below):

$ssa = Get-SPEnterpriseSearchServiceApplication “Search Service Application”

Suspend-SPEnterpriseSearchServiceApplication -Identity $ssa

After the suspend command has completed, we can verify that the application is paused on the Search Administration page in Central Administration.

Search Administration page in Central Administration paused

Next up, it’s time to set the search databases to read-only. We can do this in SSMS by changing the properties on each database -> Options -> set “Database Read-Only” to True.

set the search databases to read-only

After they are all in read-only, they will look like this in SSMS:Read only SSMS

Now it is time to back up the databases. We can do this in SSMS by right-clicking the database -> Tasks -> Back Up.back up the databases

Rinse and repeat for each database. After the backups are completed, then it’s time to restore and rename the databases. This can be done in SSMS by right-clicking Databases – Restore Database. Here are the restore steps that I used:

  1. Select “Device” under the “Source” section.restore and rename the databases 1
  2. Add the backup file for one of the databases that was created earlier:restore and rename the databases 2
    restore and rename the databases 3
  3. Remove the GUID from the destination database:Remove GUID from destination database
  4. Go to “Files” and adjust the filenames for the data and log files as well by removing the GUIDs:adjust the filenames for the data and log files 1adjust the filenames for the data and log files 2
  5. Rinse and repeat for the remaining search databases. Here are some screenshots from after I finished these steps:Database screenshot 1Database screenshot 2

Now, we need to put the restored databases back into a write state by setting the “Database Read-Only” state back to “False” on each of the new databases:Databases in Write State

Now, we need to update the Search Service Application to point to the renamed databases. Below is the series of PowerShell commands that I used. I pulled these from the TechNet article and substituted in some variables. First, let’s set some values to those variables:

$dbServer = “” ## You will need to adjust this database server variable for your environment.

$searchAppName = “Search Service Application”

$searchDB = “Search_Service_Application_DB”

$crawlDB = “Search_Service_Application_CrawlStoreDB”

$linksDB = “Search_Service_Application_LinksStoreDB”

$analyticsDB = “Search_Service_Application_AnalyticsReportingStoreDB”

 

Next up, let’s swap the search administration database:

$ssa = Get-SPEnterpriseSearchServiceApplication $searchAppName

$ssa | Set-SPEnterpriseSearchServiceApplication -DatabaseName $searchDB -DatabaseServer $dbServer

 

This will swap the analytics database:

Add-SPServerScaleOutDatabase -ServiceApplication $ssa -DatabaseServer $dbServer -DatabaseName $analyticsDB

$temp = Get-SPServerScaleOutDatabase -ServiceApplication $ssa

Remove-SPServerScaleOutDatabase -Database $temp[0] -ServiceApplication $ssa

 

Then the crawl store database:

$CrawlDatabase0 = ([array]($ssa | Get-SPEnterpriseSearchCrawlDatabase))[0]

$CrawlDatabase0 | Set-SPEnterpriseSearchCrawlDatabase -DatabaseName $crawlDB -DatabaseServer $dbServer

 

Next up, the links store database:

$LinksDatabase0 = ([array]($ssa | Get-SPEnterpriseSearchLinksDatabase))[0]

$LinksDatabase0 | Set-SPEnterpriseSearchLinksDatabase -DatabaseName $linksDB -DatabaseServer $dbServer

Now let’s make sure the Search Service App is back online:

get-SPEnterpriseSearchServiceInstance -Identity $searchServer

Do {write-host -NoNewline .;Sleep 10; $searchInstance = Get-SPEnterpriseSearchServiceInstance -Identity $searchServer} while ($searchInstance.Status -ne “Online”)

Before we resume the search service application, let’s check the Search Administration page in Central Admin to make sure we didn’t miss any databases:

Check Search Administration page in Central Admin

Looks like we can resume the search service application:

$ssa = Get-SPEnterpriseSearchServiceApplication $searchAppName

Resume-SPEnterpriseSearchServiceApplication -Identity $ssa

After that has completed, we can see that the status is back to running:

Status back to running

 

Once this is done, Microsoft recommends rebooting your search server.

There is one more thing that needs to be pointed out: In the scripts above to swap the databases, only the reference to the Analytics database was actually removed from SharePoint. So if you check the Database Upgrade Status page, for example, then you’ll still see references to the older databases:

Database Upgrade Status page

You can remove these databases with the following PowerShell that is mentioned in Marcel den Ouden’s blog.

get-spdatabase | where {$_.name -eq ‘db_name’} | foreach {$_.Delete()};

Remove databases from Database Upgrade Status page

So at this point, if you already have a web application and site collection created, then it’s time to test the service application by running a crawl. Hopefully after the crawl completes, you will find some search results in your site as I have here:

Test service application by running a crawl

That should do it! Hope this tutorial helped!

2015-01-13T08:30:57+00:00 January 13th, 2015|

6 Comments

  1. Marcel den Ouden January 13, 2015 at 10:05 am - Reply

    Thank you for mentioning my blog!
    Best regards,

    Marcel den Ouden

  2. TD May 27, 2015 at 2:36 pm - Reply

    I have proceeded through your instructions, but when I attempt to run the
    Resume-SPEnterpriseSearchServiceApplication -Identity $ssa

    I get error:
    Resume-SPEnterpriseSearchServiceApplication : The search application ‘1a07303a-af9b-408c-956d-a1e6214c0ba7’ on server did not finish loading. View the event logs on the affected
    server for more information.

    Any ideas as to why this would fail here?

  3. Christian Heim July 22, 2015 at 5:05 pm - Reply

    Great article, thank you Jim!
    Just like TD mentioned in his response I had the same problem before resuming the search.

    After setting permissions on the search DBs for the search service account I was able to get search up and running.

    Reference:
    http://blog.sharepointsite.co.uk/2014/01/search-stops-working-and-ca-search.html

  4. Bernd Webster November 7, 2015 at 2:27 pm - Reply

    Hi, just following your howto. But have some issues, if MY account and the Search account should have DBO. How should that work? I can get it managed that only one user account has DBO.

  5. Arseny May 13, 2016 at 5:39 am - Reply

    Little comment. If we run $CrawlDatabase0.Name we’ll see GUID. If you want to remove GUID from Crawl DB name you can do it from SQL Server directly (Search Admin DB – Table MSSCrawlStores).

  6. FS March 8, 2017 at 8:26 am - Reply

    Fantastic article. Used this for SP 2016. TY very much.

Leave A Comment