Backup and Restore with Cloud services (Windows Azure Blob Storage Service) using PowerShell

The two main PowerShell cmdlets introduced in SQL Server 2012 to do backup and restore operations are Backup-SqlDatabase and Restore-SqlDatabase. These cmdlets have been further enhanced and new cmdlets have been introduced in SQL Server 2012 SP1 CU4 to allow backing up your database to Windows Azure Blob storage and restoring from there as needed.  In this tip we look at how to write PowerShell scripts for backing up and restoring a database to Windows Azure Blob storage. For more information click here.

Backup and Restore SQL Server databases with Azure cloud services and SMO

As DBAs, we don't only have the responsibility to backup databases on regular basis, but also to ensure that backup drives and tapes are secure so databases can be restored when needed. I heard that SQL Server 2012 supports backups and restores using a Windows Azure Blob Storage account.  How does this work and how do I get started using SMO? For more information click here.

Backup and Restore with Cloud services (Windows Azure Blob Storage Service)

As DBAs, we have the responsibility to backup databases on a regular basis, but we also have to ensure that backups are stored securely and safely, so databases can be restored when needed.  With the current method it is sometimes difficult to control that these backup files are readily accessible when needed.  I heard that SQL Server 2012 now supports backups directly to Windows Azure Blob Storage and restores can also be done directly from Windows Azure Blob Storage.  Can you tell me more about this new feature and advantages of using it over traditional backup options? For more information click here.

Partitioning in SQL Server - Part 2

When a table grows large or huge in size it becomes really difficult to load new data, remove old data, maintain indexes and queries involving this table, and runs extremely slow. In this situation, we can partition the table, which essentially breaks the table and its indexes into logically smaller chunks called partitions. Further, with a partitioned table, maintenance works, lock escalation settings and compression settings can be applied individually at each partition rather than on a single huge table. Not only that, Query Optimizer references only partitions needed to serve the query requests and eliminates the partitions that don’t contain data needed by the query during query optimization and execution, and also it uses parallelism to process multiple partitions in parallel. SQL Server 2008 introduced partition table parallelism for better performance and for better resource utilization (of modern multi-processors hardware). With SQL Server 2012, we are now allowed to even create up to 15K partitions on a single table.

In my last article of the series, I discussed what partitioning in SQL Server is, the different kinds of partitioning options available, why and when we should go for partitioning and all of the benefits partition table/index provide. In this article I am going to examine the different concepts of partitioning in SQL Server and provide a step-by-step guide on creating a partition table/index. For more information click here.

Partitioning in SQL Server - Part 1

With every new release of SQL Server, partitioning has reached new heights of improvement. For example, though we could create partitioned views for better manageability and scalability since SQL Server 7.0, SQL Server 2005 started with native support for Table Partitioning. SQL Server 2008 introduced partition table parallelism for better performance and for better resource utilization (of modern multi-processors hardware). With SQL Server 2012, we are now allowed to even create up to a 15K partition on a single table.

To learn more about Why, What, When and How of Partitioning in SQL Server click here.

FileStream and FileTable in SQL Server 2012

Data explosion brought a need to store both structured and un-structured data together in the database so that the benefits of the database system (like transactional support, backup and restore support, integrated security support, Full-Text Search support, etc.) can be leveraged. SQL Server 2008 introduced the FileStream data type to store unstructured data, such as documents, presentations, videos, audios and images, on the file system with a pointer to the data in the database.

Storage of un-structured data in FileStream improves the performance by leveraging the NTFS APIs streaming along with additional benefits of the database system.

SQL Server 2012 enhanced this capability even further by introducing FileTable, which lets an application integrate its storage and data management components to allow non-transactional access, and provide integrated SQL Server services - including full-text search and semantic search - over unstructured data and metadata. I am going to talk about these two new features in detail, for more information click here.

Getting Started with the New Column Store Index of SQL Server 2012

Column Store Index is a new feature in SQL Server 2012 that improves performance of data warehouse queries several folds. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request along with the newly introduced batch mode processing.

In my last article I talked in detail about the new Column Store Index, how it differs from regular indexes, and why and how it improves the performance of the same query by several folds if column store index is used, where it can be used and what its limitations are.

In this article I am going to take this discussion to another level and show how you can create column store index, how you can use index query hint to include or exclude a column store index, how the performance differs when using column store index vs. row store index and more, click here for more information.

Understanding new Column Store Index of SQL Server 2012

Column Store Index is a new feature in SQL Server 2012 that improves performance of data warehouse queries several folds. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request. This article discusses the new Column Store Index in detail, how it differs from regular indexes, why and how it improves the performance of the same query by several folds if column store index is used, where it can be used and what its limitations are, for more information click here.

Getting Started with Data Quality Services of SQL Server 2012 Using SSIS

In my earlier articles, I talked about the Data Quality Service (DQS) in SQL Server 2012, which helps ensure data integrity and quality by data profiling, matching, cleansing, correcting and monitoring overall status of the data cleansing process. We also talked about using the interactive Data Quality Client tool and in this article we will walk through how to do data cleansing in a automated mode using SSIS, for more information click here.

