Cannot execute as the database principal because the principal "dbo" does not exist

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission

I got this error during SQL Server Transaction Replication.

Solution:

            Expand Object explorer, Right click on Database, Select Properties, and click on Files

Provide the owner of database

Database cannot be opened due to inaccessible files or insufficient memory or disk space

Msg 945, Level 14, State 2, Line 1
Database 'db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

One of my friends called me that their application is giving error message of disk space , and i asked him to increase the space of drive. later he responded me that he shrinked the files and space has been raised to 70GB, but the error is still there.
I asked him to access the Database through management studio
When he tried to access the database, through a query or by trying to look at the database properties in Management Studio, he got the following message:

Database 'db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. 


Here is Solution/Fix/workaround of this problem.

1. check the DB status, most of the time , it will return 1

use master
select databaseproperty('dbname','isShutdown')

2. Change the database to offline to clear the db status

use master
alter database dbname set offline

3. Now change the database to online, at this step log file and data files will be verified by sql server


use master
alter database dbname set online


This solution solved the problem.

SQL SERVER – SSMS Automatically Generates TOP (100) PERCENT in Query Designer

SQL SERVER – SSMS Automatically Generates TOP (100) PERCENT in Query Designer:

Earlier this week, I was surfing various SQL forums to see what kind of help developer need in the SQL Server world. One of the question indeed caught my attention. I am here regenerating complete question as well scenario to illustrate the point in a precise manner. Additionally, I have added added second part of the question to give completeness.

Question:

I am trying to create a view in Query Designer (not in the New Query Window). Every time I am trying to create a view it always adds  TOP (100) PERCENT automatically on the T-SQL script. No matter what I do, it always automatically adds the TOP (100) PERCENT to the script. I have attempted to copy paste from notepad, build a query and a few other things – there is no success. I am really not sure what I am doing wrong with Query Designer.

Here is my query script: (I use AdventureWorks as a sample database)

SELECT Person.Address.AddressID

FROM Person.Address INNER JOIN

Person.AddressType ON Person.Address.AddressID = Person.AddressType.AddressTypeID

ORDER BY Person.Address.AddressID

This script automatically replaces by following query:

SELECT TOP (100) PERCENT Person.Address.AddressID

FROM Person.Address INNER JOIN

Person.AddressType ON Person.Address.AddressID = Person.AddressType.AddressTypeID

ORDER BY Person.Address.AddressID

However, when I try to do the same from New Query Window it works totally fine. However, when I attempt to create a view of the same query it gives following error.

Msg 1033, Level 15, State 1, Procedure myView, Line 6

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

It is pretty clear to me now that the script which I have written seems to need TOP (100) PERCENT, so Query . Why do I need it? Is there any work around to this issue.

I particularly find this question pretty interesting as it really touches the fundamentals of the T-SQL query writing. Please note that the query which is automatically changed is not in New Query Editor but opened from SSMS using following way.

Database >> Views >> Right Click >> New View (see the image below)

Answer:

The answer to the above question can be very long but I will keep it simple and to the point. There are three things to discuss in above script 1) Reason for Error 2) Reason for Auto generates TOP (100) PERCENT and 3) Potential solutions to the above error. Let us quickly see them in detail.

1) Reason for Error

The reason for error is already given in the error. ORDER BY is invalid in the views and a few other objects. One has to use TOP or other keywords along with it. The way semantics of the query works where optimizer only follows(honors) the ORDER BY in the same scope or the same SELECT/UPDATE/DELETE statement. There is a possibility that one can order after the scope of the view again the efforts spend to order view will be wasted. The final resultset of the query always follows the final ORDER BY or outer query’s order and due to the same reason optimizer follows the final order of the query and not of the views (as view will be used in another query for further processing e.g. in SELECT statement). Due to same reason ORDER BY is now allowed in the view. For further accuracy and clear guidance I suggest you read this blog post by Query Optimizer Team. They have explained it very clear manner the same subject.

