Rule "SQL Server Database Services feature state" failed

Change registry key on active node:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\ConfigurationState.

Change all values from 2 to 1 for all Four items in the right pane.

Re-Run the rule on the installation. Now it will be solved and through.

The current SKU is invalid. When adding second node to SQL 2008 failover Cluster

Resolution
===========

There will be an ini file in setup. Z\SQLsetup\x86 folder >> DefaultSetup.ini
which contains following entry like

;SQLSERVER2008 Configuration File
[SQLSERVER2008]
PID=”*****-*****-*****-*****-*****”

Just comment out the PID key by semi collen (;) and then copy the key and put that key during the installation. It should be like this

;SQLSERVER2008 Configuration File
[SQLSERVER2008]
;PID=”*****-*****-*****-*****-*****”

copy the PID key *****-*****-*****-*****-***** and paste during the installation.

Not able to add SQL 2008 secone node , SQL server agent account is grayed out

Resolution 1 :-
============

Add the SQl Server Agent to the existing resources. From Right pane of Server manager >> Add resource>>Server>> Select Sql Server Agent

Bring it online

This will work out

Resolution 2:-
==============
If its failing with credential issues, try to install from command line. This is for Default installation, named give name in instance name.

Choose CMD run as Administrator

setup.exe /ACTION=AddNode /INSTANCENAME="MSSQLSERVER" /SQLSVCACCOUNT="DOMAIN\ACCOUNT" /SQLSVCPASSWORD="*******" /AGTSVCACCOUNT="DOMAIN\ACCOUNT" /AGTSVCPASSWORD="*******


This will resolve .

Unable to start SQLServer agent resource on cluster after installation

Resolution

========

Modify the the following Key.
=======================

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent

ServerHost :- This will be blank, Give the VIRTUAL SERVER NAME, i.e Same name when we choose during installation of SQL SERVER

Updating a XML Column

DECLARE @newValue varchar(50)
SELECT @newValue = 'NewValueXX'
UPDATE [temp1]
SET request_data.modify('replace value of (/request_data/segment/text())[1] with sql:variable("@newValue")')
Where request_data.value(' (/request_data/segment)[1]', 'varchar(50)' ) like 'ta%'

What is Contained Database in SQL Server

What is Contained Database ?
1.Contained database is a new feature launched in SQL Server 2011.
2.This model clearly separates the database application from the management of SQL Server. Users can connect to the database without authenticating a login at the Database Engine level.
3.Applications within contained databases can be isolated from other databases from the instance of SQL Server, on which they reside, simplifying development and management.

A Video Tutorial and a Presentation on Contained Databases can be downloaded from here http://sqlserver-training.com/video-sql-server-contained-database/-

Why Contained Databases feature was Required ?

One of the problems facing the existing database model is data being left behind when an application is moved from one instance to another. Some data connected with an application, (for example, login information and agent job information) is currently stored within the instance instead of inside the database and When you move a non-contained database application from one instance to another instance of SQL Server, this data is left behind.

Later On, DBA’s nee to identify the data which was left behind and move it with your application to the new instance of SQL Server. This process can be time consuming and difficult.

What all is being saved inside a Contained Database?

The contained database, keeps all necessary information and objects in the database, for example
•Tables
•Functions
•Constraints
•Schemas
•types.

It also stores all application-level objects in the database, including
•Logins
•Application-level agent jobs
•Persisted error messages
•Linked server information
•System settings

What are the Benefits of using Contained Database ?

Contained databases can be easily moved to another server and start working instantly without the need of any additional configuration like adding user, mapping SID’s again. As Contained database have no external dependencies.

What type of Authentication Modes are supported by Contained Database?

Contained Databases supports
•SQL Server Authentication
•Windows Based Authentication

A contained user is a user without a login in the master database which resides in a Contained Database and can connect to this database specifying its credentials in the connection string. This user doesn’t exists in master’s syslogins tables and will not even show up when you list all SQL Users.

How we create a Contained Database ?

Prior to create a Contained database we need to enable "contained database authentication" property at SQL Server Level. This can be enabled using the following code.
-- Enable contained database authentication on the instance of SQL Server
-- This can done by A member of the sysadmin fixed server role
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go
How to create Users in Contained Databases ?

A Contained database can be created as a normal user database, but with a property named "CONTAINMENT".

Following code, will create a database named "TEST" as contained database.
-- To create contained db you have to specify CONTAINMENT property
CREATE DATABASE TEST
CONTAINMENT = PARTIAL;
go
How to create a user inside a Contained Database ?

