Tuesday, March 27, 2012

Creating a Map Report in SSRS - SQL Server 2008 R2

SQL Server 2008 R2 brought several new features into the SSRS (SQL Server Reporting Services) arena. In the data visualization category, we now have three additional ways to display and visualize/analyze data in the reports:

  • Sparkline and data bars – Sparkline and data bars are normally used inside tables and matrices to analyze the trend and series and compare them with each other.
  • Indicators – If you are aware of KPIs, the concept of indicators is not new to you. As the name implies, indicators have icons to represent trends (up, down or flat), progress state, conditions.
  • Maps – It allows you to create maps or maps layers to let you visualize data against a geographic background.

In this article, I am going to demonstrate how you can create map reports to analyze your data against a geographical background and then in the next article I will be talking about creating a map report with drill down functionality, click here for more information.

SSAS - Best Practices and Performance Optimization - Part 3 of 4

In this tip series we have been discussing various techniques that can be used to optimize your SQL Server Analysis Services (SSAS) environment. In this segment we look at best practices for performance optimization for your cube design and development, for more click here.

SQL Server Analysis Services Best Practices for Performance Optimization - Part 2 of 4

In the first part of this series we looked at processing performance, query performance and hardware resources for your SSAS environment. In this tip, I am going to share best practices and performance optimization techniques for source system design and network settings for your Analysis Services environment, for more click here.

Sunday, March 4, 2012

SQL Server Analysis Services - SSAS - Best Practices and Performance Optimization - Part 1 of 4

There are several aspects that can take a toll on performance for your Analysis Services cubes. Some problems could be related to the source systems, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series, I am going to talk about some of the best practices which you should consider during the design and development of your Analysis Services cube and some tips which you can follow to tune your existing environment if it is suffering from performance issues, for more information click here.

Managing and Connecting to AlwaysOn Availability Groups

There are several ways to effectively manage and monitor availability groups, replicas and databases in SQL Server 2012 but the most efficient is the inbuilt dashboard which displays information in very a intuitive way for high-level monitoring and management. In addition, you can also use the newly introduced catalog views and dynamic management views, extended events as well as system performance counters, for more information click here.

Configuring and Creating An AlwaysOn Availability Group in SQL Server 2012

In the previous article on AlwaysOn Availability Groups in SQL Server 2012, we looked at The Environmental Setup for An AlwaysOn Availability Group. In this article we turn our attention to the creation and configuration of the Availability Group. For more information, click here.

SQL Server 2012 AlwaysOn Availability Group Environmental Setup

Continuing from AlwaysOn Part 1 the series continues with a step-by-step guide to setting up the environment for an AlwaysOn availability group. In this article we will break down AlwaysOn environment preparation into three steps :
1. Installation of SQL Server 2012 on each node/replica/server.

2. Installation of the failover clustering feature on each node/replica/server.

3. Creation of a failover cluster and joining all nodes/replicas/servers to the cluster.
For more information click here.

Using The AlwaysOn Feature of SQL Server 2012

Prior to SQL Server 2012, SQL Server had several high availability and disaster recovery solutions for an enterprise’s mission critical databases such as failover clustering, database mirroring, log shipping or combinations of these. Each solution typically has a major limitation, in the case of failover clustering for example, its configuration is very tedious and complex and you arguably have single shared storage or single point of failure. Database mirroring is relatively easy to configure in comparison with failover clustering, but you can have only one database in a single mirroring setup and you cannot read from the mirrored database. Log shipping does not provide automatic failover (higher availability) though it be used for disaster recovery with some expected data loss.

SQL Server 2012 (Code name SQL Server Denali) introduces a new feature called AlwaysOn which combines the best of failover clustering and database mirroring and overcomes major of the limitations imposed in failover clustering or a database mirroring setup, for information click here.

New User-defined Server Roles in SQL Server 2012

As we all know, we have fixed server roles (total nine) in SQL Server and if we make a group of DBAs a member of sysadmin, so that they can query DMVs and run traces/profile, we are actually giving them more than the permissions needed. Being part of sysadmin, they will have complete control on the instance. Unfortunately there is no way to change these fixed server roles to limit/give more permissions than needed in earlier versions of SQL Server. This means there is no way to separate DBAs into groups according to their roles, other than those that are available as fixed server roles. Starting with SQL Server 2012, apart from using the fixed server roles, we can also create user defined server roles and assign only server level/scope permissions needed to this role, for more information click here.