2) Reason for Auto Generated TOP (100) PERCENT

One of the most popular workaround to above error is to use TOP (100) PERCENT in the view. Now TOP (100) PERCENT allows user to use ORDER BY in the query and allows user to overcome above error which we discussed. This gives the impression to the user that they have resolved the error and successfully able to use ORDER BY in the View. Well, this is incorrect as well. The way this works is when TOP (100) PERCENT is used the result is not guaranteed as well it is ignored in our the query where the view is used. Here is the blog post on this subject: Interesting Observation – TOP 100 PERCENT and ORDER BY. Now when you create a new view in the SSMS and build a query with ORDER BY to avoid the error automatically it adds the TOP 100 PERCENT. Here is the connect item for the same issue. I am sure there will be more connect items as well but I could not find them.

3) Potential Solutions

If you are reading this post from the beginning in that case, it is clear by now that ORDER BY should not be used in the View as it does not serve any purpose unless there is a specific need of it. If you are going to use TOP 100 PERCENT with ORDER BY there is absolutely no need of using ORDER BY rather avoid using it all together. Here is another blog post of mine which describes the same subject ORDER BY Does Not Work – Limitation of the Views Part 1. It is valid to use ORDER BY in a view if there is a clear business need of using TOP with any other percentage lower than 100 (for example TOP 10 PERCENT or TOP 50 PERCENT etc). In most of the cases ORDER BY is not needed in the view and it should be used in the most outer query for present result in desired order. User can remove TOP 100 PERCENT and ORDER BY from the view before using the view in any query or procedure. In the most outer query there should be ORDER BY as per the business need.

I think this sums up the concept in a few words. This is a very long topic and not easy to illustrate in one single blog post. I welcome your comments and suggestions.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, SQL View, T SQL, Technology

Lengh of LOB data to be replicated exceeds configured maximum 65536

Whenever you try to replicate data from a database that saves the images(filestream) in the database, and you have included those images in your replication, then you might get this error

Length of LOB data (583669) to be replicated exceeds configured maximum 65536

 Solution:

There are two solutions for this problem 

• using TSQL
• sp_configure 'max text repl size', '2147483647'
  GO
  RECONFIGURE 
• using SQL Server Manager Studio
• In Object Explorer, Right click on your server name and select properties 
• Click the Advanced node
• Under Miscellaneous option, change Max Text Replication Size to the desired value.

Attached failed, Unable to open physical file, operating system error

Some time when you try to attach database files (.mdf, . ldf) to sql server , you might get the error
"Unable to open physical file, operating system error"

The reason behind this is that the account from which you are accessing the file , does not have rights for this specific folder or file.

Solution for this is that , you have to give the rights. In my case i have given the rights to the local administrator

After giving the rights , try to attach the file again.

I have attached the database successfully.

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column

I was trying to add a filestream column in one of my existing tables, initially i thought it would be very easy for me, but when i got the error "A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column."  i started to look here and there. Surfed on the net but didn't find a solution.
Below is the solution which i tried.

Workaround to produce the problem again

CREATE TABLE Employee (
    [emp_id] [int] IDENTITY(1,1) NOT NULL,
    [emp_name] [nvarchar](150) NULL,
    [emp_FHname] [nvarchar](150) NULL,
    [emp_age] [int] NULL,
    [emp_sex] [bit] NULL,
 CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED
(
    [emp_id] ASC
)
) ON [PRIMARY]

GO
emp_id is defined as IDENTITY and Primary key

now i wants to add a filestream enabled column named "emp_pic" to store employee pictures, and the table doesn't have any ROWGUIDCOL so i added a empid

ALTER TABLE Employee ADD
    empid UNIQUEIDENTIFIER DEFAULT (newid()) ROWGUIDCOL NOT NULL

After this i executed this statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM
 
it will give you the error  Msg 5505, Level 16, State 1, Line 1
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

