When you have automated backup jobs running on your database server, sometimes you forget that they are even running. Then you forget to check to see if they are running successfully, and don’t realize until your database crashes and you can’t restore it since you don’t have a current backup.

That’s where email notifications come in, so you can see the job status every morning when you are sipping your coffee and pretending you are working.

SQL Server provides a built-in method of sending emails, but unfortunately it requires you to have Outlook and a profile installed on the server, which isn’t necessarily the ideal way to send an email. Thankfully there is another method, that involves installing a stored procedure on your server that will allow you to send email via SMTP.

You will want to edit one line in the stored procedure to put the IP address of your SMTP server:

EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“http://schemas.microsoft.com/cdo/configuration/smtpserver”).Value’, ’10.1.1.10′

Install the stored procedure into the master database, so it can be easily used from wherever needed.

Open up the SQL Server Agent \ Jobs list, and select the properties for the job you are trying to create a notification for:



Click on the Steps tab, and you should see a screen that looks like this:



Click the New button to create a new job step. We will use this step to send the email notification on success.

Step Name: Email Notification Success

Enter this SQL into the Command window as seen below. You will want to customize the email addresses and message subject to match your environment:

exec master.dbo.sp_SQLNotify ‘[email protected]’,'[email protected]’,'Backup Job Success’,'The Backup Job completed successfully’



Click OK and then click the New button again to create another step. This will be the failure notification step.

Step Name: Email Notification Failure

SQL:

exec master.dbo.sp_SQLNotify ‘[email protected]’,'[email protected]’,'Backup Job Failure,’The Backup Job failed’

Now the idea is to make the items follow a specific workflow. First click Edit on step 1, and set the properties as shown here:



What we are saying is that on success, go to the success step, and on failure, go to the failure step. Pretty simple stuff.

Now edit the second step, the one labled “Email Notification Success”, and set the properties as seen here:



We are saying that if the notification job is successful, then just quit the job without running step 3. If we don’t specify this, then we will end up getting two emails, one with success and one with failure.

Now edit the third step, the one labled “Email notification failure”, and set the properties as seen here:



Now your job steps should look like this:



You should now have email notifications in your inbox for either success or failure.

It is important to understand the reporting options available in SQL Server 2008 R2 to provide an appropriate report layout to meet the business requirements and needs of our end users. This post briefly discusses about different report layout types available and explains in detail the steps to create a Tabular Report. It also explains how to sorting and drilldown features to your report.

Different Available Report Layouts are

- Tabular Reports – these reports are defined using table data and organized in to rows and columns. The columns are typically fields in the table. Reporting detail rows can be grouped on various fields, each group can have header, footer , breaks and subtotals.

- Matrix Reports – To summarize the date for analysis we can use the Matrix Reports. It is useful for viewing the aggregated values with two different hierarchies(example time and geography).

- List Reports – List report consist of single rectangular area that repeats for every record or group value in the dataset.

- Chart Reports – these reports provide a visual context for different kinds of data. Some times complex information can be analyzed with very little explanation.

- Composite Reports – You can combine the reports that we discussed above for compelling the advanced reports.

Creating Tabular Reports

1. Open the Report Builder 3.0 , select the Insert Tab and click Table on the ribbon.

As we have not created a data set and data source, it will prompt you to add these objects. Use the DataSource Properties window to enter a name for the new data source as shown below





2. Enter the following query in query designer

   1: Select CalendarYear,SalesTerritoryRegion, SalesAmount
   2: FROM
   3:     FactResellerSales as F INNER JOIN DimDate as D ON
   4:     F.OrderDateKey = D.DateKey INNER JOIN DimSalesTerritory as ST
   5:     ON F.SalesTerritoryKey = ST.SalesTerritoryKey
   6:     order by CalendarYear , SalesTerritoryRegion

I am using the AdventureWorksR2 database to create this report.



3. Defining Table Groups – You can drop the database fields to zones to define groups located at the bottom of the designer pane.



In this example we are grouping the rows of this table based on CalendarYear and SalesTerritoryRegion Fileds. To add the CalendarYear field as a group above the detail row, drag and drop this field from the Data Window.

