Move SQL Server configuration store database?

Question

We are required to change the server that our SQL Server configuration store is on. Our plan is to either use backup/restore or detach/attach method to move the database.  I have been unable to determine how to update the database connection string that AppFabric cache is using.  We are not using WF hosting.  Searching on the server, I have found the connection string in the registry and in config files.  Is there a way change the connection string without resetting the configuration and running the configuration wizard again?  Running the wizard again requires special database permissions which are difficult to obtain from the DBA.  The DBA does not want to configure our application for us.  It would be great if the connection string could be changed using PowerShell or WMI script.

Answer 1

Phil,

Could you use the same SQL Alias ? Then you may not need to reconfigure anything. Stop the cluster, move the db, start the cluster.

---

Mohammad Faridi.

Answer 2

Not through, but can be done with configuration cmdlets (DistributedCacheConfiguration Powershell module).

Cache configuration information that resides locally on the machine can be modified through Add/Remove-CacheHost .

[ Config info/permissions residing in SQL is modified through Register/Unregister-CacheHost ]

Answer 3

Yes, I would assume using an alias would work.  I had not thought of that.  However, it would still be good to be able change it where ever it is stored.  I would have created an alias for the configuration database from the start.    Off to running Cliconfg.exe on each server.

Answer 4

I do know that the SQL connection string is stored in C:\Windows\system32\AppFabric\DistributedCacheService.exe.config file. I actually use the following script to get the configuration: 

[xml] $config = get-content "$([System.Environment]::GetFolderPath("System"))\AppFabric\DistributedCacheService.exe.config"
$clusterConfig = $config.configuration.dataCacheConfig.clusterConfig
$clusterConfig.connectionString

I use that for example for getting cluster info:

Get-CacheClusterInfo -Provider $clusterConfig.provider -ConnectionString $clusterConfig.connectionString

Now since the connection string is in that file I would say that it should be possible to change it right there and then restart the AppFabricCachingService. I have never tried that so I don't know if it works but it might worth a try.

 

 

---

David Pokluda (MSFT)
http://blog.pokluda.com

Answer 5

We have the exact same problem. When we try to move the cache database to another sql server, the service wont come up again (it stops shortly after restart). We changed the config on disk and the connection string using (DistributedCacheConfiguration Powershell module) Get-CacheConnectionString.

Any help is appreciated.

Answer 6

You just need to stop caching service, modify registry value

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\AppFabric\v1.0\Configuration\ - ConnectionString

And modify connection string in C:\Windows\System32\AppFabric\DistributedCacheService.exe.config file

And then start caching service.

 

There is no way to do it with built-in cmd-lets.