To solve this problem i just changed my primary key column to empid that i added later as ROWGUIDCOL
After changing the primary key, execute the statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM
It will be executed successfully

An easy way to get the Definition of an Object

I was using the sp_helptext very frequently to get the definition of an object.

Here is another easy way to get the definition of an object

SELECT OBJECT_DEFINITION(object_id) FROM sys.objects WHERE type='V'

You can use the, ALTER in the result set of the above query by using the REPLACE.

In the where condition you can specify the Procedure(P) or Functions(FN) as well.

Difference between NEWSEQUENTIALID() and NEWID()

In this article, we will be looking at the Difference between NEWSEQUENTIALID() and NEWID() and How NEWSEQUENTIALID() is generated.

NEWSEQUENTIALID() generates the unique identifier values sequentially.
Whereas NEWID() generates the unique identifier values randomly.

NEWSEQUENTIALID() function creates the GUIDs greater than the previously generated GUIDs, since the last restart of the system, because after restarting the system the next NEWSEQUENTIALID can be started from the lower range.
Both NEWID() and NEWSEQUENTIALID() are globally unique. Starting of NEWSEQUENTIALID after the restart of the computer does not affect its globally uniqueness.

NEWSEQUENTIALID() uses the default constraint, and it can't be used in SELECT or SET queries like NEWID().
The following query SELECT NEWSEQUENTIALID() will generate error

The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

This will also generate the similar error

declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

while if you use
SELECT NEWID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

No error will be produced and guid will be returned.

NEWID() is not good for performance because it is generated randomly so that it increases the page splits in the indexes.
NEWSEQUENTIALID() can be used to generate GUIDs to reduce page contention at the leaf level of indexes.

Each GUID generated by using NEWSEQUENTIALID() is unique on that computer.
GUIDs generated by using NEWSEQUENTIALID() are unique across multiple computers only if the source computer has a network card.
NEWSEQUENTIALID() returns the value that includes the MAC of the NIC of the system. If the system has the network card, then it is guaranteed that the GUID generated using NEWSEQUENTIALID() will globally unique across the servers.
If the NEWSEQUENTIALID() used on a computer that doesn't have NIC, the values generated using NEWSEQUENTIALID() are not guaranteed to be unique across the servers, it will be unique only for that system.
Internally NEWSEQUENTIALID() usses a Windows API called UuidCreateSequential(), which generates the value based on the NIC's MAC address and an internal hardware timestamp.

Here you can see the Newsequentialid (Histrory/Benefits and Implementation)

Find Created date and Modified date of Database objects

This script will return the objects in sql server database with modified and created date.
However you can change the order by clause as well as the Where clause according to your requirements

SELECT sys.schemas.name + '.' + sys.objects.name,
create_date,
modify_date
FROM sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE type = 'P'
OR type = 'U'
OR type = 'FN'
OR type = 'V'
ORDER BY modify_date DESC

The newsequentialid() built-in function can only be used in a DEFAULT expression

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.


This error occurs when we try to use the NEWSEQUENTIALID() function in the SQL statements or in SET statements. e.g.

SELECT NEWSEQUENTIALID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique
OR
INSERT INTO AA(id, name) VALUES(NEWSEQUENTIALID(),'abc')

The NEWSEQUENTIALID() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

SQL Server Interview Questions (Part 2)

Few days back, i went for an interview , following questions were asked

• How to Performance Tune a Stored Procedure?
• Type of Triggers?
• How to get the old value of a field from a trigger when updating a table?
• What is the difference between horizontal partition and vertical partition?
• if we have three parameters in the where condition (query has the joins with two or more tables table1.filed1=4 and table2.field2=5 and table3.field3=1)
  what will be the Order of execution of the where condition
  Which condition would be executed first i.e. field1 or field2 or field3?