A user can be created as a normal user in contained database. The following code, will create a new user name "usr_TEST" inside a TEST Contained database.
USE TEST;
go
-- Create a contained SQL Server Authentication user
CREATE USER usr_TEST
WITH PASSWORD = 'TEST@123$';
go
How to connect to Contained database using a user which exists in Contained Database

While making a connection to SQL Server, we need to specify the [Contained Database name] as default database during the connection.



How The Authentication Process works in case of Contained Database ?

Authentication process can be understood using the following flowchart.


Thanks to :- http://sqlserver-training.com/what-is-contained-database-in-sql-server/-

New Features of SQL Server "Denali"

1. SQL Server AlwaysOn

Denali's AlwaysOn includes features to help simplify high-availability environments and maximize hardware investments. One of these features is support for multi-site clustering, also referred to as multi-subnet clustering. Windows Server Core support is also included. It attempts to reduce SQL Server downtime by eliminating 50 to 60% of the reboots required by OS patching.

2. High Availability and Disaster Recovery

HADR, High Availability and Disaster Recovery, is also introduced with this latest version. With it comes the concept of Availability Groups. Availability Groups enable a set of one or more related SQL Server databases to define one or more secondary copies. The source set is referred to as the primary replica and the failovers as secondary replica. One of the secondary replicas can be active, allowing for read-only connections. Viola, instant reporting database! For more information on HADR, see http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx.

3. Juneau

Juneau is the code name for the new SQL Server Development Tools. Its purpose is to provide a single development environment for all database related project types including bringing BIDS (Business Intelligence Development Studio) and SSMS (SQL Server Management Studio) into the same IDE.

4. Apollo

Apollo is the codename for Column-based Query Accelerator. I have to admit that this is the feature I'm most excited about. I watched some impressive demos showing how much performance gain is realized on very large result set queries. Microsoft claims an approximate 10 times increase in query performance.

Columnstore Indexes are what is set up to provide for this increase in query performance. Essentially, instead of storing rows on pages, columns are stored individually on a set of pages. Algorithms can then be used to compress the redundancy out of the column. The whitepaper on the new Columnstore Index points out the following benefits:
• only the columns needed to solve a query are fetched from disk (this is often fewer than 15% of the columns in a typical fact table),
• it's easier to compress the data due to the redundancy of data within a column, and
• buffer hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, while infrequently used parts are paged out.

5. Crescent

This cool new tool aimed at the business user is actually still yet to come. Project "Crescent" furthers Business Intelligence for everyone by providing a powerful and speedy data visualization tool in the browser. It has a feature called storyboarding which allows the user to create multiple snapshots of the data in order to tell a story about that data. Once the snapshots are created, the storyboard can be exported to PowerPoint where the data is displayed live inside the PowerPoint slides. The demos of this product are a must-see. Below are two links. The last is the keynote from Day 1 of the PASS Summit (by Ted Kummert, Microsoft Sr. Vice President, Business Platform Division) and contains a lot more than just Project Crescent.

Data Visualization Done Right: Project Crescent

PASS Summit 2010 Day One Live Streaming Keynote

6. FileTable

The new FileTable feature takes storing blob data to a whole new level. With Denali, you can now define a whole table as a FileTable and point it to a folder on a file system. When files are written to that folder, they are available within that FileTable. They can be queried, backed up, and restored right along with the rest of your application data.

7. Integration Improvements

Several improvements are coming in Denali to make creating SSIS packages more efficient. One of these changes is the new Undo and Redo buttons in the control and flow designers. There are also source and destination assistants available to help set up sources and destinations.

More importantly, though, new knowledge-driven data cleansing capabilities are being delivered as Data Quality Services. Additionally, Impact Analysis and Lineage can be used to predict changes and troubleshoot packages.

8. Contained Databases

http://www.youtube.com/watch?v=CKrH5x7KxfQ&feature=player_embedded#at=488

Contained Databases is a concept where databases are not tied to the instance they reside on. Everything about a database is contained in that database without today's coupling to the database engine (SQL Server instance) it's running under.

Specifically, users are no longer tied to logins on the instance. I'm actually pretty excited about this. I can't tell you how much time this will save, no longer needing to fix orphaned users in development, test, and stage when we pull down production databases to our non-production environments.

9. Spatial Features

SQL Server spatial features have been greatly expanded with this new release. Highlights include new CircularArc subtypes, support for objects bigger than a logical hemisphere (including a new FULLGLOBE data type), new methods for the geography type, spatial index improvements, Nearest Neighbor Query Plan, and support for persisted computes columns.

To get more detail on these features and more, the New Spatial Features Whitepaper can be downloaded from Microsoft.

Conclusion

Intrigued by the new features of SQL Server 11? You can download the CTP at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9 to try it for yourself.