Creating a knowledge base and cleansing data using Data Quality Services in SQL Server 2012

This is third article of the series. In the first article I talked about data quality challenges and its source, then I talked about the Data Quality Service in SQL Server 2012 and its processes. In the second article, I talked about different components of DQS and how to go about installing and configuring DQS and then I talked, how DQS works. In this article, I am going demonstrate step by step how you can create a knowledge base and do data cleansing, for more click here.

Getting Started with SQL Server 2012 Data Quality Services

The amount of data is increasing on a regular basis and so is the responsibility to ensure the quality of data remains intact. Data quality issues consist of inconsistency, incompleteness, invalidity, inaccuracy, duplicates, etc. These data quality issues can arise from different sources like erroneous data entry, corruption in transmission or storage, data consolidation issues from different sources with different standards or formats, etc. These types of issues can lead to incorrect data analysis, data mining and reporting, which ultimately impacts the business. I have heard that SQL Server 2012 has a brand new feature called Data Quality Service (DQS) which greatly helps to ensure the data integrity and quality by data profiling, matching, cleansing, correcting data and monitoring overall status of the processes. Let's explore this feature in this tip series:

Getting Started with SQL Server 2012 Data Quality Services - Part 1

Getting Started with SQL Server 2012 Data Quality Services - Part 2

Snapshot Isolation Level in SQL Server - What, Why and How Part 2

Introduced in SQL Server 2005, Snapshot Isolation levels improve performance, however, there are some caveats to consider when using this feature. Some people use it frequently, as it minimizes blocking and improves performance/concurrency, without knowing its impact on maintaining versions in tempdb. Others stay away from it because of this extra overhead. Some people are confused by the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one variant where the other is needed or vice versa. In my last article (Snapshot Isolation Level in SQL Server - What, Why and How – Part 1) I talked about basic of isolation levels and snapshot based isolation levels and how they differ from each other in terms of performance and cost; in this article I am going to demonstrate how to use these isolation levels with examples; for more information click here.

Snapshot Isolation Level in SQL Server - What, Why and How - Part 1

Snapshot Isolation level was introduced in SQL Server 2005 and has been available ever since. Snapshot isolation levels improve performance but there are couple of things to take into consideration when using this feature. Some people use it frequently as it minimizes blocking and improves performance/concurrency without knowing its impact on maintaining versions in tempdb, whereas some people stay away from it because of this extra overhead. Some people get confused about the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one where the other is needed or vice versa.  In this article, I am going to discuss what snapshot isolation levels are, their variants, why and when we should use them and how we should start using this feature with examples, for more information click here.

Creating Indicator and Gauge Report in SSRS - SQL Server 2008 R2 - Part 2

SQL Server Reporting Services allows you to embed indicators and Gauges in your report to analyze the data and its state. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent state value of Key Performance Indicator (KPI).

In my last article (Creating Indicator and Gauge in SSRS - SQL Server 2008 R2 - Part 1), we discussed the details of Indicator and Gauge, and created a step-by-step tablix report with two levels of grouping, which  I will be using further to add different types of indicators and gauges to it, for more click here.

Creating Indicator and Gauge Report in SSRS - SQL Server 2008 R2 - Part 1

SQL Server Reporting Services provides several ways to analyze the data; a few of them are creating reports with indicators and Gauges. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent the state value of Key Performance Indicator (KPI, a measurable value which has business significance with a specific target or goal that indicates whether things are going good or bad). Indicators can be used in either dashboards or free-form reports but more commonly are used in tabular or matrix reports to visualize data in rows or columns. There are several types of indicators, to learn more about these indicators and how to use them click here.

Creating Chart Reports in SSRS SQL Server 2008 R2

SQL Server Reporting Services provides several ways to analyze data; one of them is creating Chart reports. With Chart reports you can analyze the aggregated information (either on small or large volumes of data) at a glance. But before you start creating Chart reports it’s essential to understand and prepare the data to be analyzed so that you can choose the appropriate chart type and create the report quickly and efficiently.

In this article, I am going to demonstrate how you can go about creating, modifying and beautifying the chart report easily and quickly, for more information click here.

Creating Map Drill Down Reports in SSRS - SQL Server 2008 R2

In my last article, I talked about creating map report based on ESRI shapefile. In this article I will be talking about creating a map report with drill down functionality, so basically I will be creating two reports as follows:

• Main report based on SQL Server Spatial data for showing countries wide sales
• Drill down map report based on ESRI shapfile for showing statewise sales for the selected country

The idea behind this article is to demonstrate how you can use a combination of sources of spatial data and create a map drill down report. Also I will explain how you can import ESRI shapefile to SQL Server as a spatial data type and use it in a map report; for more information click here.

SQL Server Analysis Services Best Practices for Performance Optimization - Part 4 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 and performance optimization techniques for Analysis Services Server Resources and Reporting Services; for more information click here.

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.