• What are the disadvantages of the cursors?
• A stored procedure has cursors in it, how we can eliminate the cursors?
• What is the difference between temporary table and table variable?
• How to restore the Differential Backup?
• What are Backup Types and difference between them?
• What are Replication Types, What is snapshot replication?
• What is the difference between Replication and Mirroring?
• Can a sub report in Crystal Reports can have another sub report?
• What is log shipping?
• Should we normalize the Database on more than 3rd Normal form?

What is Parameter Sniffing, How it can affect the Performance, and What is the Solution

What is parameter sniffing? How it can affect the performance. What is solution of parameter sniffing?

Let us try to answer these questions,

Whenever a stored procedure is executed for the first time, its execution plan is created. An execution plan for a stored procedure is created the first time a stored procedure is executed. When the SQL SERVER Database engine compiles a stored procedure it looks at the parameters being passed to the stored procedure and then creates an execution plan based on these parameters. The process of looking at parameter values when compiling a stored procedure is called "Parameter sniffing". Sometimes Parameter sniffing can lead to inefficient execution plans, especially when a stored procedure is called with parameter values that have different cardinality.

Parameter sniffing can be considered a plus to the performance of the system, but in those cases where it's not, various mechanisms can be used to avoid it. One of the simplest is to use local variables. You can also use the OPTIMIZE FOR query hint or the WITH RECOMPILE query hint. In extreme cases you can force specific execution plans onto procedures.

We can also say that Parameter sniffing is a process that occurs when executing a stored procedure for the first time that allows SQL Server to build an effective query plan. Normally that is a good thing, but in some cases can actually hurt performance of the query.

According to the white paper Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 Published in the Microsoft Site:

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation"

When a SP is not in the procedure cache, when it is executed the query optimizer needs to compile the SP to create an execution plan. In order to do this the query optimizer needs to look at the parameters that are passed and the body of the SP to determine the best method to go about processing the SP. When the query optimizer looks at the SP's parameters, to help determine how to optimize the execution of the SP, it is known as parameter sniffing.

Before I show you with an example about parameter sniffing, you need to know that the query execution plan generated by the SQL Server depends on lot of factors; parameter sniffing is just one of them. So the execution plan I show here might be different from the execution plan that you will generate.

Let's look at the following code

USE AdventureWorks

GO

CREATE PROCEDURE GetCustOrders (@FirstCust int, @LastCust int)

AS

SELECT * FROM Sales.SalesOrderHeader

WHERE CustomerID between @FirstCust and @LastCust;

This procedure GetCustOrders accepts two parameters @FirstCust int, @LastCust int . These parameters are passed in the where clause of the query to get the customer orders. Now look at the execution plan of the procedure by execution this procedure by passing different parameters.

USE AdventureWorks

GO

DBCC FREEPROCCACHE

EXEC GetCustOrders 1, 1000

I had used the statement DBCC FREEPROCCACHE to clear the procedure cache so that query optimizer should make a new plan for this execution instead of using any existing one.

Look at the

Actual number of Rows 31465 and

Estimated number of Rows 31465

Here you can see that query optimizer performed a Clustered Index scan operation to get the records.

Now execute EXEC GetCustOrders 600,610 and see the execution plan

It uses the Clustered index scan for this operation as well, because this plan was present in the procedure cache.

Check the number of actual rows and estimated rows,

Actual number of Rows 31465

Estimated number of Rows 31465

Note the in both cases the actual and estimated number of rows are same.

Now clear the procedure cache and execute the procedure with smaller values

DBCC FREEPROCCACHE

EXEC GetCustOrders600,610

Exmaince the actual number of rows and estimated number of rows, they are different now, because optimizer created a new plane for this

Query optimizer performed the seek operation. This means that by passing the smaller range of values , you can get the Index Seek operation, depending on your execution. If you execute the procedure with smaller values first and then execute the procedure with larger values. Then you can get Index seek for both of the executions.

The first compile of the Procedure generates the execution plan depending on the the parameters passed to the procedure and that plan is kept in the procedure cache for use of future executions of that procedure.

