Windows Internal Database (SSEE)

The Windows Internal Database is a special variant of Microsoft SQL Server 2005 Express. It is included with Windows Server 2008, Windows Server Update Services (WSUS), Windows SharePoint Services (WSS) and other Windows Server tools. It is also referenced as SQL Server 2005 Embedded Edition (SSEE).

The Windows Internal Database is used by Active Directory, WSS, WSUS and other Windows Server services as their data storage. Some of its main characteristics are:

• It cannot be used as a regular SQL Server instance as it is intended to be only used by Windows Services/Programs.
• It cannot be removed by using the "Add or Remove Programs" tool because it does not appear in the list of currently installed programs (more info here). Note that it is not recommended to uninstall SSEE as it might affect the operation of Windows Services that use it.
  
• It only supports Windows Authentication.
• You can only connect to the instance using Named Pipes.
  

In some cases where you might need to access this special instance of SQL Server (i.e. for reducing the transaction log size of a database) you must use the Named Pipes protocol for doing so. Named Pipes can be enabled from Network Configuration in SQL Server Configuration Manager.

Fist of all you will need to install SQL Server 2005 Express Edition Management Studio (link).
Then you will need to use the following connection properties:

• Server Type: Database Engine
• Server Name: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
• Authentication: Windows Authentication
  

You have to be careful when accessing the Windows Internal Database as many Windows Services depend on it.

Read more on this article...

ADO .NET Data Services for the Web / Visual Studio 2010: A First Look

Another Cyprus .NET User Group special event took place a few days ago (June 18th, 2009). This event was somehow "celebratory". It was the last offline event before summer holidays. The next offline event (within the series of Community Nights) will take place after two months time, in September 2009. During the summer months (July and August) we will be having online events (live meeting sessions).

Due to the nature of this event, we had two sessions! The first session, dedicated to ADO .NET Data Services, was presented by myself. The second session was dedicated to Visual Studio 2010, where we had the joy to discuss the upcoming release of Visual Studio with Karl Davies-Barrett, Developer Platform Evangelist for Microsoft Malta & Cyprus, and great friend and supporter of our User Group.

ADO .NET Data Services (also known under the code name "project Astoria") is a new, exciting Microsoft technology which enables the developer to create robust data services for integration with the web. I will be soon publishing a series of posts on my blog dedicated to ADO .NET Data Services as with combining with ADO .NET Entity Framework it provides fast, scalable and robust data access!

Visual Studio 2010 provides the .NET developer with a huge variety of new enhancements in areas such as: Application Lifecycle Management, Developer Experience, Cloud Development, Web Development and last but not least, Parallel Programming.

The event was a total success!

A big thank you to Karl for his valuable contribution to our event!

For more information on our community events/activities check out our User Group's website! You can also check out the INETA-Europe Cyprus blog session.

Make sure you check back soon as the CDNUG's Live events will be announced soon!

Read more on this article...

Upcoming CDNUG Event, June 18th - ADO .NET Data Services for the Web / Visual Studio 2010: A First Look

It is time for another CDNUG event! This event will probably be the last event of CDNUG before summer holidays. The next event will take place next September.

To this end, the CDNUG committee decided that this event should feature a variety of .NET and SQL Server Technologies thus providing a taste of all their great features.

This month’s event will have two hourly sessions. In the first session, I will talk about the ADO .NET Data Services with focus on the Web. In the second session, Karl Davies-Barrett (Developer Platform Evangelist for Microsoft Malta & Cyprus and great friend of our User Group) will present Visual Studio 2010.

See below for event-specific details:

------------------------------------------------------
Topic 1: ADO .NET Data Services for the Web
Speaker: Artemakis Artemiou

Topic 2: Visual Studio 2010: A First Look
Speaker: Karl Davies-Barrett

When: Thursday, June 18th, 2009
Time: 19:00
Where: Microsoft Office (11 Limassol Avenue, Nicosia)

Registration: Please signup on CDNUG Yahoo polls.
For more details check our website at www.cdnug.net
------------------------------------------------------

If it happens and you are in Cyprus, you are more than welcome to attend this special event!!!

Read more on this article...

How to rebuild all the indexes of a database in SQL Server

In one of my previous posts, I talked about Index Fragmentation in SQL Server in terms of how to track it and how to resolve it.

The techniques explained in that post provided ways of reorganizing/rebuilding specific indexes or all the indexes within a given table. But what about when the DBA needs to rebuild all the indexes within a database? How can he achieve this?

It is a fact that in some cases where a large amount of indexes in a database on SQL Server has a large percentage of fragmentation, then the recommended approach is to rebuild those indexes. To this end, in the worst scenario, the DBA will need to rebuild the indexes in all the tables of the database.

My previous post on the topic explained ways of rebuilding specific or all the indexes within a table.

Under normal circumstances there is not a direct way allowing to rebuild all the indexes of a database with a single command. A workaround is to run different rebuild statements for each table.

Though, I know that workarounds are not very desirable in many cases as they might demand a large amount of time :)

There is not need to worry :) In SQL Server there is the undocumented stored procedure sp_MSforeachtable which allows for recursively executing a T-SQL statement (or more) for all the tables within a database with the use of a single line of code.

Hereunder I propose the syntax on how to rebuild all the indexes of a given database by utilizing the sp_MSforeachtable stored procedure.

SQL Server 2000
=============

--Rebuild all indexes with keeping the default fill factor for each index
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

--Rebuild all indexes with specifying the fill factor
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', [FILL_FACTOR_PERC])"


SQL Server 2005/2008
=================

You can either use the syntax provided above for SQL Server 2000 or:

--Rebuild all indexes online with keeping the default fill factor for each index
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON)"

--Rebuild all indexes offline with keeping the default fill factor for each index
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"

--Rebuild all indexes online with specifying the fill factor
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=ON)"

--Rebuild all indexes offline with specifying the fill factor
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=OFF)"


Considerations
==========
DBCC DBREINDEX is always an offline operation.
Online index rebuild fails for the following cases:

• XML index
• Spatial index
• Large object data type columns: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml

I hope you found this post useful. Drop me a line if you have any comments or questions!

Read more on this article...

Screencast: Using Inheritance in the ADO .NET Entity Framework

Welcome to another screencast dedicated to SQL Server 2008 Programmability!

This screencast again features the ADO .NET Entity Framework.

The screencast demonstrates how the developer can create an entity data model in Visual Studio 2008, and goes one step further, thus demonstrating a simple way of using inheritance in the entity data model.


Read more on this article...