SQL Server AlwaysOn & Failover scenarios

SQL Server AlwaysOn Architecture
SQL Server AlwaysOn is recommended for high availability, zero data loss protection, automatic failover of multiple databases

Figure 1: SQL Server AlwaysOn between 2 SQL Servers

SQL Server AlwaysOn & Failover scenarios 
Case -1: When SQL Server A is down
It will switch to SQL Server B with AlwaysOn
SQL Server B will keep use the SAN B

Case -2: When SAN A is down
SQL Server A will be down
It will switch to SQL Server B with AlwaysOn
SQL Server B will keep use the SAN B

Case -3: When SQL Server B is down
It will switch to Server A with AlwaysOn
Server A will keep use the SAN A

Case -4: When SAN B is down
SQL Server B will be down
It will switch to Server A with AlwaysOn
Server A will keep use the SAN A

SQL Server - Considerations while opting SSAS cube for existing OLTP application

​The following points must be considered while opting for the SSAS cube:

  

1) Implementing the SSAS cube will change the architecture for data processing, data storage, & reporting.

2) Need to change the database modelling to dimensional modelling.

3) Need to change the database in the de-normalized form.

4) Developing a cube that will involve creation of calculated members for all the statistics defined in the reports/ portal.

5) If all the reports are using data sets written in T-SQL. All the datasets need to be changed to MDX queries.

6) Need to maintain a separate instance of SSAS.

7) Additional ETL for processing the cube.

8) Additional storage for aggregations.

The following recommendations must be considered while using the multi-dimensional cube over tabular modelling:

1) The multi-dimensional model is suggested when the volume of data is high. The tabular model is suggested when we have a smaller volume of data.

2) The multi-dimensional model is suggested when we require complex modelling or have a large dataset.

3) Actions, Writeback, Namedsets features are not available in the tabular model.

4) Tabular is a memory-dependent solution. The more memory, the better the performance. If you do not have enough memory, the tabular model will fail.

Recommendation for separate staging database

​Following are the reasons to recommend separate staging database:

1) The database log file does not contain the staging ETL operation resulting in a smaller sized log file

The size of the database & backup files will be smaller

2) If log/data files keep growing, we can see massive performance hits as page splits and non-sequential writes may occur

3) For high availability, we can avoid pushing stage data across the network

4) The recovery model for a separate staging database can be set to SIMPLE to avoid generating a huge log

5) The separate staging database can be easily moved to another instance or server for heavy data processing without affecting the reporting environment