Amd Sqlserver Final

The

Essential June 2006

to

Guide

SQL SERVER SKILLS FOR THE

ORACLE DBA By Michelle Poolet

Y

ou’ve been a database administrator (DBA) for a few years now. Your company has always been a dedicated Oracle shop. Recently, however, you’ve heard rumors of change. You know that the vendor walls are being breached when your boss calls you into his office and tells you that you’re

Special Advertising Supplement This special advertising section was produced by SQL Server Magazine in conjunction with AMD and appears as an insert in the June 2006 issue of SQL Server Magazine. sponsored by

going to need to learn how to manage a SQL Server database and no, there won’t be budget for training until next year. Now what? Where do you go to start learning about Microsoft SQL Server? The tasks that a DBA does when administering a database are similar, no matter what product you’re dealing with. The differences lie in how you give the commands, and in what level of detail you have to manage the database environment. This Essential Guide is designed to be a checklist of what Oracle DBAs need to know about SQL Server.

The Environment The concepts of instance and database are the same, whether you’re talking about an Oracle or a SQL Server environment. The instance is a set of memory-resident processes that manage the physi-

I want a server that runs

Microsoft SQL Server

without compromise.

The AMD Opteron™ processor with Direct Connect Architecture is engineered to perform with your company’s database computing needs in mind. It enables you to exploit the features of Microsoft SQL Server – giving you the performance and power efficiency you require to meet the most urgent business needs. And while deploying other 64-bit processing solutions might force you to retool, recode, and redeploy, AMD Opteron™ processors are on your side, reducing complexities in an all-too-complex IT environment. Power your business with AMD Opteron processor-based solutions today.

http://enterprise.amd.com

cal files that comprise the database environment. User data, data that we want to keep so that we can use it later, is persisted in a set of database files. We manipulate the data via a set of processes that are part of each database. Oracle Enterprise Manager (OEM) has its counterpart in the SQL Server 2000 Enterprise Manager (EM) and the SQL Server 2005 Management Studio (MS). In the Microsoft graphical environments you can perform most of the administrative operations that you will ever need to do. While SQL Server does provide a command-line interface for its text-based command language (isql and osql in SQL Server 2000, osql and sqlcmd in SQL Server 2005), the most common avenue for DBA operations is its graphic management tools, EM and Microsoft SQL Server Management Studio (SSMS). In the EM environment all database management operations can be done by point-and-click. In addition, SQL Server has the highly graphical SQL Server 2000 Query Analyzer—think SQL*Plus on steroids—which has gone through several incarnations and has finally been totally integrated into SQL Server 2005 Management Studio.

Startup There are several ways to start a SQL Server instance in SQL Server. First there’s the Service Control Manager, which is part of the SQL Server 2000 program group in the Windows Start menu. It also sits in the Windows tray on a SQL Server 2000 management console. Not only do you start SQL Server 2000 from the Service Control Manager, you can also start (and stop) the SQL Server Agent, the Distributed Transaction Coordinator, the Search Service and Analysis Services, which is the data warehousing environment. SQL Server 2005 doesn’t have a Service Control Manager; instead, you’ll find a Configuration Manager in the SQL Server program group off the Start menu. Another way to start, pause, continue, and stop the SQL Server services – local or remote – is from Enterprise Manager or Management Studio. You just need to ensure that the SQL Server instance is first registered with your copy of EM/SSMS.

The File System File systems in a database environment are generally pretty similar. In a typical Oracle database directory structure you’ll find data files with extensions like .ora (configuration parameter file),

.ctl (control file), .dbf (data file), and rdo (redo log file). In a SQL Server directory structure you’ll find data files with extensions like .mdf (primary data file), .ndf (secondary data file), and .ldf (transaction log file). SQL Server stores more than user data in database; control and initialization parameters are also stored in a database format. Oracle control files help maintain database consistency; they contain a mapping of the physical database files to the logical tablespaces and the online redo log files. SQL Server has system databases and system tables within each database that serve a similar purpose. Each SQL Server instance contains four system databases: master, model, msdb, and tempdb. The master database contains all of the system level information for a SQL Server, including login accounts and system configuration settings, similar to an Oracle system tablespace. It contains a list of all other databases, system and user, including the location of the various database files. Instead of a separate set of parameter files used to initialize memory at Oracle startup, the master database contains the initialization information for SQL Server. It’s the first database that’s read by the SQL Server executables on startup. You should never be without a recent (and good) backup of the master database. The Model database is used as a template for all databases created on a system. Every time a create database statement is issued, the content of the model database is copied over, and then the remainder of the database is filled with empty pages. MSDB is used by the SQL Server Agent to hold scheduling information for alerts and jobs, and for recording operators. Tempdb is the equivalent of an Oracle temporary tablespace – space used by SQL Server when it needs a scratchpad area for temporary tables and stored procedures, workspace, etc. Tempdb can automatically grow as needed, or you can limit it to a fixed size. However, it’s recreated each time the SQL Server is restarted, so never place any object in tempdb that you want to persist. In SQL Server tablespaces are called filegroups. Every SQL Server database has a primary, default filegroup called, “primary.” You manage filegroups much like tablespaces – create, drop and modify. Table operations are pretty much the same in SQL Server as they are in Oracle. The major difference is that SQL Server 2000 doesn’t