4. Drag the required fields to report designer as shown in the following window. As SalesAmount field is a currency type value and it should be formatted to show the values. To do so right click on the SalesAmount textbox and then choose properties





5. To look at the report that we built so far, you can click the run button in Report builder. Now the report is rendered as shown below



6.  Switch back to the design view to add totals to the end of a group with same result. Right-click on the CalendarYear row, and choose  Insert Row—>Inside Group – Below



The new row will be added below the group values. Hover the mouse over the new cell below the SalesAmount field and click on the field list icon as shown below



7. To see the changes in report, click on the run button to preview the report

8. Adding Sorting Feature to report – Any group can be sorted by any order using combination of data fields and expressions. For Example to set the sort order for the SalesTerritoryRegion Group click the group name under Row groups and select group properties you will see the following window



You can see the preview result in the following window



Creating Drill Down Reports – All report items and groups have a visibility property that can be set either permanently or conditionally. Common use is to create drill-down reports , where headers are used expand and collapse. Typically (+) or (-) sign will be displayed next to the column header.

To create a drilldown in this example, select CalendarYear Group from the lower part of the designer and click on group properties option then you should be able to get the following dialogue box



Set the display option to hide and select relevant textbox for the CalendarYear group by checking the Display toggle option. Click Ok to save these property changes.

In this tutorial, I will show you how to get column name and corresponding datatypes in particular table using SQL Server.

Description:

I have one table with lot columns in database at that time I tried to know the column names and corresponding datatypes and their maximum sizes in particular table for that I written the following query in SQL server to get column names and datatypes in particular table.

USE MySampleDB
GO
SELECT column_name 'Column Name',
data_type 'Data Type',
character_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = 'Country'

Demo



If anyone gets error with above query like

Invalid object name 'information_schema.columns'

This error because of case sensitive databases to rectify this error we need to write query like this 

USE CRMK
GO
SELECT column_name 'Column Name',
data_type 'Data Type',
character_maximum_length 'Maximum Length'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Country'

Here you need to enter your table name if that is in Caps lock you should enter caps lock name of your table only because it it is case sensitive.

This post discusses about configuring different user account to JOB running in SQL Server 2008. By default SQL JOBs runs under service account that you have configured while installing the SQL Server. You can follow the below steps to change the user account that running under JOB

1. Open SQL Server Management Studio and expand the SQL Server Agent node and Select the JOB that you want to change the running account.



2. Double click the selected JOB then you will get the below dialogue



3. Select the Steps from the above window and click Edit to see the below properties window



4. Click the ellipses button right to Run as user and select the user from below window and say ok



Now your job will run under chosen user account. It is always recommended to run your SQL JOBS under one account rather putting to many user accounts for to many JOBS. This post is for the people who have the business reason and want to run under different user account.

Error message when I try to restore a database backup to SQL Server 2008



Msg 3176, Level 16, State 1, Line 1 File 'D:\SQL Server 2008 DBs\test01.mdf' is claimed by 'SCTA_ORG_SECOND'(3) and 'SCTA_ORGANIZATION'(1). The WITH MOVE clause can be used to relocate one or more files.

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally

Steps to fix this issue:

select Script Action to New Query Window as the above image so you will get the following T-SQL and here we will know the reason

RESTORE DATABASE [test01] FROM  DISK = N'D:\SCTA_Org2.bak' WITH  FILE = 1,
    MOVE N'SCTA_ORGANIZATION' TO N'D:\SQL Server 2008 DBs\test01.mdf',
    MOVE N'SCTA_ORG_SECOND' TO N'D:\SQL Server 2008 DBs\test01.MDF',
    MOVE N'SCTA_ORGANIZATION_log' TO N'D:\SQL Server 2008 DBs\test01_1.ldf',
    NOUNLOAD,  STATS = 10
GO

so you will notice there are two files of mdf with the same name so just change the name of second one to test02 or to test01.ndf ( different extension) then run the command and it's successfully restored.

so the logical answer for this error first test01.mdf is a primary data file and the second is the secondary data file but the extension and name are same so that way you have to change the name or extension of second file with any other name or extension.

