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.

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.

Getting Started with SQL Server Report Builder 3.0

SQL Server Reporting Services (SSRS) has a report development/authoring tool called Report Designer (Business Intelligence Development Studio) for creating managed reports. Though Report Designer has an intuitive interface for creating simple to complex reports, it can be a little complex for business users who want to create ad-hoc reports. SQL Server Reporting Services has a separate tool called Report Builder (separate downloadable) created especially for business users to do ad-hoc reporting.

Report Builder 1.0 first came with SQL Server 2005 and has grown up with each subsequent release. We are going to deep dive with Report Builder 3.0, which comes with SQL Server 2008 R2. For more information click here.

Understanding Sequence Object in SQL Server Denali

Until SQL Server 2008 R2, the Identity column was used to generate sequential numbers to identify records in a table, mostly used as a primary/foreign key. The scope of the identity column is the table on which it has been created, and the next sequential number is created when the DML statement is executed. But what if you want to have sequential generation of numbers across tables (instead of tying the numbers with just one table), and you want to have the next sequence number even before execution of the DML statement? SQL Server Denali has a new feature called Sequence object for these purposes, while retaining Identity column functionality too, for information click here.

SSIS Package Deployment Model in SQL Server 2012 - Denali

Deployment has always been a challenge for SSIS developers to deploy packages. SSIS developers are envious of SSRS/SSAS developers as they have an easy way to create a single unit of deployment (deployment package) that contains everything needed for the deployment. The good news is the inclusion of the SSIS Package Deployment Model in SQL Server 2012 code name Denali. In these articles I cover what it is and how to get started to simplify your SSIS package deployments.

SSIS Package Deployment Model in SQL Server Denali (Part 1 of 2)

SSIS Package Deployment Model in SQL Server Denali (Part 2 of 2)

SSIS Enhancements in SQL Server 2012 - Denali

I heard there are couple of enhancements in SQL Server Denali for SSIS. What are these enhancements and how does improves the usability, productivity, manageability and performance? Check out this articles to learn more.

SQL Server Denali SSIS Enhancements Part 1 of 2

SQL Server Denali SSIS Enhancements Part 1 of 2

Short-cut keys for commonly used tasks in SSMS - Part 2

We often overlook different SSMS shortcut keys which provide a boost in DBA and Developer productivity. In the second tip of this series (SQL Server Management Studio keyboard shortcuts - Part 1), I am going to further explain shortcut keys for managing Intellisence, debugging, running your code and many more...click here

Short-cut keys for commonly used tasks in SSMS - Part 1

As responsibilities are growing every day, a DBA or developer needs to improve his/her productivity. One way to do this is to use as many shortcuts as possible instead of using your mouse and the menus. In this tip we take a look at common tasks you may perform when using SSMS and the associated shortcut keys, for more details click here.

Understanding SQL Server Net-Libraries

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? SQL Server provides different Net-Libraries, known as Dynamic-Link Library (DLL), to let clients communicate with SQL Server using different network protocols. These Net-Libraries specify which networking protocols to use while exchanging data back and forth between SQL Server and the clients. To learn more about it click here.

Understanding SQL Server Net-Libraries

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? To learn about it click here.

SQL Azure - Getting Started With Database Manager

The Database Manager for SQL Azure is a lightweight, easy to use, thin web-based database management tool for connecting to a SQL Azure database and managing different database management tasks like; creating and editing tables, views and stored procedures, editing table data, writing and executing T-SQL queries, etc...for more information click here.

SQL Azure - Creating backups and copies of your databases

As a DBA you always followed a practice to back up your database (or take a snapshot of your database) before making any changes so that you can revert to your old database state if something goes wrong. Also to setup a development or test environment you use a backup of your database and restore it in the respective environment. If you are moving to SQL Azure, what would you do in these cases as backup / restore and database snapshots are not supported as of now? For more details, click here.

Generating SQL Server Test Data with Visual Studio 2010

As a database developer or tester sometimes you need to have production like data in your environment for your development or testing, but you cannot have the production data because of security and privacy issues. So how you can generate test data or replicate similar data as in production for your development or test environment, for more details click here.

Database Unit Testing with Visual Studio 2010

As a database developer you always want to ensure stored procedures, UDFs (User Defined Function) and triggers perform as expected. And more importantly you want to ensure that a change in an existing SP, UDF or trigger does not break the functionality. That is to say, you want to have smooth and fast regression testing for your database code. How would you do that, how would you write database Unit Test Cases (UTC)? In this article we cover how you can do this in Visual Studio, click here.

Register and Delete Data-tier Applications in SQL Server 2008 R2

SQL Server 2008 R2 allows its databases to be registered as Data-tier Applications In-Place; in other words without creating a DAC package and then deploying it. You can delete a registered Data-tier Application from SQL Server 2008 R2 instance in the same way. There are two ways to register and delete a Data-tier Application i.e. using the wizards available in SQL Server 2008 R2 Management Studio (SSMS) and using PowerShell commands, though in this article I will be talking about using these wizards, click here for more details.