What’s new with Microsoft SQL Server 2008 r2

Microsoft released their product SQL Server 2008 r2 in 2010. As it is usual with their products, MS SQL Server 2008 R2 also has an Express edition that is available for evaluation and use in small systems. Microsoft SQL Server 2008 r2 is available in Standard and Enterprise editions. Having tried out the SQL Server 2008 r2 Express, you would eventually graduate to either the standard or the Enterprise editions depending on your actual needs.  Two other special editions also were released with the MS SQL Server 2008 R2. These are the SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse. These two special editions were introduced to meet increasing demands for datacenters and parallel data warehouse applications (Business Intelligence applications).

As with other versions, Microsoft SQL Server 2008 R2 Express edition is supplied free by Microsoft.  This facilitates your team becoming familiar with the database product, get used to developing web based and desktop applications, etc. You will need the help of Microsoft Visual Studio and Visual Web Developer Express.

Some features added over the MS SQL Server 2008 R2 include the following:

  • A Master Data Services that helps in master data management
  • Multi Server Management, this is a feature that helps manage multiple instances of the server
  • Relational databases, reporting services, analysis and integration services are also managed by the multi server management feature

For getting started with the 2008 R2 you need to download the Express edition first. It is fairly simple to download and install. It is free to use, but you will need to get the product registered with Microsoft. Your team can start getting familiar with it right away. When the team gets into applications development activities, there is another issue you need to take into account. All such database applications need to have the database as part of the application and then the question is if you can re-distribute the database to a third party (your customer). This too is free for the Express edition, but you will have to apply and get the right approved by Microsoft.

The so-called core editions of Enterprise and Standard are the most popular ones of the SQL Server 2008 R2 product.  The SQL Server 2008 R2 Standard is positioned as the database tool for data management as well as business intelligence for departments and also the small and medium business segment. This configuration is supposed to take up minimum of IT resources. Some new features in the Standard edition are:

  • Backups could now be compressed significantly for faster backups
  • The SQL Server 2008 R2 could become a managed instance in applications that use multiple servers and need to manage them
  • Support for processing of complex events

The SQL Server 2008 R2 Enterprise is capable of delivering a data platform but provides the user a secure, highly available and scalable product. Business intelligence features are supported. Some new features in the Enterprise edition include:

  • Complex event processing support
  • Mater data service to make sure of data consistency
  • Support for SharePoint collaborative work
  • Up to 25 server instances could be managed with the available multi-server management

The Microsoft SQL 2008 R2 also offers a free Compact edition for applications on mobile devices besides desktop and the web.

Additional Resources on SQL 2008

  1. Sql server 2008 Tutorial
  2. Download SQL Server 2008 Management Studio
  3. Related info on MS SQL 2008



SQL Server Management Studio 2012 – What is it?

SQL Server Management Studio or SSMS is a product that is a part of Microsoft’s SQL Server 2012 product and has been available from the time the SQL Server 2005 came out. This is a component that helps manage all the parts of the SQL Server. SQL Server Management Studio 2012 can do the following for all the software modules in the server:

  • Configuring
  • Administering
  • Managing

SSMS is a crucial product and a critical piece in managing the database product; it is necessary to become familiar with the product first. Starting with the SQL Server Management Studio Express studio then seems to be a prudent approach. Getting the express version of the SQL Server, which includes the Management Studio could be downloaded from the Microsoft site. Installing the product should not create any problems. You should invest in a regular version after your team is comfortable with the SQL Server Management Studio 2012.

The SQL server Management Studio is like the control center for the product and helps you manage the features of the major modules of the product. MS has their products available in different configurations. The express version is usually a free product that helps users get familiar with the tool before plunging in into larger configurations that are priced. The SQL Management Studio 2012 product download module includes a complete tutorial. The first step for familiarization should be to study this guide. You are provided an access to the user community. This can help with any queries you may have regarding the product, its installation and configuration.

The SQL Management Studio provides the following features that will help manage the server:

  • A minimal database engine
  • An integrated environment to let you work with the product, and any scripting necessary
  • Data analysis services
  • Reporting services

The Microsoft SSMS 2012 provides an activity monitor. It is possible to schedule actions at different times and to have multiple tool open at the same time. The SQL Server Management Studio can monitor these activities. If you needed an uncluttered and reduced higher level view, it is possible to apply filters.

The SQL Management Studio has an object explorer that can provide access to all types of objects within the server environment. Access to the SQL server agent for administering them in a replicated environment, filtering of schema, name or date is available. The SSMS will let you filter parts of these objects too. Objects inside the database could be populated asynchronously. If necessary, the objects could be filtered with the metadata associated to the objects.

