SQL Server Architecture

(0) Share

Report

Posted on by Community Member

Hi, we are a Company with 1500 employees. We want to implement Dynamics CRM 2016. Now, I have a question to the SQL Server Architecture. We have an separate RS Server for the Reporting Services and an separate Server for the DB-Instance (separation Front- BackEnd). On the DB-Server runs the Application-Database and in another Instance, on the same Server, the ReportServer-Databases. Do you see any Problems about the Performance? (Workload ReportServerDB and OLTP Database) Thanks a lot. Martin

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Suggested answer

JohnAnonymous 5,241 on at

Like (1)

Report
Copy link

Link copied!

Hi,

I think it depends on a lot more than just your configuration in servers. Other factors are also important, like the expected load, server processing power, memory, disk IOPS etc.

Was this reply helpful? Yes No
David Jennaway 14,065 on at

Like (1)

Report
Copy link

Link copied!
As per the previous reply, more factors are involved to be certain. Probably the most significant are:

How heavily the 1600 user use CRM. If for most of them it is their main application, then this is very different from most users only occasionally accessing CRM

The reporting load. If a lot of users frequently run a lot of complex reports, this will add noticeable load onto the system

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi, thank you for your answer. You're right, the server specifikation and other factors are also important. The server has 64GB RAM and 4 CPUs and running on Windows Server 2016 and SQL Server 2016 EE CU2.... I can't find a recommended server architecture for Dynamics CRM.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi, thank you. At the moment it is not our main application, but in the next few years it should be. Then we expect that about 1000 persons use the CRM, but it is improbable that all create reports at the same time..... I think in the beginning it is too much to install a separate SQL Server just for the Report Server Databases. or not? I don't know what is recommended for us and what is recommended for small or bigger environments.

Was this reply helpful? Yes No
Suggested answer

SergeyT 310 on at

Like (0)

Report
Copy link

Link copied!
I would put Reporting DB on the same SQL instance and installed SSRS on a separate windows machine and forgot about it. The reasoning behind this:

Premature optimization is the root of all evil

You can scale that windows machine separately

You can always backup\restore Reporting DB to separate SQL instance if in need

Since you just starting adopting Dynamics probably there are no millions records

Reports is not something users runs constantly in day to day routine (IMHO). There are views, charts, dashboards for day-to-day use in Dynamics. And they aren't run via SSRS.

You need great analytic? - take Power BI or any other BI tool and scale it.

Probably there will be separated DWH for yours BI solution

Was this reply helpful? Yes No
Suggested answer

JohnAnonymous 5,241 on at

Like (0)

Report
Copy link

Link copied!

I agree with Sergey in that the reporting server is probably not going to be used very often with a new implementation, unless you know up front that it's going to be very important.

PowerBI is far more user friendly for dashboarding and if you get very serious in using CRM, there's most likely going to be a DWH.

Was this reply helpful? Yes No