How to setup load balanced SSRS Servers for CRM 2016


While not very common, there are times when a customer or client requests two servers for the CRM SRS data connector/SSRS to be installed and have load balancing configured for the servers. The idea here is failover; when one server hosting SSRS crashes, all incoming requests are routed to the secondary server. There are a couple of ways to do this, but the following method is the most straightforward and simplistic way.

1.    Create a Virtual IP(VIP) to route to each SSRS server.

In order for this method to work, a virtual IP will need to be created. The virtual IP will accept incoming data packets, then route the requests to each of the IP addresses of the physical servers that will have SSRS installed.

2.      Install and configure SSRS on each server.

Install and configure SSRS on each server as you typically would by pointing to the SQL server and/or instance where you would like the report server databases to be located.

3.    Install the CRM SRS data connector on each server.

Grab the installation files for your respective version of CRM and install the data connector on both servers. Install the data connector as you would for a typical CRM deployment.

4.      Configure the host file on each SSRS server.

On both SSRS servers, open the host file: C:\Windows\System32\Drivers\etc\hosts. Edit the host file by adding the IP and DNS name of the virtual IP created in step 1:

host

 

 

 

 

 

 

 

 

5.      Add the BackConnectionHostNames registry key with the server name and FQDN.

Open Registry Editor on one of the SSRS servers, and locate the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0.

Right click MSV1_0, point to New and then click Multi-String Value. Type BackConnectionHostNames, then press ENTER.

Right click BackConnectionHostNames, then click Modify.

In the Value data box, type the host name of the VIP and FQDN of the VIP, and click OK.

multi

 

 

 

 

 

 

Repeat these steps for the other SSRS server.

6.      Add hostname and URL root values.

On one of the SSRS servers, make a backup of the reportserver.config file located here: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer.

Right click the original reportserver.config file and choose edit.

Modify the hostname and URL root tags of the .xml file located under the service tag by adding the FQDN of the VIP as shown below:

reportserverconfig

 
 
 
 
 
 
 
 
 

Save the file after making the changes.

Repeat these steps for the other SSRS server.

7.   Restart both SSRS servers.

8.   During the import or new creation of an organization in CRM, specify the name of the VIP.

Use the VIP created in step 1 when prompted for the reporting services URL during a new org creation or an org import:


A Nice CRM Story!


We recently moved to Tampa this year and have started to build a new home. Imagine my surprise when the home builder, Standard Pacific tells me they have our information in CRM.
Excitedly, I ask which brand but they didn’t know. About 30 seconds later it was Dynamics CRM 2011, very well integrated with custom build your home aspx pages that created the opportunity!

I’m so proud to be a customer and support a dynamics CRM company. Of course they had some small complaints, maybe I can barter for some fixes for more options in our home! It also turns out that they are a Click Dimensions Customer! www.clickdimensions.com.

Just wanted to share with everyone including the product team! Btw If you’re looking for a new home, please contact Lizz Owen at LOwen@stanpac.com or 813.494-8105! She was fantastic!

lizz


Upgrade from CRM 2011 to 2013 fails with “Subquery returned more than 1 value. Not Permitted CRM 4.0″


We recently stumbled upon an issue when upgrading a customer from CRM 4.0 to CRM 2013. As is required, you must first upgrade the 4.0 database to 2011 and then from 2011 to 2013. The 4.0 to 2011 upgrade succeeded without error but threw an error when going from 2011 to 2013. 2013 was on update rollup 2. The error thrown was:

Error| Installer Complete: OrganizationUpgrader – Error encountered
14:56:47| Error| Exception occured during Microsoft.Crm.Tools.Admin.OrganizationUpgrader: Error.ActionFailed Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction
InnerException: System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

We upgraded the database from 4.0 to 2011 one afternoon and then tried going from 2011 to 2013 the following morning. As it turns out, CRM 2013 contains a query that checks to see if an organization is a new organization or an upgrade by checking the CreatedOn field of the system solution. It does this by seeing if the CreatedOn date/time is within one day (24 hours) of the current time. If the CreatedOn date of the System Solution is within 24 hours, it is considered a new organization. Here is the query:

IF EXISTS (SELECT * FROM SolutionBase WHERE SolutionId=’FD140AAD-4DF4-11DD-BD17-0019B9312238′
AND CreatedOn >= DATEADD(day, -1, GETUTCDATE()))
BEGIN

(This query goes on to Update prvAppendToOpportunity depth to global on sales person role and update prvAppendToQuote, prvAppendToOrder, prvAppendToInvoice to local on sales person role.)

When the 4.0 database is upgraded to 2011, it sets the CreatedOn date/time to whatever the UTC date/time is of that upgrade. So when going from 2011 to 2013 it sees the new date/time resulting from the upgrade to 2011 and considers it a new organization and errors out on the above query. Below is the query that can be run to check the CreatedOn date of your 2011 database.

crmquery

The easy solution to this is to just wait 24 hours between the upgrade stages but if you are in a time crunch or minimizing down time for a go-live, you can update the CreatedOn date/time of the system solution directly in the 2011 database. Remember that the time is in UTC, changing the date back one day should be easier than trying to figure out the time difference in your time zone. However, keep in mind that updating fields directly through SQL is not a change supported by Microsoft so this is done at your own risk! Not sure if this issue is isolated to just Update Rollup 2 or if this occurs with 2013 RTM, Update Rollup 1, or Service Pack 1 yet.

Thanks to my young CRM Jedi Gage Pennisi for putting the details together!