support the concept of a synonym, but SQL Server 2005 does. As in the Oracle world, when you create synonym, you can define the synonym for your own schema (like an Oracle private synonym) or, by creating the synonym within a schema that every user has rights to, you can create the equivalent of an Oracle public schema.

Backup and Recovery Models, Mirroring SQL Server’s transaction log provides the same func-

tionality as the redo log and the rollback segments in Oracle, ensuring that committed transactions are not lost or corrupted, and can be recovered if necessary. In the SQL Server environment you can use any one of three levels of logging for a database – simple, full or bulk-logged. These are the three “recovery models” for SQL Server. The simple recovery model is suitable for databases that need to be recovered to the point of the last good backup. You’ll be able to do both full and differential

The Next Revolution

in Database Solutions - AMD Opteron™ Processor and SQL Server 2005 Rapid technology advancements to the x86-based processors, including 64-bit and multi-core technologies, have resulted in 2P and 4P industry standard servers capable of meeting the demands of enterprise-class software. As older UNIX and x86-based database servers enter replacement life cycles, many businesses are evaluating x86 server platforms running AMD Opteron™ processors and MS SQL Server 2005 as a way to consolidate and standardize their IT infrastructures.

AMD Opteron™ Processor With the initial release of the AMD Opteron processor in April 2003, AMD introduced innovations to the x86-based processor that offered core functionality needed for database applications. It also created an innovative system architecture—Direct Connect Architecture—that is designed to effectively handle large amounts of data while providing the power efficiency to address the growing power, cooling, and space issues of the data center. Like 32-bit x86 processors, the AMD Opteron™ processor is a CISCbased processor where the CPU performs its own code optimizations and scheduling. Unlike the legacy front-side bus-based architecture, AMD’s Direct Connect Architecture has been designed to accommodate the increased processing power and I/O requirements needed for 64-bit computing. In addition, Direct Connect Architecture has been designed to support the multi-processor needs of

server and workstation applications. The Direct Connect Architecture supports Symmetrical Multiprocessing (SMP) system configurations and is capable of running in configurations with up to 8 CPU sockets. One of the most important features of AMD’s Direct Connect Architecture is the use of a new bus technology called HyperTransport™. With HyperTransport™ technology I/O and CPUs are directly connected for maximum throughput. Figure 1 provides an overview of AMD’s Direct Connect Architecture using the HyperTransport™ technology bus system design. Perhaps the most important feature of Direct Connect Architecture is the means by which the CPU’s access memory. The Direct Connect Architecture uses an integrated memory controller that allows CPUs to connect directly to the system memory, eliminating the need for an external memory controller. The integrated memory controller enables the AMD64 architecture to provide increased bandwidth, reducing memory latency and improving overall system performance. By providing each x64 CPU with a dedicated high-speed link to I/O via HyperTransport™ technology, and a separate high-speed connection to memory via an integrated memory controller, the AMD Direct Connect Architecture eliminates a significant bottleneck found in front-side bus-based architectures – memory and I/O contention for the shared frontside bus.

backups with the simple recovery model. You won’t be able to do transaction log backups, because SQL Server turns on the Truncate_Log_On_Checkpoint option to clear out any inactive entries from the transaction log each time a checkpoint is taken. This recovery model is ideal for most of the system databases. Backup files, which contain copies of a database and are used to recover the database in times of emergency, are as necessary in a SQL Server environ-

6.4 GB/S

8 GB/S

ment as they are in an Oracle environment. As with Oracle, you can do both offline and online database backups in SQL Server. The fastest, most efficient way to do an offline database backup is to detach the database in EM/SSMS. Then, using the Windows copy or xcopy utility, make a file-level copy of the detached database. When that’s finished, go back to EM/MS and reattach the database. When you detach a database and make a copy of it, all database objects, including security logins and

6.4 GB/S

8 GB/S

8 GB/S

PCI-E Bridge

PCI-E Bridge

• Increased memory allows for a higher maximum number of concurrent users • More memory enables various types of sort conditions to execute more efficiently.

