Configuring SSIS,SSRS, and SQL Server - three server model

Question

I have a requirement for a third party vendor application which requires SSIS and SSRS. The vendor had suggested this 3 server model, and my management is going ahead with the same. Three VMs are being provisioned, one each for SQL Server, SSIS, and SSRS.

I want to know in what ways I can configure SSIS and SSRS to work with the SQL Server. Also, what would be the best way to create SSIS packages? Because, if the packages have to be created on the dedicated instance, I still need to create a local instance of the database engine on the SSIS server.

I need some guidance as to how I can configure SSIS and SSRS in conjunction with the SQL Server instance in the third VM. I am fairly new to these two areas. I am going through these two links:

1. Configuring SSIS

2. Configuring SSRS

Please let me know if another URL would provide detailed step by step information.

Also, would the three servers have to be in the same cluster?

The system is spread across continents hence we expect a lot of users in the long term. The way it was planned was to have separate instances of SSIS and SSRS, and they point to a database in an Always On setup which stores the reporting information.

Answer 1

What you are asking about is broad architecture rather than specific issues. I dont believe there will be a single document that gives you a step by step guide.

If your vendor is making this reccomendation you could ask them to explain thier design with at least some basic documentation and diagrams that help you understand the layout.

If you are unfamiliar with either of these products you have a big learning curve ahead of you. MSDN is probably the best place to start. If you have specific issues then you should google those.

Assuming your main database is already running, i would start by installing an instance for SSRS. Then work through designing and deploying a report that connects to your source db.

Then repeat for SSIS. Set up the server and design and deploy a basic package.

A key part of learning about both SSRS & SSIS is learning about datasources, connection managers and connection strings.

If you are unfamiliar with these concepts and your company is expecting you to install configure and develop with these products they might be expecting too much of you and I suggest that you ask for assistance.

Answer 2

What the vendor is suggesting is a common practice for large systems/applications that require database engine, ETL, and reporting. Each of those components will suck up resources based on their need, so trying to have those run on one system is not a good practice (but it does depend on workload).

Each component can run on a dedicatd server, but do require each server to be licensed. The exception will be SSIS and will depend on what deployment module you are going to use. With project deployment module you will utlize SSIS Catalog, this requires a local database instance to run on the same server as SSIS binaries. With SSRS, you simply decide which server you want the SSRS databases to be created on, they do not have to be the same server as SSRS. I would likely put the SSRS databases on the server with SSIS instance (if you are going for project deployment model.

Your comment notes AG configuration and setup. If you are referring to SSRS and SSIS databases being part of an AG you can refer to the documentation links below. If however, you are talking about these components interacting with user databases that are part of an AG, that is fully supported. You just need to ensure you are connecting to the listener name for your AG.

• SSIS in AG setup
• SSRS in AG setup Can already tell you the SSRS database can be put in an AG, but SSRS services do not support automatic failover.

In regard to the 3 servers and the same cluster, it will depend on what type of HA is required. In most cases you could have a 3 replica AG where SSRS is reporting on the secondary replica (read-only routing) instead of the primary replica. This is common with OLTP ssytems that have reporting requirements. For that specific requirement will be purely based on the vendor and your business owners requirements for HA.

There is documentation on this type of architecture but not in a single document. Either way, if you are not even remotely familiar with these technologies that documentation is not going to help understanding the concepts. Which you need to understand in order to support it and ensure you are deploying each component properly. You need to tell your management they need to bring in another resource (hire DBA or reach out to 3rd party service that offers DBA support).