Upgrading to SQL Server 2014: Step 2 – Planning For Reporting

When planning for upgrading our servers to SQL 2014, I not only had to plan for our production instances, but also for our reporting instance. Before planning our capacity needs, I needed to decide on how to get our production data into our reporting environment. How I did that will be the subject of this post. 

Extending our HA/DR strategy with our Reporting Strategy

For our reporting solution, I chose to build 2 other SQL Server instances: 1 instance for staging and 1 for reporting. For staging our production databases we wanted to create secondary replicas using AlwaysOn Availability Groups (AGs). In my last post about planning our HA/DR strategy, I gave a high level overview of the HA/DR choices I made. Extending this overview with our reporting strategy will look like this:

Planning HADR with Reporting

In this setup, node 1 and 2 form a Windows Cluster with a SQL Server Failover Cluster Instance (FCI) installed. The primary replica databases in the Availability Group, our production databases, are going to live here. For High Availability, if node 1 or node 2 fails, the FCI automatically fails over to the other node. For Disaster Recovery, when the complete primary location is down, we can failover the Availability Group to node 3. For Reporting, we don’t want to pull our data directly from our production databases, hence the Staging server where I’m also going to configure secondary replicas in the same Availability Group. Via Extract Transform and Load (ETL) processes we can then pull data from the databases on the Staging server into our Reporting database on our Reporting server.

Read Only Access

With this setup, I’m also able to grant read only access to our production databases via the secondary replicas on the Staging server. For example to developers – something they’ve always wanted (recognize your developers wanting access to production data?). If you wonder why, ask them this: “For developers it’s important to have access to production data, because…”. I did that and the answer I got was:

For developers it’s important to have access to real time production data to solve production issues quicker and prevent them from happening. Having real time production data available developers will get feedback earlier on their code and hypotheses.

Capacity planning

I started this series stating that it is advisable to collaborate with your colleagues from the operations team, especially when they are planning to buy new hardware for your new database servers. Before you do that, you’ll have to know what you are going to build to be able determine you capacity needs. That starts by determining the RPO/RTO goals and choosing a HA/DR strategy. If you’re planning to build a database server for reporting, you’ll have to take that into account as well. Now we know what we want, we can plan for capacity. More on that in the next post in this series!


Leave a Reply

Your email address will not be published. Required fields are marked *