SQL Server 2005 Standard Edition and Enterprise Edition pro8 GB/S vide native support for AMD Opteron procesUSB = HyperTransport sors. The relational dataI/O Hub links base engine and all the PCI subsystems—including SQL Server Integration Figure 1 HyperTransport™ Technology Bus Design Services, Analysis Services, and Reporting Serv64-bit SQL Server ices; and SQL Server Service Broker and Notification One of the server applications that can derive the Services—are available in native 64-bit code. In most benefit from the 64-bit platform is the rela- addition, all the SQL Server 2005 management tools tional database. The x64 platform enables SQL and utilities are also available as native 64-bit appliServer 2005 Standard and Enterprise editions to cations. achieve higher levels of scalability than was possiThe versatility and extended capabilities of the ble with the previous 32-bit editions of these prod- AMD Opteron Processor and SQL Server 2005, ucts. The increased memory capacity enables SQL address the needs of today’s IT organizations. The Server 2005 to handle larger databases and more massive improvement in headroom over the older complex application requirements. 32-bit technology, combined with the improved • Queries submitted to the database engine can processing power and system prices that are in-line derive substantial benefits from the increased with today’s 32-bit systems makes a very commemory available for in-memory buffer caching pelling package for any organization considering • Statement execution can benefit from extended upgrading Unix-based database servers. plan caching • Improved processor parallelism provides better For more information visit support for multi-threaded workloads http://enterprise.amd.com.

passwords, filegroups and transaction log files, are contained within that copy. Online backups traditionally come in three flavors—full, incremental, and differential (since the last full backup). The backup command lets you list specific files and filegroups, so backing up very large databases can be done in segments. In SQL Server 2005 you can do a partial backup by including only the read-write filegroups and by excluding the readonly filegroups. SQL Server 2005 has also introduced the concept of snapshot backup and restore. While this can be hugely beneficial for extremely busy or extremely large databases, there is a limitation— snapshot restores have to be done offline.

Security and Role-Playing SQL Server provides a set of pre-defined roles, both for the server and for each database. It also has a special server login called sa (system administrator) that is a counterpart to the Oracle pre-defined account SYS. Sa is a member of the sysadmin fixed server role, and can’t be changed. It’s a best practice to change the password for sa upon installation (in SQL Server 2000 it defaults to no password), to prevent unauthorized access to the server, and to refrain from using sa for everyday work. Instead, assign your system administrator logins to the sysadmin fixed server role, and have them log in under their own credentials. You can create userdefined roles in addition to the pre-defined server and database roles. Every user is automatically a member of the public role, and can be part of as many other roles as needed. There are multiple levels of security in SQL Server. Minimally you’ll need a login to gain access to the SQL Server, a userid to be able to use a database on the server, and permission to access the objects within a database. There’s a form of “passthrough” security called Windows Authentication, which lets you use the user and group accounts established in the Windows domain for authentication. For outside users, including non-domain Windows users, there’s mixed-mode security. These authentication modes are set up at the server level; for each server instance you’ll have to set up separate security authentication. Once inside the SQL Server you’ll need a userid in order to access a database. Essentially you’ll map the login to a user account (userid) in every database that you’re authorized to access. You assign privileges in SQL Server with the commands grant, revoke, and deny. You can do this either by executing scripts in

Query Analyzer/Query Editor or by the graphical point-and-click method in EM/MS. GRANT and REVOKE are ANSI-standard commands; DENY prevents an account from inheriting permissions through its membership in groups or roles.

The Data Dictionary Like Oracle, SQL Server stores its metadata (information about the database structure) separately from the user, or corporate, data. SQL Server metadata is stored in the master database; database metadata is stored in the system tables that are part of each database. For a downloadable .pdf of the SQL Server 2005 system views please visit http://www.windowsitpro .com/common/images/System_Table_mapping.pdf. Data Dictionary Views are provided by Oracle so that you can view and monitor objects in the database. These views reveal static data, data that won’t change until someone with adequate privileges (like a DBA) overtly adds or modifies a database object. The data dictionary views are like the V$ views that Oracle uses to monitor the database statistics and performance. For each Oracle data dictionary or V$ view there is not going to be a comparable SQL Server view – don’t look for a one-to-one match.

Tracing SQL Queries You can trace and analyze SQL queries using Oracle SQL Trace, TKPROF, SQL Analyze, SQL*Plus Autotrace, EXPLAIN PLAN, DBMS_XPLAN, and Oracle Trace. The counterpart in the SQL Server world is the SQL Profiler and the SQL Query Analyzer/Query Manager. SQL Profiler is a graphical tool which outputs a constant stream of results based on what you want to have profiled. The SQL Profiler can trace more than just SQL queries. Any event class and any of the class attributes can be traced. The tool for analyzing SQL queries is the SQL Query Analyzer/Query Manager. Both Query Analyzer and Query Manager have a sequence of SET commands that you can execute to turn on options like the query plan (in both text and graphical format), the input/output statistics, and the ■ elapsed time.

Michelle A. Poolet ([email protected]) is cofounder of Mount Vernon Data Systems and teaches database design and programming. She has a Masters degree in Computer Information Systems and is an MCP.