How to eliminate parameter sniffing?

There are three methods to avoid parameter sniffing,

By using local variables in the procedure By using RECOMPILE Query Hint Query hint OPTIMIZE FOR

By using Local variables

Parameter sniffing can be disabled by using the local variables in the stored procedure.

CREATE PROCEDURE GetCustOrders (@FirstCustint, @LastCustint)

AS

DECLARE @FC int/td>

DECLARE @LC int

SET @FC = @FirstCust

SET @LC = @LastCust

SELECT * FROM Sales.SalesOrderHeader

WHERE CustomerID BETWEEN @FC AND @LC

You can create two local variables @FC AND @LC that takes the values from parameters of the stored procedure and then pass those values to the query. In this way the actual values of the parameters are no longer contained in the BETWEEN clause in the SELECT statement, instead only those local variables are present.

This method of removing the parameter sniffing problem doesn’t mean you will get a best plan for each execution of the SP

By using Recompile

Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure

USEAdventureWorks

GO

CREATE PROCEDURE GetCustOrders(@FirstCust int,@LastCust int)

WITH RECOMPILE

AS

SELECT*FROMSales.SalesOrderHeader

WHERECustomerIDbetween@FirstCustand@LastCust ;

By using Optimize For

Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization

CREATE PROCEDUREGetCustOrders(@FirstCustint,@LastCustint)

WITH RECOMPILE

AS

SELECT*FROMSales.SalesOrderHeader

WHERECustomerID between @FirstCustand @LastCust

OPTION (OPTIMIZE FOR (@FirstCust UNKNOWN,@LastCust UNKNOWN));

go

You can read more about Query Hints from msdn

SQL Load Generator

A really nice tool for Load testing on SQL Server. SQL Load Generator is used to generate a load simulating several different users. It is is used to run multiple concurrent queries against SQL Server. The user can choose the number of concurrent queries to run, provide different queries, choose SQL or domain accounts, and provide application name settings.

read more or if you want to download SQL Load Generator you need to go on codeplex SQL Load Generator

Unable to start T-SQL Debugging. Could not attach to SQL Server Process

How to Enable TSQL Debugging in SQL SERVER ?


Click on the menu Debug >> Start Debugging
An error will be produced "Unable to start T-SQL Debugging. Could not attach to SQL Server Process"

Here is the solution to get rid of this error

Expand the SQL Sever Management Studio
Expand the Security

Right Click on the Logins
Click on New Login
A new window will appear to define the Login

Specify the Login Name.
Specify the Server roles.

Check the sysadmin Server role and then press OK.
Now Debug your Query /SP Debug >> Start Debugging

This can also be done using TSQL.
Here is the Way to do this using TSQL

sp_addsrvrolemember 'Domain\Name', 'sysadmin'
GO

EXEC master..sp_addsrvrolemember @loginame = N'Dmain-Name\Administrator', @rolename = N'sysadmin'
GO

if you are not using any domain, then try your system name.

SQL Server Performance Killers

There might be several individual factors that can kill the performance of your database. If you aware of the main performance killers in SQL Server in advance, you will be able to focus your tuning efforts on the likely causes.
You also need to look the hardware, operating and SQL Server settings
The main performance killers in SQL SERVER performance are as follows

• Poor Indexing
• Inaccurate statistics
• Excessive blocking and deadlocks
• No-set-based operations, usually T-SQL cursors
• Non set-based thinking leads to excessive use of cursors and loops rather than exploring more efficient joins and sub-queries. SQL has rich mechanism to get the data instead of trying to loop or row by row approach to be used in the code, this will kill the performance

• Poor query design
• Querying the data from a table that is not required, or using a table in joins that is not required.
• Poor database design
• No reusable execution plans
• Poor execution plans, usually caused by parameter sniffing
• Frequent recompilation of execution plans
• Improper use of cursors
• Always use set based tsql query. By using cursors, you add a large amount of overhead on SQL SERRVER.

