Sharpen Your Basic SQL Server Skills - 28 May 2008

Q: What’s the difference between the Windows Authentication Mode and the Mixed Mode of security authentication?

A: The Windows Authentication Mode of security authentication allows a user to connect to SQL Server with a Windows user account. In this authentication mode, SQL Server receives the user’s login name and password and validates them with the Windows OS’s logon name and password. After the user’s login credentials are validated, SQL Server grants the user access. Windows Authentication uses the Kerberos security protocol and supports many standard group polices related to login name and password. This makes Windows Authentication Mode the most secure method to connect to SQL Server.

Mixed Mode is used when a user can connect to the SQL Server machine via Windows Authentication Mode or SQL Server Authentication. When you run a legacy application or use a non-trusted connection, SQL Server Authentication is automatically used instead of Windows Authentication. SQL Server receives the user’s login name and password and validates them with the previously created login name and password stored in SQL Server. After the user’s credentials are validated, SQL Server grants the user access. Mixed Mode authentication is convenient for multi-platform systems that contain non-trusted connections and legacy applications.

When you install SQL Server, Windows Authentication Mode is the default; SQL Server Authentication is disabled. For information about enabling SQL Server Authentication, see the Microsoft article “How to: Change Server Authentication Mode” (msdn2.microsoft.com/en-us/library/ms188670.aspx).

Q: How many system databases are installed with SQL Server 2005, and how are they used?

A: There are five system databases in a default installation of SQL Server 2005: master, resource, model, msdb, and tempdb. You can install additional system databases if you perform additional setup.

The master database contains all the system-level information (e.g., login accounts, server-wide database configurations, information about system and user databases, file groups), plus initialization information required to start SQL Server. A full backup must be performed regularly. Even a small change to this sensitive data can cause SQL Server to behave in an unexpected manner. Therefore, the master database has many modification restrictions (e.g., changing owner, modifying file groups, dropping databases, making a database read only). In SQL Server 2005 all the system objects have been moved to the new resource database.

The resource database contains all the system objects in SQL Server, such as user and system tables, views, triggers and functions, stored procedures, and constraints. In SQL Server all system objects logically belong to the system schema of each database and can be accessed with sys.objects, even though the system objects physically reside in the resource database. The resource database contains only system data; it doesn’t contain any user data. It isn’t possible to take a SQL Server backup of the resource database; instead, use a manual file system backup process. The resource database location depends on the location of the master database. If you move the master database, you must also move the resource database. If you move the resource database without moving the master database, SQL Server 2005 won’t start. The resource database is read-only, and you can’t see it in SQL Server Management Studio (SSMS). This database facilitates SQL Server quick version upgrades and easy service pack rollbacks.

The model database is a template for all new databases that you’ll create. So any changes to this database will show up in subsequently created databases. Back up this database regularly.

The msdb database contains scheduling alerts and scheduled jobs for all user databases. This database is used by log shipping jobs, service brokers, database mail, and other services. You need to back up this database.

The tempdb database contains every temporary object (e.g., temporary tables, temporary stored procedures, table variables) and many other internal system objects of SQL Server (e.g., worktables, deleted and inserted tables from triggers). You don’t need to back up tempdb because it’s recreated each time SQL Server is started.