Note: the extension is anything ( it can be .fad or .ndf but .ndf is best practice to determine what this file for for example .ldf for log file , .ndf  for secondary data files ..).

Finally : I think the original database backup come from SQL Server 2000 and maybe this behavior allowed in SQL Server 2000 or the name is a case sensitive ( test01.mdf not like test01.MDF).

SQL Server use ASPState database to store the session information for SQL Server Session mode. ASPState database having two tables ASPStateTempApplications and ASPStateTempSessions. ASPStateTempApplications  table contains Application ID and Application Name which is specific to each and every application which are using SQL Server session mode on the particular database. ASPStateTempSessions table having numbers of fields to store the session related information which includes [SessionId], [Created], [Expires], [Timeout]  etc. [SessionItemShort] and [SessionItemLong] actually contains the session data for every users. If the session data size is < = 7000 KB it will be stored in SessionItemShort field and anything > 7000 KB will be stored in SessionItemLong field.



Once you have some session data stored inside ASPStateTempSessions  table, you can easily get the size of session data by running below SQL Query

use ASPState

select [sessionid],[created], datalength(SessionItemLong) as SessionDataSize from ASPStateTempSessions



Note : While running SQL query, you have to make sure you are calculating the size of proper field, if the session data size may vary for different user then you can try to find the data size for both  SessionItemLong and SessionItemShort field.

Introduction

Transparent Data Encryption is a new feature in SQL Server 2008. The TDE feature provides real time encryption of both data and log files. Encryption basically working in the following way; initially the data is encrypted before it’s being written to the disk and it is decrypted before it is being read from the disk. When you are using the Transparent Data Encryption feature of SQL Server 2008 the encryption is performed by the SQL Server 2008 Database Engine and the SQL Server clients will not be aware of this change. However, before implementing this feature in Production environment I would request you to validate the solution completely in the Test Environment.

To enable Transparent Data Encryption Feature of SQL Server 2008 on a database, the DBA needs to perform the below mentioned four steps as described in Books Online:-

1. Create a master key
2. Create or obtain a certificate protected by the master key
3. Create a database encryption key and protect it by the certificate
4. Set the database to use encryption

Create a Master Key

The initial step will be to identify if there is any Master Key already created in the Instance of SQL Server 2008 where you want to implement this feature. You can verify the same by executing the below mentioned TSQL code.

USE master
GO
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO

If there are no records found, then it means there was no predefined Master Key on the SQL Server 2008 Instance. To create a Master Key, you can execute the below mentioned TSQL code.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass@word1'
GO

Create or obtain a certificate protected by the Master Key

Once Master Key is created then the next step will be to Create or obtain a certificate protected by the master key. This can be achieved by executing the below mentioned TSQL code.

Use master
GO
CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'SQL Server TDE Certificate'
GO

/* Verify Certificate */
SELECT * FROM sys.certificates where [name] = 'TDECertificate'
GO

Next step will be to create a new database. Once the database is created you can create a database encryption key and protect it by the certificate by executing the below mentioned TSQL code.

Create a database encryption key and protect it by the certificate

Use master
GO
CREATE DATABASE TryEncryption
GO

Use TryEncryption
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECertificate
GO

Once the Database Encryption Key is created successfully you need to take a backup of the Certificate and the Private Key by executing the below mentioned TSQL code.

BACKUP CERTIFICATE TDECertificate TO FILE = 'D:\TDE\TDECertificate.cert'
WITH PRIVATE KEY (
FILE = 'D:\TDE\EncryptPrivateKey.key',
ENCRYPTION BY PASSWORD = 'Certific@tePass@word')
GO

Set the database to use encryption

The final step will be to enable encryption on the user database by executing the below mentioned TSQL code

ALTER DATABASE TryEncryption SET ENCRYPTION ON
GO

Verify Database Encryption Status

You can verify the database encryption status by executing the below mentioned TSQL code.

SELECT
       DB_NAME(database_id) AS DatabaseName
      ,Encryption_State AS EncryptionState
      ,key_algorithm AS Algorithm
      ,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO

SELECT
       NAME AS DatabaseName
      ,IS_ENCRYPTED AS IsEncrypted
FROM sys.databases where name ='TryEncryption'
GO

Advantages of Transparent Data Encryption

1. Physical Security of Database Files
2. When Transparent Database Encryption feature is used all the backups of the TDE enabled database are encrypted

Disadvantages of Transparent Data Encryption

1. As Encryption is CPU intensive and it is performed at I/O level, any server with higher I/O and higher CPU load should avoid using this feature
2. This feature is only available in Enterprise and Developer Editions of SQL Server 2008
3. TDE encrypted database cannot be attached or restored in other edition of SQL Server 2008
4. If the certificate is lost then the data will be unreadable. Hence you need to protect the certificate and master key along with the database backup files
5. If you are using FILESTREAM feature, then be informed that only FILESTREAM enabled database is encrypted and not the actual files which are residing on the servers file system will be encrypted
6. There won’t be much of a benefit if you planning to use Database Backup Compression feature of SQL Server 2008
7. As TempDB database is automatically encrypted once you have enabled encryption on any of the user databases. This resulted in slow query performance for non encrypted databases which may use TempDB

For more information you can check the following link

The programmers are getting this error message when there is high workload on the server. And servers are experiencing high memory pressure.

In this error theire are some additional symptoms also.

1. When connecting to server will get the error message as "Login Failed".

2. Will get disconnected from server.

3. CPU usage will be very high.

4. if running "select * from sysprocesses" SPIDs will have a waittype of 0x40 or 0x0040 and a last_waittype of RESOURCE_SEMAPHORE.

5. The System Monitor object SQLServer:Memory Manager displays a non-zero
value for Memory Grants Pending.

6. SQL Profiler displays the event "Execution Warnings" that includes
the "Wait For Memory" or the "Wait For Memory Timeout" text.

Reasons for this error is memory intensive queries are getting qued and are not getting resources before timout period. And after timout period and getting timout. By default query wait period is -1 by setting non-negative number you can improve the query wait time.

Other reasons for this errors are not properly optimised queries, memory allocation for sql server is too small.

Solutions for this error include the following.

1. Optimise the performance of queries using sql profiler.

2. Distrybution statistics should be uptodate.

3. Watch the system monitor trace to see the memory usage of sql server.

4. If you are running SQL Server 7.0, test disabling parallelism for SQL Server 7.0 by turning the max degree of parallelism configuration option off.

Report builder 2.0 is a report authoring tool that we can use to design and publish reports. We can specify the data source, what data to display on report and which layout you prefer to see the report. When you run the report, the report processor takes all the information you specified and retrieves the data and generates each page as you view it. This post explains step-by-step details of creating the report using Report Builder 2.0.

Download Report Builder 2.0 here. Report Builder was first introduced in SQL server 2005 and it is still available in report.

After Installing Report Builder you can launch it by Start->All Programs->Microsoft SQL Server Report Builder 2.0.



1. You’ll see the following screen after launching the Report Builder 2.0:



2. Visually there are number of differences when you compare with the SQL Server 2005 report builder.

- The office 2007 ribbon interface.
- On the left hand side of the designer you will see built-in fields,report parameters, images and data fields.
- To begin designing a report, you can select either Table or Matrix or Chart icons from designer interface as shown above.

3. This post using the AdventureWorksDW2008 sample database as data source. You can download the databases from here.  

4. Select the data source for you report as follows:



5. After completing the data source connection configuration, then click next to design a query for your report.



Either you can select the data fields or views from your database or you can use editor to type your sql query for binding data.

6. Click next to get the Arrange fields-dialogue box where you can drag and drop your data fields into the Row groups.



7. Click Next to choose the layout for your report.



8. You can also the chart in your report with the same data source which we defined above as follows



9. Run the above report then you will see the following output



10. You can deploy the above designed report to a SQL server 2008 Report Server. You should specify the URL of the default report server in Report Builder Settings. Click the database icon in top-left corner from Report-Builder window and you will see the following dialogue



You can enter the URL of your default report server or SharePoint site where you want to deploy your reports.

Introduction

