AL MA TE RI SQL Server 2005 Architecture GH TE D The days of SQL Server being a departmental database are long gone, and SQL Server can now easily scale to databases dozens of terabytes in size. In this chapter, we lay some of the groundwork that will be used throughout the book. We first discuss how the role of the DBA has changed since some of the earlier releases of SQL Server and then quickly jump into architecture and tools available to you as an administrator.
Chapter 1 Since SQL Server 2000, there has been a trend away from full-time Production DBAs, and the role has merged with that of the Development DBA. The trend may have slowed, though, with laws such as Sarbanes-Oxley, where you need a separation of power between the person developing the change and the person implementing the change.
SQL Server 2005 Architecture Business Intelligence DBA The Business Intelligence (BI) DBA is a new role that has grown due to the increased surface area of SQL Server. In SQL Server 2005, BI has grown to be an incredibly important feature set that many businesses cannot live without. The BI DBA is an expert at these features. He or she is the one who creates your SSIS packages to perform Extract Transform and Load (ETL) processes or reports for users.
Chapter 1 Industry Trends We’ll get into the SQL Server 2005 features momentarily, but you’ll notice a trend as you begin to see the list. Feature after feature will require that a DBA become acclimated to a .NET programming language such as C# or VB.NET to remain effective. For example, if you are a DBA trying to debug a performance problem with a CLR stored procedure, you’re going to need to know the language the stored procedure is written in to understand the performance problem.
SQL Server 2005 Architecture The transaction log’s most important purpose is to serve as an exact point in time in case you need to recover your database. Each data-modifying transaction is logged into the transaction log (although this behavior can be minimized if you turn on certain features).
Chapter 1 System Databases The system databases in SQL Server are crucial, and you should leave them alone most of the time. The only exception to that rule is the model database, which allows you to deploy a change like a stored procedure to any new database created. If a system database is tampered with or corrupted, you risk your SQL Server not starting. They contain all the stored procedures and tables needed for SQL Server to remain online.
SQL Server 2005 Architecture just holds the schema and stored procedures needed to run your instance. You should always back up the master database after creating a new database, adding a login, or changing the configuration of the server. You should never create objects in the master database. If you create objects here, it may cause you to have to make more frequent backups. Tempdb Database The tempdb database is like your database’s swap file.
2.qxp 8/8/07 9:21 AM Page 8 1 ma is called dbo.Employee. This table would be different from HumanResource.Employee, if you table in the database. It is a best practice always to refer to a database object by its two-part name, from the AdventureWorks database: ECT EmployeeID, Salary M HumanResource.Employee have been around since earlier releases of SQL Server but were not used in the same manner. ly, schemas were tied to your user name.
SQL Server 2005 Architecture SELECT login_name, COUNT(session_id) as NumberSessions FROM sys.dm_exec_sessions GROUP BY login_name In fact, DMVs are also sometimes functions and accept parameters. For example, the following code uses the sys.dm_io_virtual_file_stats dynamic management function (we use the term DMV for simplicity throughout this book) to retrieve the I/O statistics for the AdventureWorks data file. SELECT * FROM sys.
Chapter 1 SELECT o.name AS ObjectName, c.name AS ColumnName, TYPE_NAME(c.user_type_id) as DataType FROM sys.objects o JOIN sys.columns c ON o.object_id = c.object_id WHERE o.name = ‘Department’ and o.
SQL Server 2005 Architecture Data Type Stores Storage Space Bit 0 or 1 1 byte Tinyint Whole numbers from 0 to 255 1 bytes Smallint Whole numbers from –32,768 to 32,767 2 bytes Int Whole numbers from –2,147,483,648 to 2,147,483,647 4 bytes Bigint Whole numbers from –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 8 bytes Numeric 38 38 Numbers from –10 +1 through 10 – 1 Up to 17 bytes Decimal 38 38 Numbers from –10 +1 through 10 – 1 Up to 17 bytes Money –922,337,203,685,477
Chapter 1 inserted. To get only the date out of a datetime data type, you must essentially “fool” the data type by converting it: SELECT CONVERT(varchar, GetDate(), 101) CLR Integration In SQL Server 2005, you can also create your own data types and stored procedures using CLR (Common Language Runtime). This allows you to write more complex data types to meet your business needs in Visual Basic or C#, for example. (We cover the administration aspect of these much more in Chapter 8.
SQL Server 2005 Architecture Edition of SQL Server allows you to have high-availability options, Enterprise Edition far outdoes its sister edition with higher-end clustering as well as more advance mirroring and log-shipping options. The counter to this, of course, is the price. This edition of SQL Server will cost you more than $20,000 per processor if you choose that licensing model. (We discuss licensing later in this chapter.
Chapter 1 Database Features by Edition The main benefit from one edition of SQL Server to the next are the features enabled. In the following set of grids, you can see how the features line up to each other across the various editions. These grids do not capture all the features of SQL Server but instead focus on areas that we receive common questions about and areas that help distinguish the editions. Developer Features by Edition SQL Server 2005 really tries to appeal to the developer.
SQL Server 2005 Architecture Feature SQL Express Workgroup Report Subscriptions Standard Enterprise ✓ ✓ ✓ Data-Drive Subscriptions Report Builder ✓ ✓ ✓ Report Manager ✓ ✓ ✓ ✓ Infinite Drill-down Notification Services SQL Server Integration Services (SSIS) ✓ ✓ ✓ ✓ ✓ DBA Features by Edition The DBA features have the largest disparity among editions. You can see in the following table that most of the disparity revolves around high availability.
Chapter 1 Licensing Every DBA has probably received a dreaded licensing question or two, and we hope to answer some of those common questions in this section. There are several ways to license SQL Server, and we can’t address this ever-changing landscape completely in this book. Instead, we’ve tried to answer common questions that are not as likely to change from year to year.
SQL Server 2005 Architecture Summar y In this chapter, we covered the basic architecture for how SQL Server stores its data and how it communicates. We also addressed the various data types and when to use one over another data type. Last, we answered some of the many dreaded SQL Server edition and licensing questions that we often hear from users. Now that we have the groundwork down, we’re ready to jump into installing SQL Server and some of the common issues that you may experience.