Scripting capabilities keep getting updated with the server version releases. This is supported with templates for quick development; script editing could be off-line, even when disconnected from the server and a source control tool for managing the script sources.

SQL Server Management Studio capabilities could be extended, given the way it is built. However, the Microsoft does not promise any support for extensions users may build. No official documents that may help in such developments are also published. This policy is so strict that when you seek customer support, you need to remove any such extensions if you have any installed.

SQL Server Management Studio – Related Links




Using SQL Server 2012 Agent to create a Database backup SQL job

If you are a SQL server DBA at your company, scheduling jobs and automating Admin tasks is a common practice in your daily life. Of course as the number of SQL servers and the underlying databases grow, this vital role for a SQL DBA can quickly get out of hand. This is where SQL Server 2012 Agent can come to your rescue.
SQL Agent is a separate service in SQL server 2012 DBMS. It has the ability to setup and schedule automated jobs that can run on a predefined schedule. This can include DBA things like backing up your databases on a regular intervals, running import and export jobs, executing a predefined Transact SQL scripts and even deploying PowerShell commands.

Let’s go ahead and dig deeper into some of these components

A visual representation of the MS SQL Agent is as follows.



Create a backup job for Northwind database

We are going to create a backup job for Northwind database using SQL Agent. First make sure the Agent service is running, if not right click on SQL Server Agent and choose Start. Next do this:

Jobs > New Job

You will get a new dialog box. Enter the following info for job:

Name: Backup Northwind
Category: Database Maintenance

Next go to Steps in the left pane. Then click on New in the middle pane. Basically you are creating a job step that will run the backup command for your database.

For Step name type “Run SQL”
Next go ahead and enter the following SQL code:

 TO DISK = N'C:\SQLBackups\Nwind.bak' WITH NOFORMAT, INIT, NAME = N'Northwind-Full Database Backup',

You always want to parse the sql code and test it. When we ran out SQL command, we got the following output which means that it WORKS!

11 percent processed.
 21 percent processed.
 31 percent processed.
 41 percent processed.
 51 percent processed.
 61 percent processed.
 71 percent processed.
 81 percent processed.
 91 percent processed.
 Processed 560 pages for database 'Northwind', file 'Northwind' on file 1.
 100 percent processed.
 Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1.
 BACKUP DATABASE successfully processed 562 pages in 0.410 seconds (10.690 MB/sec).

Here is what the new job step looks like in our SQL Server 2012 Agent instance.



Generally you should enable logging which means track execution of your SQL jobs.

Click on Advanced in the left pane

Go ahead Check Log to table and make sure to select Append output to existing entry in table

This basically will provide forensics (big word for SQL geeks) on job executions as time goes on. Btw you can also write this to a file on a shared drive. I actually prefer that option over the Logging table. Click Ok. We can certainly add alerts and a recurring schedule to this. For now lets go ahead and click OK to finally create the sql job in SQL Server 2012 Agent.

Now we are ready for the million dollar question, is this going to work??

Let’s find out by manually executing this job. Under Jobs folder find Backup Northwind. Right click on it and select

Start job at Step

Since we have only one step, the job should start doing its magic. If you did everything correctly, you should get this nice dialog box.

Of course you never assuming anything as a SQL Server DBA and always verify the results. We are going to browse to the folder location and look for the backup file. Here we go:




Yep its there, BINGO!

Related topics to SQL Server 2012 Agent

Other important things SQL Server 2012 Agent can do are the following:

Operators: An operator in SQL server is someone who can be notified or alerted when something goes wrong in SQL server. And it can also be used as FYI information when an important SQL job completes.

Alerts: Alerts are notifications. These are customized SQL alerts based on error messages or Severity levels. Think off an alert as a heads-up which can then in turn execute a job or notify an operator.

Proxies: SQL Server 2012 Agent proxies are specific accounts with particular permission so that you can have fine-tune control on your SQL server. This will minimize any related security risks.

Error logs: This is the placeholder for any execution related info that is tied to a SQL server job or its underlying job steps. Basically this keeps a chronological order of all the executions with different messages that get generated as the job is executing. Def helpful for troubleshooting purposes


Delete all the user tables in SQL Server 2012

At times we need to delete all the user tables in a SQL Server 2012 database. Why do we need to do that? This may happen if:

  • The data is used for analysis or reporting
  • You are reloading tables every night via a batch
  • There could be application development or testing
  • We have a need to delete objects for a certain schema


So this means we have to keep the database intact, just need to drop the tables. There may be other objects like stored procedures, views, user logins and even SQL jobs associated with the database. How do we do that, I mean keep the database but zap all the user tables. Well there are really two options. One is to do it the “old fashioned” way, delete a table one at a time. The other is more elegant and uses TSQL to delete all the user tables in SQL Server 2012. Since we also belong to the Lazy DBA Association, we are going to use TSQL to achieve the end result. We will be doing the following:

  1. Create a temp table
  2. Insert the list of tables into this temp table
  3. Loop through list of tables and zap them one at a time