Microsoft SQL Server 2008 R2 is the latest release of SQL Server. This article will introduce the top 10 features and benefits of SQL Server 2008 R2. The “R2” tag indicates this is an intermediate release of SQL Server and not a major revision. However, there are a number of interesting new features for both DBAs and developers alike. At the time of this article, R2 is available as a CTP (Community Technology Preview). In addition to new features, there are two new editions as well, SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse.

Report Builder 3.0

Report Builder is a tool set for developing rich reports that can be delivered over the web. Some of the features of Report Builder include the ability to create reports containing graphs, charts, tables, and printing controls. In addition, Report Builder also supports drill downs and sorting. If you are familiar with the third party tool Crystal Reports, then you have good idea of what to expect from Report Builder.

New features in SQL 2008 R2 / Report Builder 3.0 include: Map Layers, which can hold spatial and analytical data and will integrate with Microsoft Virtual Earth. Indicators, these are gauges used to show the state of one value. Report Parts, this object can be reused or shared between multiple reports. Aggregate Calculating, this allows you to calculate the total value of other aggregate calculated totals.

SQL Server 2008 R2 Datacenter

The new Datacenter edition of SQL Server 2008 R2 is targeted towards Enterprise Edition users who require a greater performance platform. The new edition will support 256 logical processors, high numbers of instances, and as much memory as the operating system will support.

SQL Server 2008 R2 Parallel Data Warehouse

Another new SQL Server edition, Parallel Data Warehouse, formally codenamed “Madison”, specializes in handling extremely large amounts of data. This new version uses massively parallel processing to spread large tables over multiple SQL nodes. The multiple nodes are handled by a propriety Microsoft technology called Ultra Shared Nothing. This new technology is described as a Control Node spreading queries to Computer Nodes, evenly distributed, then collecting the results.

StreamInsight

New in SQL Server 2008 R2 is component called StreamInsight. This interesting component allows streaming data to be analyzed on the fly. Meaning the data is processed directly from the source stream prior to being saved in a SQL Server table. This could be extremely handy if you’re running a real time system and need to analyze data but can’t afford the latency of a committed write to a table first. Examples usually cited for this application include stock trading streams, click stream web analytics, and industrial process controls. Multiple input streams can be simultaneously monitored.

Master Data Services

Master Data Services (MDS) is both a concept and a product. The concept of a Master Data Service is that there is a central data gate keeper of core business data. Data items such as customer billing addresses, employee/customer names, and product names should be centrally managed so that all consuming applications have the same information. The Microsoft example given is a company that has a customer address record in the customer table but a different address in the mailing table. A Master Data Service application would ensure that all tables would have only one correct address. While an MDS can be a homegrown application, SQL Server 2008 R2 includes an application and an interface to manage the central data.

PowerPivot for SharePoint

PowerPivot is an end-user tool that works in conjunction with SharePoint, SQL Server 2008 R2, and Excel 2010 to process large amounts of data in seconds. PowerPivot works like an Excel Pivot Table, and includes analytical capabilities.

Data-Tier Application

A Data-Tier Application (abbreviated as DAC –no idea what the C stands for, and not to be confused with the Windows Data Access Components also abbreviated as DAC ) is an object that stores all the needed database information for a project, such as login, tables, and procedures into one package that can be consumed by Visual Studio. By creating a Data-Tier Application, a SQL Server package version could be saved with each Visual Studio build of your application. This would allow application code builds to be married to a database build in an easily managed way.

Unicode Compression

SQL Server 2008 R2 uses a new algorithm known as Simple Compression Scheme for Unicode storage. This reduces the amount of disk spaced used by Unicode characters. This new format happens automatically and is managed by the SQL Server engine so no programming changes are required of the DBA.

SQL Server Utility

The new SQL Server Utility is a repository object for centrally controlling multiple SQL Server instances. Performance data and configuration policies can be stored in a single Utility. The Utility also includes an Explorer tool where multi-server dashboards can be created.

Multi Server Dashboards

While the SQL Server Management Studio could always connection to multiple servers, each was managed independently with no central view of all of them. Now with SQL Server 2008 R2, Dashboards showing combined server data can be created