Tips
Capturing and Alerting on SQL Server Configuration Changes
Once a SQL Server is initially configured, we would love nothing but to set it, and forget it. However, in the real world, especially with multiple SQL Server environments to manage, we need to keep track of any changes to the configuration, which could impact the environment. As a DBA, we need to be aware of any ad-hoc changes and ideally be alerted to address the issue.
In SQL Server 2008, Microsoft introduced the Policy Management Framework or Policy-Based Management. This powerful new framework allows DBAs to define a set of policies that can control several aspects of SQL Server. For example, the DBA could define a policy that specifies how a particular configuration option should be set on all the servers in the enterprise.
Configure MaxTokenSize for SQL Server Authentication
DBA's and web developers at our company are experiencing issues with connecting to SQL instances using SQL Server Management Studio and other SQL tools using Windows Integrated Authentication. Our company is large, with well over 70,000 users and groups in Active Directory. When we look in the NT event log on the SQL Server we see both MSSQL and Kerberos errors. What could be causing this?
Configure Windows Firewall for SQL Server 2008 Database Engine in Windows Server 2008 R2
I have installed SQL Server 2008 Developer Edition on Windows Server 2008 R2 and I am unable to get connect to SQL Server 2008 Instance from SQL Server 2008 Management Studio which is installed on another remote server. As I am new to Windows Server 2008 R2 it would be great if you can let me know the step by step approach to enable the default port of SQL Server 2008 in Windows Firewall for user connectivity.
Configure Windows Firewall for SSAS in Windows Server 2008 R2
I have installed SQL Server 2008 Developer Edition on Windows Server 2008 R2 and I am unable to get connect to SQL Server 2008 Analysis Services Instance from SQL Server 2008 Management Studio which is installed on another remote server. As I am new to Windows Server 2008 R2 it would be great if you can let me know the step by step approach to enable the default port of SQL Server 2008 Analysis Services in Windows Firewall for user connectivity.
Configure Windows Firewall for SSIS in Windows Server 2008 R2
I have installed SQL Server 2008 on Windows Server 2008 R2 and I am unable to connect to SQL Server 2008 Integration Services Instance from SQL Server 2008 Management Studio which is installed on another remote server. As I am new to Windows Server 2008 R2 it would be great if you can let me know the step by step approach to enable the default port of SQL Server 2008 Integration Services in Windows Firewall for user connectivity.
Configuring SQL Server to Use Multiple Ports
Recently I came across a situation where an existing production SQL Server default instance was configured to use static TCP/IP port 48030 and the default 1433 port was disabled. In this tip we walk through how to configure a SQL instance to listen on multiple TCP/IP ports.
Create a 32-bit DSN on 64-bit machine for SQL Server
During the install of an application on a 64-bit Windows machine, the application was unable to list the ODBC System DSN because it had been setup as a 64-bit DSN and the application needed a 32-bit system DSN for connectivity to SQL Server. In this tip, we cover how to create a 32-bit DSN on 64-bit machine.
Determine which version of SQL Server data access driver is used by an application
SQL Server Native Client was introduced with SQL Server 2005 and a newer version was introduced with SQL Server 2008. In this tip we look at how you can determine which version is being used.
Find Last Restart of SQL Server
How can I tell when my SQL Server instance was last started? In this tip we look at various methods of how to get the start time for a SQL Server instance.
How and why would I use multiple instances of SQL Server
SQL Server 2000 and 2005 have the capability to use multiple instances of the database engine on a single server. But is going down this path really worth it? In some situations the answer should be a resounding 'yes'. So, how are they setup? When would I want to use them?
How to setup and use a SQL Server alias
I have an application that has a specified database connection that I either can't or don't want to change. However, I need to move the database to a new server. How can I do this easily without breaking other things that maybe using this connection and/or database?
How to setup security features for SQL Server
I have been put in charge of securing our SQL Server 2005 servers. Are there any tools available that can help me get started? Are any tools available to secure SQL Server independent of the logins and users that I have in place? Do I need to manage SQL Server on each machine or can I do so from a single application?
How to tell what SQL Server version you are running
One issue that I am often faced with is determining what service pack is installed on the SQL Server. Until recently the version of SQL Server that was installed was just a number, but now with the later service releases for SQL Server 2000 you can also see what service pack is installed. For SQL Server 2005 it still only shows you the version number that is installed. So how do you know what service pack is installed?
Identify location of the SQL Server Error Log file
How do I find out where the SQL Server Error Log file is located for a specific SQL Server instance? In this tip we look at different ways a DBA can identify the location of the SQL Server Error Log file used by an instance of SQL Server.
Identify SQL Server TCP IP port being used
How do I find out what TCP/IP port SQL Server is using for a specific SQL Server instance? In this tip we look at different ways a database administrator can identify the port used by instance of SQL Server.
Installing SQL Server 2012 on Windows Server Core Part 1
Windows Server Core has been around since Windows Server 2008. I have been wanting to install SQL Server 2008 on Windows Server Core but could not do so because it wasn't officially supported. However, SQL Server 2012 now is. In this tip we cover how to install SQL Server 2012 on a Windows Core Server.
Remotely Accessing the SQL Server Desktop
How can I get direct access to my SQL Server's desktop, not a terminal services session? Do I need to purchase a third party product or is a tool available with Windows or SQL Server? I know I should not need access to my SQL Server like this on a regular basis because I can manage my enterprise of SQL Servers via Management Studio or Enterprise Manager. However, I want to find out if an option is available to keep in my back pocket in case a pop-up is on the desktop or I need to check to see if something unexpected has occurred on the SQL Server.
Resolving could not open a connection to SQL Server errors
Sometimes you may have issues connecting to SQL Server and you may get messages such as "Could not open a connection to SQL Server". In this tip we look at different things you can check to resolve this issue.
SQL Server 2000 vs SQL Server 2005 Startup Parameters
SQL Server offers the ability to use startup parameters when your SQL Server instance starts. With SQL Server 2000 the startup parameters are available right from Enterprise Manager. With SQL Server 2005 the startup parameters are available from SQL Server Configuration Manager.
SQL Server Best Practices Analyzer (Tip 1 of 2)
We have a number of SQL Server 2000 instances in our environment. I am not sure if they have been consistently deployed or not. Further, I am not sure if the SQL Server 2000 instances are following best practices or not. How can I get some feedback on my SQL Server 2000 environment to shed some light on the situation?
SQL Server Best Practices Analyzer (Tip 2 of 2)
With SQL Server 2000, I had a good feel for many of the best practices, from an administration, development and configuration perspective. When deploying SQL Server 2005 some of those same principals will apply. But I'm sure with the vast amount of change in SQL Server 2005 that there are some new rules as well. How can I validate my SQL Server 2005 environment is properly configured? Are there really absolute rules when configuring a SQL Server 2005 instance?
SQL Server DSN and ODBC Configuration
In part one of our four-part series on connecting Microsoft Access to a SQL Server database, we outlined the steps for creating a ODBC Data Source Name (DSN) using the SQL Server OLE DB driver. With the advent of SQL Server 2005 we had a new driver at our disposal for the purpose of connecting to SQL Server 2005: the SQL Native Client or SNAC for short. If you want to take advantage of some of the new features associated with SQL 2005 you'll need to use SNAC. How is using this driver any different? Are there any new capabilities with this new driver?
SQL Server Instance Objects
SQL Server server related information is valuable for administrative scripts and very important when troubleshooting general configuration issues. In the transition from SQL Server 2000 to 2005, some of the objects and commands that we have grown to rely on are no longer the recommended information source. In this tip we will outline core sets of data that need to be retrieved for SQL Server and map the objects from SQL Server 2000 to 2005.
SQL Server Name Resolution Troubleshooting
A colleague recently said, "With all the legacy systems, modern systems, and 'spokes in the wheel' in this environment, we should celebrate whenever we actually get data to the client." No doubt there are many pieces to the puzzle that comprises data delivery to internal and external clients, but knowing about every facet of an N-tier architecture application can be challenging. So what are the truly important things a DBA should know about to ensure data gets delivered? Sure, there are the usual technologies like replication and database mirroring, but one of the most important systems that make enterprise applications work is server name resolution.
SQL Server System Configuration Values
Often times we need to review the SQL Server instance configuration values while troubleshooting performance problems. What alternatives are available to capture this information? With all of the new views in SQL Server 2005, is one available with the configuration values, a description and the list of possible values?
Static Port Assignments in SQL Server 2005
We have noticed some connection issues on a variety of our web based applications when the corresponding SQL Server is rebooted. We have noticed the issue with SQL Server 2005 Express edition as well as the SQL Server 2005 Standard edition. It seems like after the reboot we have to change our application connection strings and firewall settings in some circumstances in order to correct the situation. We know in some of the circumstances the applications are in a transient state, but others have been deployed and unchanged for a while. When we have to reboot servers, the whole team is on guard to test the applications and ready to troubleshoot the issues. We having found that in some circumstances...
Steps to change the server name for a SQL Server machine
In this tip we look at the steps within SQL Server you need to follow if you change the physical server name for a standalone SQL Server.
Suspect SQL Server 2000 Database
I have a SQL Server 2000 database that has the wrong database status. For some reason it is in the 'Suspect\Offline' mode. I just need to correct the problem quickly and get my database back online. How can I do so? Once the status is correct, do I need to take any further steps?
Troubleshooting SQL Distributed Transactions (Part 2 of 2)
Distributed transactions are not working in our environment. When we run a T-SQL query using "begin distributed transaction" it fails with the following error: “MSDTC on the server is unavailable” however it looks like the MSDTC service is online and working fine. In this tip (part 2), I will show the steps that were taken with the Microsoft SQL Server Premier Support Team to solve the problem.
Troubleshooting SQL Server Distributed Transactions (Part 1 of 2)
Distributed transactions are not working in our environment. When we run a T-SQL query using "begin distributed transaction" it fails with the following error: “MSDTC on the server is unavailable” however it looks like the MSDTC service is online and working fine. In this tip, I cover a few ways to test whether MSDTC is working properly.
Understanding SQL Server Net Libraries
I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? In this tip we cover each of these Net Libraries.
Understanding the SQL Server SELECT @@VERSION command
I was recently trying to determine what version of SQL Server was installed on a particular server. I found this tip to determine the version and tried to execute the code in the command. However, I do not understand what all the information means. Could someone explain it? Check out this tip to learn about the SELECT @@VERSION command in SQL Server.
Using Friendly Names for SQL Servers via DNS
At my organization, servers have a very cryptic name or a name where you can't immediately tell the function. For instance, Amazon is the name of the SQL Server which our SharePoint uses as a back-end and Nile is the SQL Server hosting the databases for our HR application. This isn't intuitive and it makes upgrading the servers a challenge because we always have to go back and touch the applications where they specify database connections. Is this tip we show an easy way to handle this.
What is a hobt_id in Sql 2005?
Why is SQL Server Configuration Manager Missing Services
When I open SQL Server Configuration Manager (SSCM) I see no services displayed (or just Integration Services). How can I resolve this quickly? Check out this tip for the solution.
Why listing all of your SQL Servers does not always work
When I do a SQLCMD -L or OSQL -L or I hit the drop down on the server list to connect using the GUI tools (like SQL Server Management Studio), I only see some of the SQL Servers in my environment. I don't see them all. Why is that?
Windows Integrated Authentication Failures to SQL Server
In a previous tip you discussed overriding the MaxTokenSize to accommodate larger Kerberos tokens due to membership of a large number of groups in Active Directory. Right around the same we noticed that the MaxTokenSize needed to be adjusted, issues with web applications that use Windows Integrated Authentication also started failing, one of which was SQL Server Reporting Services. The company I work for made the necessary changes and rebooted the SQL and Web servers. After the reboot we still had issues with our web applications, although the issue with SQL Server Management Studio was resolved. What additional changes need to be made?
Windows Services for SQL Server
I am a BI Developer and have been recently moved to Microsoft BI, I need to get up to speed. As part of my exercise to ramp up on this new platform, I started going through each of the SQL Server components to try to get familiar with them. While I was going through the Windows Services, I have seen a lot of new services that are added after installing SQL Server. I want to get an understanding of the various services related to SQL Server and its components, and their dependencies.
Top 10
How to tell what SQL Server version you are running
One issue that I am often faced with is determining what service pack is installed on the SQL Server. Until recently the version of SQL Server that was installed was just a number, but now with the later service releases for SQL Server 2000 you can also see what service pack is installed. For SQL Server 2005 it still only shows you the version number that is installed. So how do you know what service pack is installed?
Using Friendly Names for SQL Servers via DNS
At my organization, servers have a very cryptic name or a name where you can't immediately tell the function. For instance, Amazon is the name of the SQL Server which our SharePoint uses as a back-end and Nile is the SQL Server hosting the databases for our HR application. This isn't intuitive and it makes upgrading the servers a challenge because we always have to go back and touch the applications where they specify database connections. Is this tip we show an easy way to handle this.
How to setup and use a SQL Server alias
I have an application that has a specified database connection that I either can't or don't want to change. However, I need to move the database to a new server. How can I do this easily without breaking other things that maybe using this connection and/or database?
Understanding the SQL Server SELECT @@VERSION command
I was recently trying to determine what version of SQL Server was installed on a particular server. I found this tip to determine the version and tried to execute the code in the command. However, I do not understand what all the information means. Could someone explain it? Check out this tip to learn about the SELECT @@VERSION command in SQL Server.
Windows Services for SQL Server
I am a BI Developer and have been recently moved to Microsoft BI, I need to get up to speed. As part of my exercise to ramp up on this new platform, I started going through each of the SQL Server components to try to get familiar with them. While I was going through the Windows Services, I have seen a lot of new services that are added after installing SQL Server. I want to get an understanding of the various services related to SQL Server and its components, and their dependencies.
Identify SQL Server TCP IP port being used
How do I find out what TCP/IP port SQL Server is using for a specific SQL Server instance? In this tip we look at different ways a database administrator can identify the port used by instance of SQL Server.
Installing SQL Server 2012 on Windows Server Core Part 1
Windows Server Core has been around since Windows Server 2008. I have been wanting to install SQL Server 2008 on Windows Server Core but could not do so because it wasn't officially supported. However, SQL Server 2012 now is. In this tip we cover how to install SQL Server 2012 on a Windows Core Server.
Suspect SQL Server 2000 Database
I have a SQL Server 2000 database that has the wrong database status. For some reason it is in the 'Suspect\Offline' mode. I just need to correct the problem quickly and get my database back online. How can I do so? Once the status is correct, do I need to take any further steps?
Resolving could not open a connection to SQL Server errors
Sometimes you may have issues connecting to SQL Server and you may get messages such as "Could not open a connection to SQL Server". In this tip we look at different things you can check to resolve this issue.
Identify location of the SQL Server Error Log file
How do I find out where the SQL Server Error Log file is located for a specific SQL Server instance? In this tip we look at different ways a DBA can identify the location of the SQL Server Error Log file used by an instance of SQL Server.
Last 10
Using Friendly Names for SQL Servers via DNS
At my organization, servers have a very cryptic name or a name where you can't immediately tell the function. For instance, Amazon is the name of the SQL Server which our SharePoint uses as a back-end and Nile is the SQL Server hosting the databases for our HR application. This isn't intuitive and it makes upgrading the servers a challenge because we always have to go back and touch the applications where they specify database connections. Is this tip we show an easy way to handle this.
Installing SQL Server 2012 on Windows Server Core Part 1
Windows Server Core has been around since Windows Server 2008. I have been wanting to install SQL Server 2008 on Windows Server Core but could not do so because it wasn't officially supported. However, SQL Server 2012 now is. In this tip we cover how to install SQL Server 2012 on a Windows Core Server.
Windows Services for SQL Server
I am a BI Developer and have been recently moved to Microsoft BI, I need to get up to speed. As part of my exercise to ramp up on this new platform, I started going through each of the SQL Server components to try to get familiar with them. While I was going through the Windows Services, I have seen a lot of new services that are added after installing SQL Server. I want to get an understanding of the various services related to SQL Server and its components, and their dependencies.
Understanding the SQL Server SELECT @@VERSION command
I was recently trying to determine what version of SQL Server was installed on a particular server. I found this tip to determine the version and tried to execute the code in the command. However, I do not understand what all the information means. Could someone explain it? Check out this tip to learn about the SELECT @@VERSION command in SQL Server.
Steps to change the server name for a SQL Server machine
In this tip we look at the steps within SQL Server you need to follow if you change the physical server name for a standalone SQL Server.
Identify location of the SQL Server Error Log file
How do I find out where the SQL Server Error Log file is located for a specific SQL Server instance? In this tip we look at different ways a DBA can identify the location of the SQL Server Error Log file used by an instance of SQL Server.
Create a 32-bit DSN on 64-bit machine for SQL Server
During the install of an application on a 64-bit Windows machine, the application was unable to list the ODBC System DSN because it had been setup as a 64-bit DSN and the application needed a 32-bit system DSN for connectivity to SQL Server. In this tip, we cover how to create a 32-bit DSN on 64-bit machine.
Identify SQL Server TCP IP port being used
How do I find out what TCP/IP port SQL Server is using for a specific SQL Server instance? In this tip we look at different ways a database administrator can identify the port used by instance of SQL Server.
Find Last Restart of SQL Server
How can I tell when my SQL Server instance was last started? In this tip we look at various methods of how to get the start time for a SQL Server instance.
Configuring SQL Server to Use Multiple Ports
Recently I came across a situation where an existing production SQL Server default instance was configured to use static TCP/IP port 48030 and the default 1433 port was disabled. In this tip we walk through how to configure a SQL instance to listen on multiple TCP/IP ports.
