SQL SERVER – Dedicated Access Control for SQL Server Express Edition – An error occurred while obtaining the dedicated administrator connection (DAC) port.

Recently I had faced very interesting situation. Due to some reason we were not able to login into the production server for one of client. The reason for the same was that server was very busy, we had to login into the system and bring server to normal situation. When all the attempts failed, I decided to login using Dedicated Administrator Connection (DAC). However when I attempted to connect using DAC it threw following error for me.

C:\Users\pinald>sqlcmd -A -d master -S .\SQLEXPRESS

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for t he port number [xFFFFFFFF]. .Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

I was bit taken a back as I knew that my commands are correct to login and if DAC does not work, there should be some serious reason for it. When inquired further about the SQL Server version I learned that it was SQL Server Express version deployed. To conserve resources, SQL Server Express does not listen on the DAC port. There is an additional step to be done if SQL Server Express has to be used with DAC. Enable TRACEFLAG on SQL Server Express will enable the connection by DAC possible. Here is the quick methods how one can enable DAC on SQL Server Express.

Go to Start >> All Program >>Microsoft SQL Server (your version) >> Configuration Tools >> SQL Server Configuration Manager.

Click on SQL Server Services >> Select your SQL Server Express version >> Right Click Properties >> select Startup Parameters

Once on the Startup Parameter add the Startup parameter which is TRACEFLAG -T7806. Click on OK and RESTART SQL Server Express edition. Now once again try to connect to SQL Server Express edition and it will work just fine.

This is absolutely documented method on BOL and SQL Server Express needs to be restarted.

Watch SQL in Sixty Seconds video to Resolve Connection Error:

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

SQLAuthority News – Download SQL Server 2008 Express Datasheet

Microsoft® SQL Server® 2008 Express is a free edition of SQL Server ideal for learning, developing and powering desktop and small server applications and for redistribution by ISVs.

Download SQL Server 2008 Express Datasheet

Abstract courtesy : Microsoft

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

SQL SERVER – Download FREE SQL SERVER Express Edition and Service Pack 1

Here is the quick link from where SQL Server 2008 Express Edition can be downloaded.

Download SQL Server 2008 Express Edition

You can download it with many additional details as described in following image. Click on above link to go to page and select desired version.

Additionally, please install SQL Server 2008 Express Service Pack 1.

You can read one of my previous article where I have covered SQL Server 2008 Express in detail SQL SERVER – SQL Server Express – A Complete Reference Guide.

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

SQL SERVER – Choose Right Edition of SQL Server Express for Your Application

SQL Server Express is better alternative of MySQL. I have recently helped quite a few organizations to move to SQL Server Express recently. However, one question keep on coming up quite often regarding which is the right edition for SQL Server Express. SQL Server Express have more than one edition available.

Here is the quick guide to select right edition for SQL Server.

After reading above guide if you are still not sure which edition you should select, leave a comment here or send me email and I will get back to you.

SQL Server 2008 Express with Advanced Services – 546.6 MB

  • SQL Server Database Engine
  • SQL Server Management Studio Basic
  • Full-Text Search
  • Reporting Services

SQL Server 2008 Express With Tools – 230.4 MB

  • SQL Server Database Engine
  • SQL Server Management Studio Basic

SQL Server 2008 Express Runtime Only – 82.5 MB

  • SQL Server Database Engine

SQL Server 2008 Express Management Studio Basic - 38.5 MB

  • SQL Server Management Studio Basic

SQL Server 2008 Management Studio Express is also known as SSMSE.

You can refer my complete reference article SQL SERVER – SQL Server Express – A Complete Reference Guide.

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

SQL SERVER – SQL Server Express – A Complete Reference Guide

SQL Server Express is one of the most valuable products of Microsoft. Very often, I face many questions with regard to SQL Server Express. Today, we will be covering some of the most commonly asked questions.

Q: What is the cost of SQL Server Express?
A: SQL Server Express is a FREE product from Microsoft.

Q: Where can I find more details about SQL Server Express?
A: On official Microsoft Site: http://www.microsoft.com/express/sql/default.aspx

Q: Why should I use SQL Server Express when I have full version available?
A: Usually, I install only the license version product on my system. When I do not have to use all the features of SQL Server, I install SQL Server Express.

Q: What is the maximum size per database for SQL Server Express?
A: SQL Server Express supports a maximum size of 4 GB per database, which excludes all the log files. 4 GB is not a very large size; however, if database is properly designed and the tables are properly arranged in a separate database, this limitation can be resolved to a certain extent.

Q: Can MySQL be compared with SQL Server Express as both of them are free products?
A: Yes. Both are free and have their own space. However, thus far, I have never faced a situation wherein the task that can be accomplished by using MySQL is not accomplished by SQL Server Express.

Q: Does SQL Server have sufficient help available online?
A: Yes. There are so many online help resources available for SQL Server Express that user will find strongest community for any FREE product.

Let me give you quick list of the online help resources. Even though I took some time to build this reference list, I am sure that all of you will appreciate this and will share with others, which is worth the effort!

Overview

Getting Started

Installation

Quick Start

Misc

Blogs

Webcasts

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

SQL SERVER – Fix : Error: 15372 Failed to generate a ser instance od SQL Server due to a failure in starting the process for the user instance. The connection will be closed

Just a day ago, I was installing SQL Server Express on backup computer. I only use this computer when I have something to experiment. While installing SQL Server Express I found following error in my error log which prevented successful installation to computer. It was difficult to initially understand the problem by itself, after some try and error I found the solution.

Error: 15372, Severity: 16, State: 1.
Failed to generate a ser instance od SQL Server due to a failure in starting the process for the user instance.  The connection will be closed.

Solution for this error is fairly simple.

Fix/Workaround/Solution:

For XP:
Delete the directory “c:\Documents and Settings\username\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS “

For Vista:
Delete the directory “c:\Users\<username>\AppData\Local\Microsoft\Microsoft Sql Server Data\SQLEXPRESS”

Reboot the system.

Watch SQL in Sixty Seconds video to Resolve Connection Error:

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


SQL SERVER – Difference Between SQL Server Compact Edition (CE) and SQL Server Express Edition

I often received question regarding what are difference between SQL Server Compact Edition (CE) and SQL Server Express Edition. In one line – SQL Server CE is for mobile application and embaded systems where as SQL Server Express Edition is limited feature light version of SQL Server Standard.

SQL Server Compact Edition SQL Server Express Edition
ClickOnce Deployment ClickOnce Deployment
Installed centrally with an MSI Installed centrally with an MSI
XML storage XML storage
Transact-SQL Transact-SQL
Subscriber for merge replication Subscriber for merge replication
Simple transactions Simple transactions
Database size support – 4GB Database size support – 4GB
Number of concurrent connections – 256 Number of concurrent connections – Unlimited
Privately installed, embedded, with the application
Non-admin installation option
Runs on Windows Mobile platform
Runs in-process with application
Data file storage on a network share
Support for different file extensions
Code free, document safe, file format
Remote Data Access (RDA)
ADO.NET Sync Framework
Subscriber for merge replication
Simple transactions
Runs as a service
Procedural T-SQL
Distributed transactions
Native XML, XQuery/QPath
Stored procedures, views, triggers
Role-based security

I am interested to know if my readers have used any of these servers what was the real life application for the same.

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

Source : http://blog.sqlauthority.com/2009/04/22/sql-server-difference-between-sql-server-compact-edition-ce-and-sql-server-express-edition