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.

Windows 2008 R2 has introduced a number of compelling features that would entice any Windows administrator to upgrade to, and the most welcomed feature in my own opinion would have to be the Active Directory Recycle Bin.  Previous to the R2 upgrade, system admins and the like would have had to rely on paid 3rd party software that would take care of accidental deletions of users or even worst organizational units.  Those who did not make the investment in 3rd party software would have had to rely on system state backups which is always a disruptive process in the event that you needed to perform an authoritative Active Directory restore.

Before we begin, we need to ensure that we have met the minimum requirements allowing you to enable the Active Recycle Bin.  In summary, your Domain Forest Functional Level needs to be at least Windows 2008 R2.  More information can be found in the following TechNet article; http://technet.microsoft.com/tr-tr/library/dd379484(WS.10).aspx

Now that we have met those requirements, we need to run the following command on the AD Domain controller where the Schema Master Resides.  If you are not sure where the Schema Master role resides, follow the below TechNet article on How to view and transfer FSMO roles in Windows Server 2003.

On the Schema Master Domain Controller, run Start / Administrative Tools /  Active Directory Module for Windows PowerShell.

Type in the following command;

N.B replace yourdomain.com with your own Active Directory domain name

Enable-ADOptionalFeature –Identity ‘CN=Recycle Bin Feature,CN=Optional Features,CN=Directory Service,CN=Windows NT,CN=Services,CN=Configuration, DC=yourdomain,DC=com’ –Scope ForestOrConfigurationSet –Target ‘yourdomain.com’



You will get a warning which you will need to confirm stating that enabling the Recycle Bin Feature is irreversible.

That’s it!  The recycle bin will now begin capturing deletions of objects which will allow you to later restore them to their original or alternate location.  Now you might be asking, how do we actually perform a restore?  Well, I’m glad you asked, that’s the 2nd reason why you are reading this article right?!

Microsoft for some reason did not provide admins with a GUI in doing so, however there are FREE 3rd party tools that plug directly into the Recycle Bin feature that will provide you with an easy GUI for performing Active Directory object(s) restoration.  Now before delving into MY current tool of choice, the following article by Microsoft explains how it is done via the command line; Restore a Deleted Active Directory Object http://technet.microsoft.com/tr-tr/library/dd379509(WS.10).aspx

My tool of choice (there are several out there) providing me with a graphical interface is PowerGUI in combination with their Active Directory Recycle Bin for PowerPack.  You can download these from the links below;

Download the latest PowerGUI from http://powergui.org/downloads.jspa

Download the latest Active Directory Recycle Bin PowerPack from http://powergui.org/kbcategory.jspa?categoryID=46

Now that we have the relevant components, install PowerGUI and then import the AD Recycle Bin PowerPack via File / PowerPack Management / Import



Now as a test I have created a Test User account in Active Directory and then deleted the account a few minutes later.  Lo and behold when I refreshed the Active Directory Recycle Bin node within the PowerGUI Navigation tree, my Test User was listed in the results pane.



From the Actions menu, you can easily restore the user to either its original location or alternate location.  From the Actions menu you can also configure the recycle bin further via a GUI, and empty the recycle bin completely.



As you can see from the above, the Active Recycle Bin is a long awaited feature introduced with Windows 2008 R2 and with a front end like PowerGUI you can now easily and very quickly restore accidentally deleted Active Directory objects.  Now I wonder if Microsoft will incorporate their own graphical interface in the near future.

“Track Change” one of the best interesting features in visual studio which indicates the code changes with a color indicator at the beginning of the line.  Generally we know about the two color indicator  “Green” and “Yellow” which are used indicting the color change till VS 2008 along with those VS 2010 introduced another new color “Orange” which indicates some additional track change for undoing file after save. In this blog post I am going to explain how those color indicator helps developers to track the code changes.

In Visual Studio 2010, there is three color indicator

Green color indicates the lines  which you have edited before your last save.  Save again the file and green mark will be disappear.



Yellow color indicates the lines which  you have edited since the  last save of that file.  Yellow becomes Green after saving of the file. Once you close the file that indication disappears.



Orange color indication has newly introduced in VS 2010.  This color will come when  user does an undo after a save operation for that current file. Orange color indicates  that current changed line is different from the saved version of the file.



How to Enable / Disable Track Change features ?

To enable or disable  the “Track Change” features, Goto Tools > Options > TextEditor . In General section, you can checked or Unchecked the “Track Change” option



Key Note :  While you are using “Track Change” option, you have to select “Selection Margin” display option other wise, “Track Change” Won’t work.



One Quick Flow of “Track Change”

