93. SQL Server

SQL Server is a Microsoft product used to manage and store information. Technically, SQL Server is a “relational database management system” (RDMS). Broken apart, this term means two things. First, that data stored inside SQL Server will be housed in a “relational database”, and second, that SQL Server is an entire “management system”, not just a database. SQL itself stands for Structured Query Language. This is the language used to manage and administer the database server. One of the key improvements in Microsoft SQL Server 2005 is the new management suite, SQL Server Management Studio (SMSS), which has many enhancements over the previous SQL Server Enterprise Manager. If you are a hoster who wants to enable the highest functionality of database administration for your customers, you can use SSMS to manage their databases.

SQL Server Management Studio Express is a slightly less functional tool that is a free download for customers. It provides much of the same functionality for database administration as SSMS without the server-specific capabilities. In most cases, end customers don't have permissions to use the server-side functionality of SSMS, because they normally only have rights to administer their database. Windows-based Hosting encourages hosters to operate in the most secure way possible; however, business and customer requirements often dictate flexibility in what you offer customers. If you decide to offer direct access, consider recommending that your customers download the SQL Server Management Studio Express tool. It is easily available and free. The full SSMS toolset is only provided with SQL Server, so getting customer access to the tool without having SQL Server 2005 is complicated.

The Microsoft Solution for Windows-based Hosting recommends against leaving SQL Server management ports (UDP 1434 and TCP 1433) exposed to the Internet. Therefore, we recommend Terminal Service access to a host where SSMS is installed. This is usually not an issue for dedicated servers, as the customer administrators may have access. Terminal Services can be used if a separate SQL Server administrative machine with SSMS installed is created. Multiple customers can use Terminal Services to access this server. This option is more costly as it requires additional hardware and Terminal Services client licenses.

Although not recommended, some service providers will decide not to close the ports. If your customers or service offerings require open ports, consider one of the following options: Port Authorization — Require specific authorization to the port prior to allowing SSMS to connect. This may mean a separate logon is required prior to opening SSMS for the customer. Fixed IP Port Authorization — Lock the SQL Server ports down to a specific list of Customer IP addresses. Many customers may not have fixed IP addresses, so this really is applicable to those customers that have fixed IP addresses. Port Reassignment — Change the SQL Server ports to some other unused port for a specific customer. This is an intermediate step, and requires more configuration on your part for your firewall, as well as customer configuration of their SSMS tool.

SQL Server also allows stored procedures to be defined. Stored procedures are parameterized T-SQL queries that are stored in the server itself (and not issued by the client application as is the case with general queries). Stored procedures can accept values sent by the client as input parameters, and send back results as output parameters. They can also call other stored procedures, and can be selectively provided access to. Unlike other queries, stored procedures have an associated name, which is used at runtime to resolve into the actual queries. Also because the code need not be sent from the client every time (as it can be accessed by name), it reduces network traffic and somewhat improves performance. Execution plans for stored procedures are also cached as necessary.