Best Practices for Microsoft SQL Server 2005

104 14
    • Microsoft SQL Server 2005 is a database application used to host and manage databases on servers running Microsoft Windows Server 2003 with service pack 3a, or later Microsoft Windows Server editions. The flexible nature of the software means a number of configuration options are possible to suit specific client data storage and retrieval requirements. Best practices include configuring authentication methods, enabling remote connections to the server and planning the location of database files.

    Authentication Mode

    • Microsoft SQL Server 2005 can be configured to use one of two authentication methods: Windows Authentication and Mixed Mode Authentication. If you configure SQL to use Mixed Mode Authentication, set up a user account and password in the SQL security database for each user who needs to access data on the SQL server. When the user opens the program to access a database on the SQL server, the program sends the user's user name and password across the network to the SQL server for authentication. The user name and password can be intercepted, posing a security risk, If you configure Windows Authentication Mode on the SQL server, users needing access to the SQL server can do so only when they have logged onto the Windows network. When a user wants to access the SQL server, encrypted, digital resource access tickets that do not contain the user name and password tell the SQL server that the user is valid. The best practice is to use Windows Authentication Mode, as this does not expose SQL user names and passwords on the network.

    Remote Connections

    • A single server can host multiple instances of SQL Server 2005. Each instance can host its own databases, and you can configure each instance of SQL independent from the other instances. By default, each instance of SQL 2005 is configured not to accept remote connections. This means you cannot connect to instances of SQL 2005 from a remote computer, across a network. You may want to connect from a remote computer to perform administration tasks without having to go to the actual server hosting SQL 2005, and to do this you must enable "Remote Connections" on each instance of SQL 2005 you want to connect to, and you must then start the SQL Browser Service. If a firewall sits between your computer and the server, configure the firewall to allow remote connections between your computer and the server. Because allowing the server to accept remote connections is considered a security risk, the best practice is not to enable remote connections until you are satisfied that doing so will not expose the server to unauthorized access attempts. Enable remote connections only after all other security measures have been configured and tested.

    Database Location

    • Once you have installed SQL Server 2005, you can use it to create, host and manage a single or multiple databases. A best practice is to install SQL 2005 on a server with multiple hard drives. Install the operating system and SQL 2005 on one hard drive, and when you create your databases install them on the other drives. If the operating system, SQL 2005 and your databases were all on the same hard drive, requests for data would be slow, as these requests will have to contend with the requests sent by the operating system and SQL 2005 itself. Placing databases on dedicated hard drives means that the hard drives need respond only to user requests. When you create a database, you can enable indexing of the database so if users need to search for data using specific words and phrases, the index is searched to return the required data quickly. It is a good practice to put indexes on separate hard drives so index searches do not slow up data retrieval.

Subscribe to our newsletter
Sign up here to get the latest news, updates and special offers delivered directly to your inbox.
You can unsubscribe at any time

Leave A Reply

Your email address will not be published.