• But if you are forced to use cursors, try to use the efficient cursor types such as fast-forward only.
• Remember cursors are called the performance killers.
• Improper configuration of database log
• Excessive use or improper use configuration of tempdb
• Every SQL Server instance has only one tempdb, which is used to store the information such as operations involving temporary tables, table variables, also operations such as sorts and row versioning, tempdb can become a bottleneck.

How to Attach Database in SQL SERVER

This is a step by step walkthrough to attach the Database in SQL SERRVER, if you have data file (.mdf) only and you don’t have log file (.ldf)
I had tried to simplify the steps with the help of screen shots

Connect to your SQL SERVER enterprise manager, in my case I have connected to SQL SERVER 2011 “Denali”
Right click on the Databases, a menu will appear,

Click on Attach, a form Attach Databases will appear

Click on the Add button, file open dialog will appear to select the required file.

Locate your desired data file (.mdf) from your system, and then select that file and then click OK
You will be back again on the Attach Databases dialog, showing the file you have selected. I have selected the AdventureWorks data file.

You can see the row, I highlighted, this is because, I am going to attach the data file with the log file. If you have the log file, then you wouldn’t the message “Not Found”
You can see that currently Remove button is disabled, click on the Row showing the log file

After clicking the Row, Remove button will be enabled, and then click on the Remove button
The Row containing the log file will be removed. If you don’t remove the row containing the log file, the Database wouldn’t be attached, and it will give the error “An error occurred while attaching the Database”.
Click on OK button, the database will be attached successfully.

Sample Database for SQL Server 2011 Denali CTP1

Sample Database AdventureWorks for SQL Server Denali CTP1 can be downloaded from AdventureWorks Database

This database was used with SQL SERVER 2008R2 and now with 2011 as well

How to Install SQL SERVER 2011

This is step by step Walkthrough installation guide with images for SQL SERVER 2011
The installation of SQL SERVER 2011 is very much similar to SQL SERVER 2008, If you have already installed SQL SERVER 2008, You might not face any problem.

Microsoft has launched SQL SERVER 2011 "Denali"; I have downloaded MICROSOFT SQL SERVER "Denali" from

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en


I hope SQL Server database professionals like administrators and t-sql developers find my SQL SERVER 2011 setup notes useful.
The Operating System requirements for Microsoft SQL Server 2011 "Denali" are
Windows Vista with SP2,
Windows Server 2008 with SP2,
Windows 2008 R2, and
Windows 7 operating systems.
What is interesting related with the supported operating systems list is Windows XP is not supported any more.
Although I have successfully installed MS SQL Server 2008 R2 Developer Edition on my Windows XP PC, SQL Server 2011 Denali CTP1 cannot be installed.
Here is a short list which you can review:
Windows PowerShell 2.0
Microsoft .NET Framework 3.5 SP1
Microsoft .NET Framework 4.0
Please note that these requirements for SQL Server Denali CTP may change with the new releases of Microsoft SQL Server 2011 and SQL Server Setup Application wizard.
Hardware and software requirements can also be seen from
http://msdn.microsoft.com/en-us/library/ms143506%28SQL.110%29.aspx

This is the step by step installation guide of the SQL SERVER 2011 “Denali” on windows 7
I have installed x86 version of the product, you can proceed according to your system.

Extract the file at your desired location.

On start of setup, the first screen will be

These screens are very much similar to the SQL Server 2008.
After a while, next screen will appear

To view the hardware and software requirements for SQL Server 2011, visit the following Microsoft web site
http://msdn.microsoft.com/en-us/library/ms143506%28SQL.110%29.aspx

On selection of New SQL Server stand-alone installation, the following screen will appear. On this screen Setup Support Rules will be executed to identify problems that might occur during installation SQL Server Setup support files. Before continuing to installation process, the failures listed must be corrected

No Reboot package error is found.
Reboot Package: : This rule determines whether the computer has the required update package that ensures that the computer will not have to be rebooted because of the Microsoft .NET Framework 4 installation.

If you are installing Microsoft SQL Server 2011 (aka Denali) on Windows 7 or on Windows 2008 R2 OS computer or server, please download the .NET 4.0 update from KB Article 958488
If you are installing Microsoft SQL Server 2011 on Windows Vista SP2 or on Widows Server 2008 SP2 operating system, then please download Microsoft .NET Framework 4.0 update from KB Article 956250
After the installation of .Net Framework 4.0, system will start, and after resuming the setup again, the screen will be displayed

After clicking OK button, the next screen will be of Product key, I prefer to use Microsoft SQL Server 2011 Enterprise Edition so I select Enterprise Evaluation from dropdown list.
Select the Enterprise Evaluation.

You must accept the license terms in order to continue the installation of Microsoft SQL Server 2011 (SQL Server Denali CTP).Accept the license terms in order to continue to Microsoft SQL Server 2011 installation (SQL Server Denali CTP).and then click next.

Click Install button to install SQL Server 2011 Setup Support files. SQL SERVER will install the setup support files that necessary during the installation.
Click Next button after selecting Microsoft SQL Server 2011 Denali CTP 1 features.
I just select the following Microsoft SQL Server 2011 features.
Click Next button for selecting among Microsoft SQL Server 2011 Denali CTP 1 features.

Select the SQL SERVER 2011 “Denali” features according to your requirements. I have selected all the features except SQL Server Replication, Full-Text Search, Analysis Services and Reporting Services for faster installation of SQL Server Setup

After SQL Server 2011 features selection for setup process, the installation rules are applied and give reasons for a possible failure.

On this screen (Instance Configuration), you can change the instance name of the system. This screen will also show the instances that already installed on the machine.
I have kept the default instance because I don’t have SQL Server installed on my machine.When you click on the Next button, the following screen will be displayed.

Information about the disk space required and available is displayed on this screen. Click the Next button to proceed.

This is screen is used for configuration of SQL Server. This screen will be used to specify the accounts under which the SQL SERVER services will run.
I have used the Windows NT service account, if you want to the same, then click on the button “Use the Same account for all SQL Services”. After clicking following screen will be displayed.

You can see the selection of NT AUTHORITY/SYSTEM. When you click OK, you will be back on SERVER CONFIGURATION screen.

On server configuration, you can also specify the startup type.ie You wants to start the service automatically , when the windows starts or you wants to start the service later from control panel > administrative tools > services.
You can also go to your services panel by just entering services.msc on your windows Start >Run

You can also specify the collation (Collation refers to a set of rules that determine how data is sorted and compared) for this instance of SQL Server.
I have kept the default settings for collation.
Click the Next, after doing necessary configurations.

This screen is used to give specify the Database Engine Configuration. From this screen we specify the authentication mode, Directories for SQL SERVER and Configuration for FILESTREAM. We will use the windows authentication. Click on “Add Current User”, the windows user will be added. Click on Data Directories tab.

You can change the path of SQL SERVER data directories. I have kept all the default options for this page.
Click on FILESTREAM tab.

You can configure the FILESTREAM from this screen. Just enable all the options specified. I didn’t configure the FILESTREAM so I didn’t make any changes on this page.
Click the next button to proceed.

This screen is used to send the errors that might occur during the installation of SQL SERVER.
Click the Next button.

On this screen SQL SERVER checks few rules, there is nothing to do on this screen.
Click Next

This is the last screen to start the installation. On this screen, SQL SERVER shows the features/components that are going to be installed.
Click the install button to start the installation.

This screen will be displayed at the END of installation process. It means that your installation of SQL SERVER is completed.

Select SQL Server Management Studio, this will be come under
Start >> Microsoft SQL Server Denali CTP1>>SQL Server Management Studio