About the SQL Server Browser Service
As you're getting started with SQL Server 2005 Express Edition and you spend some time poking around at the various options that you'll find in the SQL Server Configuration Manager or the SQL Server 2005 Surface Area Configuration programs, one item that you might find a bit confusing and mysterious is the SQL Server Browser service. As its name implies, the SQL Server Browser is a Windows service, and by default it’s turned off. The primary purpose of the SQL Server Browser is to enable networked systems to locate named instances of the SQL Server database engine. This functionality can be important because SQL Server Express, like the full-blown editions of SQL Server 2005, supports multiple named database instances running on a single computer.
Each SQL Server instance listens on its own port number to respond to its individual incoming client requests. When the SQL Server system starts, each named database instance is dynamically assigned a port number. Because the assignment is dynamic, these port numbers can change if the system is rebooted. The job of the SQL Server Browser is to query the registry and locate all of the active SQL Server named instances and report them to client applications. By default, the SQL Server Browser uses port UDP 1434 to tell networked clients about the active SQL Server database instances.
So, is the SQL Server Browser really required to use SQL Server Express? Strictly speaking, the answer is no. Single-user desktop applications have no need to advertise the presence of the database to networked systems, so they don’t need the SQL Server Browser. In addition, if there’s only one instance of the database running, networked applications can connect to it by using the standard SQL Server TCP port 1433. In other words, if you’re using the default SQL Server instance, the SQL Server Browser isn’t necessary. The SQL Server Browser is useful only when there are multiple named instances of the SQL Server database. Then, the networked clients can specify the instance name as part of the connection string, and the SQL Server Browser will take care of resolving that instance name to the matching TCP port. Technically speaking, the client application can actually connect to the different named instances by specifying the port number of the named instance on the application’s connection string. However, that’s not a desirable connection method because the port number could change the next time the service is restarted.
If you want to enable the SQL Server Browser service, go to the Start menu, select All Programs, Microsoft SQL Server 2005, Configuration Tools, SQL Server Surface Area Configuration. Click the “Surface Area Configuration for Services and Connections” link, then select SQL Server Browser from the “Select a component and then configure its services and connections list.” Click Start to start the service, and use the Startup type drop-down box to change the startup type to Automatic. For more information about SQL Server Express’s Browser, refer to the SQL Server Books Online (BOL) topics “Using the SQL Server Browser” at http://msdn2.microsoft.com/en-us/library/ms165724.aspx and “SQL Server Browser Service” at http://msdn2.microsoft.com/en-us/library/ms181087.aspx.
Solutions for Windows SQL Server and Exchange Servers
When disaster strikes your servers you need answers whether they are dedicated to Windows SQL Server or Exchange. Make sure that if an emergency occurs you're prepared. Get the full eBook and get started on your recovery plan today.
HA Solutions for Windows, SQL Server, and Exchange Servers
When disaster strikes your servers, you need answers, whether they are dedicated to Windows, SQL Server, or Exchange. Make sure that if an emergency occurs, you’re prepared. Get the full eBook and get started on your recovery plan today!
Check It Out
Although SQL Server 2005 Express is primarily intended to be used as a standalone database for desktop or small scale server applications, that's not its only role. SQL Server 2005 Express can also function as a part of a bigger enterprise environment in which database changes are replicated to other SQL Server systems in the organization Centralizing database activity from multiple branch office locations is one useful scenario for SQL Server 2005 Express's replication support. You can check out more about using SQL Server Express to replicate data in SQL Server 2005 Books Online BOL under "Replication in SQL Server Express" or by going to the Microsoft website.
Enabling Network Access
By default the network access for SQL Server 2005 Express is turned off. This out of the box security measure is designed to help ensure secure implementation for desktop oriented applications. However, this default can be confusing because it can make the user think that SQL Server 2005 Express doesn't support network access However that's certainly not the case The easiest way to enable SQL Sever Express's network access is by using the SQL Server Surface Area Configuration program. You start the SQL Server Configuration Manager by using the Start menu and selecting All Programs SQL Server 2005 Configuration Tools SQL Server Surface Area Configuration.
After you start the SQL Server Surface Area Configuration program select the Surface Area Configuration for Services and Connections option. Then in the Select a component and then configure its services and connections list, click Remote Connections and select Local and remote connections. Typically, you would also select the default TCP IP protocol by leaving the Using TCP IP only option enabled. This option allows TCP IP based connections to SQL Server Express. Remember, if you're using SQL Server Express's default port setting to access the system across the network you'll also need to keep TCP port 1433 open on your firewall.
The Essential Guide to Jump Starting Your SQL Server Skills
Now can you use it Get up to speed on database design and hierarchy including columns and data types creating databases and using the Query Editor. Download your free copy today.
SQL Server Magazine Connections
March 25-28, 2007 Orlando World Center Marriott
Dive into the latest releases and train with Microsoft architects and world renowned developers and DBAs Choose from over 150 in depth no hype sessions. Register early to save $100 and attend sessions of the concurrently run Visual Studio Connections and Microsoft ASP NET Connections for FREE. For more details, call 800-438-6720 or 203-268-3204.
How do you manage security vulnerabilities? If you depend on vulnerability assessments to determine the state of your IT security systems you can't miss this Web seminar. Special research from Gartner indicates that deeper penetration testing is needed to augment your existing vulnerability management processes.
Do you know the clues and secrets to effective disaster recovery? Lucky mates will win a Weekly Prize of a 25 Best Buy Gift Card or a Grand Prize of a $100 Best Buy Gift Card. Find the buried treasure by uncovering the secrets to Web filtering. Complete this quiz correctly and you could be a winner.
Get a solid introduction to Microsoft System Center Data Protection Manager DPM Download the full eBook today to learn how to use DPM to augment tape based backups.
Is effective security out of reach for your small or midsized business? Imagine having a team of IT experts who focus only on security as part of your staff . Download this free must have white paper today and find out how you can eliminate your company s security risks.
WANTED: Your reviews of products you've tested and used in production. Send your experiences and ratings of products to [email protected] and get a Best Buy gift certificate.
by Blake Eno
SQL Server Administration and Development Tool
EMS Database Management Solutions announced updates to its SQL Server administration and development tool EMS SQL Manager for SQL Server 2.6. The software supports all SQL Server versions including SQL Server 2005 Express Edition and lets users automate the development process design and maintain existing databases build SQL query statements and manage user rights. The newest version features a number of fixes from previous versions and includes updates to the editor visual and environment options. For a complete list of features contact EMS Database Management Solutions at 646-362-2260 | 866-775-4968 or visit them on the Web.
Introducing a Unique Scripting Resource
Scripting Pro VIP is an online information center that delivers new articles and downloadable code every week on topics such as ADSI and PowerShell. Subscribers also receive tips cautionary advice direct access to our editors and a host of other benefits. Order now at an exclusive charter rate and save up to 50 percent.