You have opened one code file in visual studio 2010 which having below lines of code. By default there is not color indicator with line number.



Now, you have started editing the file Yellow indicator shows , you have made changes on those line.



When you are done with your changes, Save the file. Green indicator shows, all of your changes has been saved.



Now, you have undoing the files after save, orange indicator saying that, the lines is different  than the save version. It means, you have changed something on original file, saved it and again undo it.



When you first undoing it, it will be showing as “Yellow”, which means you are editing with this line, but when you have done with all the undoing for that line which is different the saved version colors becomes “Orange”. Which means, it is different than the saved version.

To get a clear , just compare the below two image . Yes,  all the orange color  marked line are similar with default version image.



But, the Saved version of the file is



Similarly, This Track changes also works for config files also.



Summary : “Track Change” is one very useful features in Visual studio , by which You can see where you have edited / saved on  a file for that current VS IDE state . Green color indicates the lines  which you have edited before your last save.Yellow color indicates the lines which  you have edited since the  last save of that file. Orange color indicates  that current changed line is different from the saved version of the file.

 If you want to know more about editor setting options , please check the below link

How to: Set Text Editor Options

This is great for developers, testers, and anyone looking to learn Active Directory or deploy to a small network.  If this is for a production deployment, you might want to bring in a professional to help you.  There are many other things to consider, like ‘hardening’ your server and setting up Group Policy.  Having an insecure or unprotected domain controller is inviting havoc on your network.

So without any further ado and in the immortal words of ‘Marv’, “Let’s get to it!”

In the Server Manager click on Add Roles.



Click next on the ‘Before You Begin’ screen if it shows.  On the next screen, ‘Select Server Roles’, check the box for Active Directory Domain Services.  After checking the box, you may receive a window that says you need to add required features, click the button marked Add Required Features.





Then back at the ‘Select Server Roles’ window, click Next.  Here you can do some reading if you’re unfamiliar with Active Directory.  There are links for an overview, installation instructions, and common configurations.  There’s also some notes that say it is advisable to have at least 2 domain controllers, that you’ll need a DNS server, that you’ll have to run DCPROMO.exe, and informs you that you’re also installing DFS (Distributed File System), and some replication services tied to DFS.

Click Next and you’ll see the ‘Confirm Installation Selections’ window.  Click the button marked Install.



The ‘Installation Progress’ window will appear letting you know what the system is doing.  After a few minutes the ‘Installation Results’ window will appear.  Click the link marked Close this wizard and launch the Active Directory Domain Services Installation Wizard (dcpromo.exe).



Another wizard will open, ‘Active Directory Domain Services Installation Wizard’.  Click Next



Read the note on the next screen titled ‘Operating System Compatibility’.  The link to the KB article 942564 underneath is (http://go.microsoft.com/fwlink/?LinkId=104751).  Click Next. On the ‘Choose a Deployment Configuration’ screen, we’ll choose Create a new domain in a new forest for the purposes of this tutorial.  If you’re attempting to add a domain controller to an existing domain / forest, you would choose the ‘Existing Forest’ checkbox.  Click Next



Here’s where you input what you want your FQDN (Fully Qualified Domain Name) to be.  Then click Next.



The system will confirm that the FQDN is not in existence already on your network, then allow you to choose your Domain NetBIOS name.  After doing so, click Next.  The system will then confirm that NetBIOS name is not in use.



On the next screen, you select what you want your forest functional level to be.  You can choose: Windows Server 2003, 2008, or 2008 R2.  In this tutorial we’ll be setting the forest functional level to Windows Server 2008 R2.  If you’ll be connecting other DCs that are running Windows Server 2008 or 2003, then may will need to choose a compatible level.  Click Next.



Now we’ll install the DNS server.  Make sure that DNS server checkbox is checked, then click Next.  Domain controllers, DCs, require Domain Name Services.



Click Yes at the next window, which is warning you that delegation cannot be configured for the parent zone.  Don’t worry, there is no parent zone.  Accept the default locations for your Database, Log Files, and SYSVOL folders, or change them if you really like.  Click Next.



Input a password, twice, in the ‘Directory Services Restore Mode Administrator Password’ window.  Then click Next.  Review your selections and click Next.

The wizard will then install and configure Active Directory Domain Services and Directory Services on the DC.  Click Finish, and select to Restart.



Congratulations, you’ve just done the basic setup for an Active Directory Domain Controller, and DNS support services on Windows Server 2008 R2.  After the reboot, you can log into your server using the administrator account and password that was previously assigned to the local administrator account.  NOTE: the password that you were using, is now assigned as your domain admin.  It is advisable to make sure that password is STRONG.

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.