Archive

Archive for April, 2012

SQL Server 2012 (a.k.a. Denali) | The Fantastic 12 of 2012

April 24, 2012 2 comments

SQL Server team has come up with a new series to promote its new product and create awareness among techies about what SQL Server 2012 is capable of and what new features it comes packed with. Check out the full series of The Fantastic 12 of SQL Server 2012 here.

The above link will provide you direct access to the technet blogs with detailed info on all 12 topics and videos.

The series will talk about 12 new features, which are as follows:

1. Required 9s Data Protection:
– Always On
– New support for Windows Server Core

2. Blazing-Fast Performance:
– New ColumnStore Index, [video]
– Improved Full Text Search
– Good Compression capabilities.

3. Organizational Security and Compliance:
– Data Protection (Encryption and Compression)
– Control Access (User-Defined Server Roles, Default Schema for Groups, Contained Database Authentication and Active Directory)
– Ensure Compliance (SQL Server Audit and Policy-Based Management)

4. Peace of Mind:
– Product Enhancements (Distributed Replay, System Center Alignment, System Center Advisor and No-fee Service Packs)
– Free Planning Tools
– Tailored Support and Licensing Programs (Mission Critical Support and Enrollment for Application Platform)

5. Rapid Data Exploration & Visualization:
– Self-Service Analytics (Power Pivot)
– Stunning, interactive data visualization (Power View)

6. Managed Self-Service BI:
– Gain insight and oversight (Power Pivot for SharePoint)
– Enable IT Efficiency (End user created, IT managed, Ease of administration through SharePoint and SQL Azure Reporting)

7. Credible, Consistent Data:
– BI Semantic Model
– Integration Services
– Data Quality Services
– Master Data Services

8. Scalable Analytics & Data Warehousing:
– Flexibility and Choice (Hardware and deployment options and Optimized Solutions)
– Massive Scale at Low Cost (Built-in Functionality, Support for Powerful Hardware and Parallel Data Warehouse)
– Complete BI Solution (Scalable OLAP)

9. Scale On Demand:
– Self-service Deployments
– SQL Azure Federation
– Contained Databases
– License Mobility
– Data Sync

10. Fast Time To Solution:
– Optimized Transaction processing and data warehousing
– Complete solutions co-engineered with hardware partners
– Agile to market from weeks and months to days

11. Optimized Productivity:
– SQL Server Data Tools
– T-SQL Enhancements
– Common tools
– SQL Server Management Studio

12. Extend Any Data, Anywhere:
– Support for Any Data
– Statistical Semantic Full-Text Search
– OData
– SQL Azure DataMarket
– Enhanced interop support

This “Fantastic 12” feature list is also available for download, and the PDF is here.

Check out the Webcast/Videos for all these 12 features, here.

Advertisement
Categories: SQL Server 2012 Tags: , , , , , , , , , ,

SQL Server 2012 (a.k.a. Denali) – Best Practices Analyzer

April 23, 2012 Leave a comment

Microsoft SQL Server 2012 released its Best Practices Analyzer (BPA) on 1st week of April and is available free for download at following [link].

-> Overview …as mentioned in the link

BPA is a diagnostic tool that performs the following functions:
1. Gathers information about a Server and a Microsoft SQL Server 2012 instance installed on that Server.
2. Determines if the configurations are set according to the recommended best practices.
3. Reports on all configurations, indicating settings that differ from recommendations.
4. Indicates potential problems in the installed instance of SQL Server.
5. Recommends solutions to potential problems.

-> System Requirements to install
– PowerShell V2.0
– Microsoft Baseline Configuration Analyzer V2.0

The download link also provides information about:
– Installation Instructions.
– And some Additional Information about its capabilities.

… stay tuned for more updates about SQL Server 2012.

Categories: Optimization Performance, SQL Server 2012 Tags: , , ,

SQL DBA – Check Isolation Level of a Database | DBCC USEROPTIONS

April 20, 2012 Leave a comment

While testing a functionality we had to run some Stored Procedures in parallel. So we executed all 4 SPs in different Sessions (separate windows). All SPs got executed successfully except one, this one ended up in a Deadlock.

I thought to check the Isolation level of database but my mind didn’t clicked instantly.
So I checked MS BOL about this and find the DBCC management command to find it, which is DBCC USEROPTIONS & as follows:

USE [AdventureWorks2012]
GO

DBCC USEROPTIONS
GO

Output:-

Set Option		Value
textsize		2147483647
language		us_english
dateformat		mdy
datefirst		7
lock_timeout		-1
quoted_identifier	SET
arithabort		SET
ansi_null_dflt_on	SET
ansi_warnings		SET
ansi_padding		SET
ansi_nulls		SET
concat_null_yields_null	SET
isolation level		read committed

Other Isolation level values it returns are:-

- read uncommitted
- read committed
- repeatable read
- serializable
- read committed snapshot
- snapshot

The sys.databases metadata View also contains a column i.e. is_read_committed_snapshot_on, which tells if READ_COMMITTED_SNAPSHOT Isolation level in ON or OFF.
Check this:

select is_read_committed_snapshot_on, *
from sys.databases
Categories: DBA Stuff Tags:

Microsoft Deployment Toolkit (MDT) 2012 Now Available | Reliable and flexible OS deployment

April 18, 2012 Leave a comment

The latest version of the Microsoft Deployment Toolkit—version 2012— is now available for download.
Visit the Download Center to download MDT 2012.