Note; Make sure the Referential Integrity is disabled, otherwise this will not work


use Northwind_test

–declaration of variables

@init_count int,
@rec_count int,
@total_count int,
@table_name varchar(1000),
@sql_stmt varchar(1000)

–create temp table #tmp_tables, more info in another script

create table #tmp_tables
(id int identity(1,1),
tablename varchar(50)

–insert into temp table #tmp_tables

insert into #tmp_tables (tablename)
FROM information_schema.tables
where TABLE_TYPE=‘base table’

set @init_count=1
select @total_count=count(*) from #tmp_tables
set @rec_count=@init_count

print @init_count
print @rec_count
print @total_count

–while loop to go through the tables

while @rec_count < @total_count

select @table_name= tablename from #tmp_tables where id=@rec_count
select @sql_stmt=‘drop table ‘ + @table_name
–print @table_name
print @sql_stmt
exec (@sql_stmt)

set @rec_count=@rec_count + 1

drop table #tmp_tables
/* Sample output

drop table dbo.Employees
drop table dbo.Categories
drop table dbo.Customers
drop table dbo.Shippers
drop table dbo.Suppliers
drop table dbo.Orders
drop table dbo.Products
drop table dbo.Order Details
drop table dbo.CustomerCustomerDemo
drop table dbo.CustomerDemographics
drop table dbo.Region
drop table dbo.Territories
drop table dbo.EmployeeTerritories
drop table dbo.Customers_All



Overview of SQL Server Management Studio

SQL Server Management Studio aka SSMS can be defined as an environment that is integrated for managing the SQL Server Infrastructure. It is a combination of tools that can be used for configuring, monitoring and administering the SQL Server.  SQL Server Management Studio also makes available tools for deployment, monitoring and up-gradation of the data tier components like data warehouses and databases that are brought into use by applications and construct queries and corresponding scripts.

Benefits of SQL Server Management

There are a number of benefits of SQL Server Management Studio, the most important being that it is a comprehensive utility that manages SQL Server objects. It combines a graphical interface and capabilities related to scripting. It can also be used for managing Database Engine, Analysis Services, Integration Services, and Reporting Services

Features of SQL Server Management Studio

SQL Server Management Studio supports a number of highly developed features. The general features of the SQL Server are that it is able to support most of the administrative functions and an environment that is integrated and single allows effective authoring and management. There are dialogs that for managing various aspects like objects, Analysis and Reporting Services. Here is a screen capture of what SSMS looks like for SQL Server 2012.


It also supports new Scripting Capabilities. This is supported by the integrated script editors that are contained in the SQL Server Management Studio. The script editors allow authoring Transact-SQL, MDX, DMX, and XML/A. The main features that SQL Server Management Studio supports under this head are a dynamic environment allowing access to information without any loss of time, various ready templates and the ability to customize them, renders support for functions like editing and writing queries and scripts while providing a new interface that allows XML results to be viewed. The Object Explorer part of the SQL Server Management Studio filtration using the entire name, date or schema or a part of it and the asynchronous members are filtered using the metadata. SQL Server Management Studio supports plug-ins and add-ins (extensibility) for it s built upon the Visual Studio Isolated Shell. This allows usage of extensions that have been developed by third parties.

Tool Windows in SQL Server Management Studio

For all stages of development and administration, the SQL Server Management Studio provides a number of powerful tool windows. Some of these tools are capable of being used on any SQL Server part while others are limited to only specific components. The tools that can be used for all the components of SQL server and their purpose are enumerated in the table below:




Can be Viewed

Object Explorer This is used for browsing servers, creating and locating objects. It is also used for managing data sources and viewing logs. This tool can be accessed from the View menu.
Solution Explorer This tool is used for storing and organizing scripts and connection information. SQL Server Scripts can be stored as Solutions and use the source control for managing scripts as their evolvement takes place. This tool can be accessed from the View menu.
Template Explorer This is used for creating queries that are based on the templates that exist. Custom queries can also be created and alter the templates that are existing. This tool can be accessed from the View menu.
Dynamic Help This is used for displaying a list of Help topics that are related. For viewing a particular topic, one needs to click on the name of the specific component.

The tools in the SQL Server Management Studio work with each other. To explain it with an example, a server can be registered with the Object Explorer and for opening a SQL Editor window that is connected to a specific database.

We provide SQL Server 2012 Tutorial videos including topics on Administration, Development, Programming and Business Intelligence areas. Feel free to check sample training at this page: http://sqlserver2012tutorial.com/free-video-training/





Follow us on: