:::: MENU ::::
Browsing posts in: SQL Server

SQL Server 2008R2 compare editions


Features Supported by the Editions of SQL Server 2008 R2

Comparison of editions on Microsofts ‘flashy’ site

On MSDN site: http://msdn.microsoft.com/en-us/library/cc645993.aspx#High_availability


SQL Server Developer edition have all features supported by SQL Server Datacenter, they match or surpass Enterprise edition.


Scalability and performance

Feature Name Enterprise Standard Express
Number of CPUs 8 4 1
Maximum memory utilized 2 TB 64 GB 1 GB
Maximum database size 524 PB 524 PB 10 GB
Table and index partitioning Yes
Parallel consistency checks (DBCC) Yes
Parallel index operations Yes












High availability

Feature Name Enterprise Standard Express
Online system changes Yes Yes Yes
Log shipping Yes Yes
Database mirroring Yes Yes (single thread, synchronous only) Witness only
Backup compression Yes Yes
Database snapshots Yes
Online indexing Yes














Feature Name Enterprise Standard Express
Snapshot replication Yes Yes Subscriber only
Merge replication Yes Yes Subscriber only
Transactional replication Yes Yes Subscriber only








Management tools

Feature Name Enterprise Standard Express
SQL Server Management Studio Yes Yes
SQL Server Agent Yes Yes
SQL Server Profiler Yes Yes Yes (with tools option)






Get SQL Server portnumber

— Show port number for SQL 2000/2005/2008/2008R2 Version

set nocount on
 DECLARE @SqlPort Nvarchar(10)
 DECLARE @instance_name Nvarchar(30)
 DECLARE @reg_key Nvarchar(500)
 Declare @value_name Nvarchar(20)

— Scan for SQL 2008R2

 if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.50'
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
 set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'
 --set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
 @key=@reg_key, @value_name='TcpDynamicPorts',
 @value=@SqlPort output
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port

— Scan for SQL 2008

 if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.0.'
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
 set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'
 --set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
 @key=@reg_key, @value_name='TcpDynamicPorts',
 @value=@SqlPort output
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port

— Scan for SQL 2005

if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
 set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
 set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
 @key=@reg_key, @value_name='TcpPort',
 @value=@SqlPort output
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port

— Scan for SQL 2000

if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
Create table #Port_2000 (value nvarchar(20),Data nVarchar(10))
 insert into #Port_2000 exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Supersocketnetlib\tcp', 'tcpPort'
 select @SqlPort = Data from #Port_2000
 select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port
 drop table #Port_2000

Restoring system databases

Just links to where to look for information.

Start here Considerations for Backing Up and Restoring System Databases

SQL Server version 2005 links below, from them you have the option to choose the same document but for another version of SQL Server.

Configure Server Startup Options

Sometimes you will need to start the instance in special modes, see below for more information,

Shortcut to How to: Configure Server Startup Options (SQL Server Configuration Manager)

Restore master database

This topic explains how to restore the master database from a full database backup. For this to work the master database must be ok so the instance can start, if the master database is damaged the instance won’t start and you have to first rebuild the master, see “Rebuild master” below.

Shortcut to How to: Restore the master Database (Transact-SQL)

Rebuild master database

When damaged, the master database must be rebuilt if:

  • A current backup of master is not available.
  • A backup of master exists, but it cannot be restored because the instance of Microsoft SQL Server cannot start.

See how to rebuild the master database, Considerations for Rebuilding the master Database

Restore msdb or model database

If you lost the old msdb or model database, either by it being corrupt, data is lost or because of a master database rebuild, you can restore a backup.

Shortcut to Considerations for Restoring the model and msdb Databases

Extract LiteSpeed backups to native format


If you don’t want  or can’t use LiteSpeed to restore a backup you have to extract it to native SQL Server format. Quest has a commandline tool for this purpose, no gui here but it is simple to use and pretty straightforward.

Usually placed in C:\Program Files\Quest Software\LiteSpeed\SQL Server.

The program’s name is extractor.exe. If you can’t find it easily then search for this name.
The syntax is :
extractor.exe -F [path to LiteSpeed backupfile] -E [native extracted backupfile]

Example: You have a LiteSpeed compressed backup C:\temp\testDB.bak that you want to extract to C:\temp\testDB_native.bak

extractor.exe -F  C:\temp\testDB.bak -E C:\temp\testDB_native.bak
Here comes the part where almost everyone wonders what went wrong, instead of one backupfile we get multiple, that is because LiteSpeed used multiple threads when executing the backup, resulting in multiple files, compressed together. When extracting these to native we get a striped backup set, with the number of files in the stripe matching the number of threads used by LiteSpeed. So when checking our native extracted backups we can get for example:


Well how do we restore these then? The same way we always restore a striped backup, we restore them by specifying all files in the backup, not just one as it usually is.
In this case using SSMS we would specify all 3 files, see screenshot below:

Or in TSQL, the exact same command:

FROM  DISK = N’C:\temp\testDB_native.bak2′,
DISK = N’C:\temp\testDB_native.bak1′,
DISK = N’C:\temp\testDB_native.bak0′

Count rows without count(*)

Running a count(*) on a huge table can sometimes take too long, and is not very efficient, a faster way is to check with the system index views. The drawback can be that they are not updated with the absolute latest data, so before using in a production environment test your solution, I use this mostly as a way to get an overview of how many rows a table holds.

Depending on SQL version there are a couple of ways to do this:

SQL Server 2000, check with the system table sysindexes

SELECT [rows]
 FROM sysindexes WITH (NOLOCK)
 WHERE id = OBJECT_ID(N'your table')
 AND indid < 2

SQL Server 2005 and later, using a compatibility view to mimic the 2000 behaviour

SELECT [rows]
 FROM sys.sysindexes WITH (NOLOCK)
 WHERE id = OBJECT_ID(N'your table')
 AND indid < 2

SQL Server 2005 and later using an object catalog view

 FROM sys.partitions
 object_id = OBJECT_ID(N'your table')
 AND index_id IN(0,1)

Test these with SET STATISTICS ON and compare to running a count(*), there will be a difference, not so much on small tables, but once you get to tables with millions or billions of rows there will be a huge difference.

Random password stored procedure

Often you as a DBA or developer is required or asked or just need to create a good password, this is a simple stored procedure to create a default 15 character random password, the procedure can also receive a variable requesting a specific length password, see examples below.

CREATE procedure [dbo].[GeneratePass]
@passlen int = 15
set nocount on
if (@passlen > 8000 or @passlen < 1) -- Sanity check :-)
select @passlen = 15

declare @password varchar(8000), @string varchar(256), @numbers varchar(10), @extra varchar(50),
@stringlen int, @index int

-- no 1, l, I, 0, O which can cause confusion
select @string = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz'
select @numbers = '0123456789'
select @extra = '!' -- add more special characters if you want

select @string = @string + @numbers + @extra
select @stringlen = len(@string)

select @password = ''

while (@passlen > 0)
select @index = (abs(checksum(newid())) % @stringlen) + 1
select @password = @password + substring(@string, @index, 1)
select @passlen = @passlen - 1
select @password

Run it with default 15 chacters:

exec GeneratePass

Or choose your desired number of characters, in this case 30 for a very strong password:

exec GeneratePass @passlen=30

Setup users/logins when using mirroring

Scenario: You mirror a database from one SQL Server instance to another, were assuming SQL Server 2005 version or later.

You setup your  database on the primary instance, or principal as MS likes to call it, create logins and users, run it through testing and everything checks out fine. No problems there :-)

Ok, to setup mirror database you create these same logins and take a backup of the principal database, restore it in ‘no recovery’, setup some mirroring permissions and start mirroring. Again no problems.

Now we want to test what happens when we failover the database to mirror instance, easy enough, just use SSMS and click on Failover for the database in question, failover works like a charm and everyones happy……for a couple of seconds until you realize that the application login is no longer mapped to the application user in the database!! Bad!!

So, what the hell happened??

What you should have done when creating the logins on the mirror instance is to script them out from the principal with the correct SID as it’s the SID that defines the connection between login and user.

I use the script below for this to script out logins with correct password and sid, have worked great for me, customize it for your needs, and as always, it’s provided as is, use it carefully and understand what you’re doing.

select 'create login [' + p.name + '] ' +
 case when p.type in('U','G') then 'from windows ' else '' end +
 'with ' +
 case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_policy = ' +
 case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_expiration = ' +
 case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
 case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
 else '' end +
 'default_database = ' + p.default_database_name +
 case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on  p.principal_id = l.principal_id
left join sys.credentials c
on  l.credential_id = c.credential_id
where p.type in('S','U','G')
and  p.name <> 'sa'
order by p.name

The result of the script will have several rows with all logins, remove those you don’t want to copy, example row below:

create login [restoretest] with password = 0x01005f8060713f41d4e6b1f861a18851190b6626796d826e205f hashed,
sid = 0x6c6510e8b79c674f821737f0312c1d72,
check_policy = OFF,
check_expiration = OFF,
default_database = master,
default_language = us_english


SA login locked

So, someones tried to hack the SA password and in the process locked it due to too many login failures?

Don’t want to change the password just to unlock it?

Open a connection as some other login with sysadmin rights and run:


sa login unlocked, no problem :-)

Extrahera Litespeed backuper till Native format


Vill man inte använda Litespeed för att restora en backup måste man extrahera den till native format, Quest har ett verktyg för det, ren commandline men den funkar bra.
Brukar ligga i C:\Program Files\Quest Software\LiteSpeed\SQL Server.
Programmet heter extractor.exe
Syntaxen är:
extractor.exe -F [sökväg till litespeed backup] -E [native extraherad backupfil]

Exempel: Du har en backup på C:\temp\testDB.bak som du vill extrahera till C:\temp\testDB_native.bak

extractor.exe -F  C:\temp\testDB.bak -E C:\temp\testDB_native.bak
Nu brukar det ibland bli lite funderingar, man får nämligen ofta multipla backupfiler, det är för att extractor skapar en backupfil per tråd Litespeed använde vid den ursprungliga backupen. Så man kan få:


Hur restorar man dom då? Jo som man alltid gör vid en stripad backup/restore, man specar helt enkelt alla backupfiler vid restoren istället för bara en.
I SSMS skulle man i detta fall ange alla 3 filer, se screenshot nedan:

Eller i T-SQL, exakt samma kommando:

FROM  DISK = N’C:\temp\testDB_native.bak2′,
DISK = N’C:\temp\testDB_native.bak1′,
DISK = N’C:\temp\testDB_native.bak0′

SQL Server data aggregation

Arranging SQL data that you can effectively analyse requires an understanding of how to use certain SQL clauses and operators. These tips will help you figure out how to build statements that will give you the results you want.

Arranging data in a manner that’s meaningful can be a challenge. Sometimes all you need is a simple sort. Often, you need more — you need groups you can analyse and summarise. Fortunately, SQL offers a number of clauses and operators for sorting, grouping, and summarising. The following tips will help you discern when to sort, when to group, and when and how to summarize. For detailed information on each clause and operator, see Books Online.

#1: Bring order with a sort

More often than not, all your data really needs is a little order. SQL’s ORDER BY clause organises data in alphabetic or numeric order. Consequently, similar values sort together in what appear to be groups. However, the apparent groups are a result of the sort; they aren’t true groups. ORDER BY displays each record whereas a group may represent multiple records.

#2: Reduce similar values into a group

The biggest difference between sorting and grouping is this: Sorted data displays all the records (within the confines of any limiting criteria) and grouped data doesn’t. The GROUP BY clause reduces similar values into one record. For instance, a GROUP BY clause can return a unique list of ZIP codes from a source that repeats those values:

SELECT ZIP FROM Customers GROUP BY ZIP Include only those columns that define the group in both the GROUP BY and SELECT column lists. In other words, the SELECT list must match the GROUP BY list, with one exception: The SELECT list can include aggregate functions. (GROUP BY doesn’t allow aggregate functions.)

Keep in mind that GROUP BY won’t sort the resulting groups. To arrange groups alphabetically or numerically, add an ORDER BY clause (# 1). In addition, you can’t refer to an aliased field in the GROUP BY clause. Group columns must be in the underlying data, but they don’t have to appear in the results.

#3: Limit data before it’s grouped

You can limit the data that GROUP BY groups by adding a WHERE clause. For instance, the following statement returns a unique list of ZIP codes for just Kentucky customers:

SELECT ZIP FROM Customers WHERE State = 'KY' GROUP BY ZIP It’s important to remember that WHERE filters data before the GROUP BY clause evaluates it.

Like GROUP BY, WHERE doesn’t support aggregate functions.

#4: Return all groups

When you use WHERE to filter data, the resulting groups display only those records you specify. Data that fits the group’s definition but does not meet the clause’s conditions won’t make it to a group. Include ALL when you want to include all data, regardless of the WHERE condition. For instance, adding ALL to the previous statement returns all of the ZIP groups, not just those in Kentucky:

SELECT ZIP FROM Customers WHERE State = 'KY' GROUP BY ALL ZIP As is, the two clauses are in conflict, and you probably wouldn’t use ALL in this way. ALL comes in handy when you use an aggregate to evaluate a column. For example, the following statement counts the number of customers in each Kentucky ZIP, while also displaying other ZIP values:

SELECT ZIP, Count(ZIP) AS KYCustomersByZIP FROM Customers WHERE State = 'KY' GROUP BY ALL ZIP The resulting groups comprise all ZIP values in the underlying data. However, the aggregate column (KYCustomersByZIP) would display 0 for any group other than a Kentucky ZIP.

Remote queries don’t support GROUP BY ALL.

#5: Limit data after it’s grouped

The WHERE clause (# 3) evaluates data before the GROUP BY clause does. When you want to limit data after it’s grouped, use HAVING. Often, the result will be the same whether you use WHERE or HAVING, but it’s important to remember that the clauses are not interchangeable. Here’s a good guideline to follow when you’re in doubt: Use WHERE to filter records; use HAVING to filter groups.

Usually, you’ll use HAVING to evaluate a group using an aggregate. For instance, the following statement returns a unique list of ZIP codes, but the list might not include every ZIP code in the underlying data source:

SELECT ZIP, Count(ZIP) AS CustomersByZIP FROM Customers GROUP BY ZIP HAVING Count(ZIP) = 1 Only those groups with just one customer make it to the results.

#6: Get a closer look at WHERE and HAVING

If you’re still confused about when to use WHERE and when to use HAVING, apply the following guidelines:

  • WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records.
  • HAVING comes after GROUP BY; SQL evaluates HAVING after it groups records.

#7: Summarize grouped values with aggregates

Grouping data can help you analyse your data, but sometimes you’ll need a bit more information than just the groups themselves. You can add an aggregate function to summarise grouped data. For instance, the following statement displays a subtotal for each order:

SELECT OrderID, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY OrderID As with any other group, the SELECT and GROUP BY lists must match. Including an aggregate in the SELECT clause is the only exception to this rule.

#8: Summarise the aggregate

You can further summarise data by displaying a subtotal for each group. SQL’s ROLLUP operator displays an extra record, a subtotal, for each group. That record is the result of evaluating all the records within each group using an aggregate function. The following statement totals the OrderTotal column for each group:

SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH ROLLUP The ROLLUP row for a group with two OrderTotal values of 20 and 25 would display an OrderTotal of 45. The first record in a ROLLUP result is unique because it evaluates all of the group records. That value is a grand total for the entire recordset.

ROLLUP doesn’t support DISTINCT in aggregate functions or the GROUP BY ALL clause.

#9: Summarise each column

The CUBE operator goes a step further than ROLLUP by returning totals for each value in each group. The results are similar to ROLLUP, but CUBE includes an additional record for each column in the group. The following statement displays a subtotal for each group and an additional total for each customer:

SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE CUBE gives the most comprehensive summarisation. It not only does the work of both the aggregate and ROLLUP, but also evaluates the other columns that define the group. In other words, CUBE summarises every possible column combination.

CUBE doesn’t support GROUP BY ALL.

#10: Bring order to summaries

When the results of a CUBE are confusing (and they usually are), add the GROUPING function as follows:

SELECT GROUPING(Customer), OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE The results include two additional values for each row:

  • The value 1 indicates that the value to the left is a summary value–the result of the ROLLUP or CUBE operator.
  • The value 0 indicates that the value to the left is a detail record produced by the original GROUP BY clause.