Deploy Windows 7, Office 2010, Windows 8 Consumer Preview, Windows Server “8” Beta and Windows Server 2008 R2 with the newly released Microsoft Deployment Toolkit (MDT) 2012. MDT 2012 is the newest version of Microsoft Deployment Toolkit, a Solution Accelerator for operating system and application deployment. MDT is the recommended process and toolset for automating Windows 7 and Office 365 deployments.

The Solution Accelerators Team added new features to MDT 2012 that include the ability for users to initiate and customize their own deployments using System Center Configuration Manager 2012, key enhancements in Windows 7 driver support, and much more.
MDT 2012 will provide new benefits such as:
• Comprehensive tools and guidance to efficiently manage large-scale deployments of Microsoft Office 2010.
• An enhanced User-Driven Installation (UDI) deployment method that utilizes System Center Configuration Manager 2012. UDI lets end users initiate and customize an OS deployment on their PCs—via an easy-to-use wizard.
• Ease Lite Touch installation with new capabilities to check on the status of currently running deployments.
• This release provides support for deploying Windows 8 Consumer Preview in a lab environment.

These features, combined with many bug fixes and other improvements, make MDT 2012 more reliable and flexible than ever.

Support for Configuration Manager 2012: MDT 2012 provides support for Configuration Manager 2012 releases. MDT 2012 fully leverages the capabilities provided by Configuration Manager 2012 for OS deployment. Users now also have the ability to migrate MDT 2012 task sequences from Configuration Manager 2007 to Configuration Manager 2012.

Customize deployment questions: For System Center Configuration Manager customers, MDT 2012 provides an improved, extensible wizard and designer for customizing deployment questions.

Ease Lite Touch installation: The Microsoft Diagnostics and Recovery Toolkit (DaRT) is now integrated with Lite Touch Installation, providing remote control and diagnostics. New monitoring capabilities are available to check on the status of currently running deployments. LTI now has an improved deployment wizard user experience. Enhanced partitioning support ensures that deployments work regardless of the current structure.

Secure Deployments: MDT 2012 offers integration with the Microsoft Security Compliance Manager (SCM) tool to ensure a secure Windows deployment from the start.

Reliability and flexibility: Existing MDT users will find more reliability and flexibility with the many small enhancements and bug fixes and a smooth and simple upgrade process.

Support for Windows 8: MDT 2012 provides support for deploying Windows 8 Consumer Preview and Windows Server “8” Beta in a lab environment.

Next steps:
• Download MDT 2012.
• Send your comments to the MDT Team.
• Learn more about the MDT.

Get the latest tips from Microsoft Solution Accelerators—in 140 characters or less! Follow us on Twitter: @MSSolutionAccel.

Categories: Microsoft Tags: , ,

SQL Server 2012 (a.k.a. Denali) | “SET FMTONLY ON/OFF” Deprecated | new SPs and DMFs Introduced

April 17, 2012 Leave a comment

While running SQL Server Profiler to know what’s running behind the Application you might have noticed “SET FMTONLY ON” with other SQL statements. Most of you would be knowing this, the main point of putting this post is to let people know that this feature is deprecated in Denali and not to use it in new development work.

“SET FMTONLY ON” Returns only metadata to the client. It can be used to test the format of the response without actually running the query.
Let’s check how it is being used and what it returns:

USE AdventureWorks2012;
GO

-- Simple SELECT without using the "SET FMTONLY ON", OFF by-default:
SELECT TOP 10 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion
FROM [Person].[Person]

-- Using "SET FMTONLY":
SET FMTONLY ON;

SELECT TOP 10 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion
FROM [Person].[Person]

SET FMTONLY OFF;
GO

Output:

This feature is deprecated in Denali and should not be used in production code.

Now in replacement to this Denali has introduced 4 new objects to get the same information in easy and more informative way.
These are as follows:

--> 1. SP: sp_describe_first_result_set
-- Returns the metadata for the first possible result set of the Transact-SQL batch.
EXEC sp_describe_first_result_set @tsql = N'SELECT * FROM HumanResources.Employee'

--> 2. SP: sp_describe_undeclared_parameters
-- Returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch.
EXEC sp_describe_undeclared_parameters @tsql = N'SELECT * FROM HumanResources.Employee where BusinessEntityID = @id AND BirthDate > @dt'

--> 3. DMF: sys.dm_exec_describe_first_result_set()
-- This is a DMF (Dynamic Management Function) which takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.
SELECT * FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM HumanResources.Employee', NULL, 0)
-- It is similar to the SP sp_describe_first_result_set mentioned above.

--> 4. DMF: sys.dm_exec_describe_first_result_set_for_object()
-- This is a DMF (Dynamic Management Function) which takes an @object_id as a parameter and describes the first result metadata for the module with that ID.
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('uspGetEmployeeManagers'), 0)
-- It has same set definition as sys.dm_exec_describe_first_result_set() DMF mentioned above.


-- The last mentioned DMF can also be used with the sys.procedures to get the metadata of the Stored Procedures:
SELECT p.name, X.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS X

… Just execute each one of these and check what all information they provide.

I really like these SPs and DM Functions, will be very useful in Metadata based based Dev work.

More info on this check MS BOL: http://msdn.microsoft.com/en-us/library/ms173839.aspx

Categories: SQL Server 2012, SQL Tips Tags: , , , , , , ,