By Patrick LeBlanc, Publisher: Microsoft Press, Released: January 2013

http://shop.oreilly.com/product/0790145338273.do?sortby=publicationDate

CHAPTER 1, Overview of Microsoft SQL Server 2012

after completing this chapter, you will be able to

  • Explain SQL Server components and features and their uses.
  • Identify SQL Server features vital to your environment.
  • Define and scope your SQL Server installation topology from a high level.

The process of learning a new technology can be daunting and sometimes involves a tremendous amount of time and effort. Each step of the process, from installing and configuring the software to deploying the first project, introduces new challenges. These challenges often grow when the technology includes several components and features, so the first step, especially with a multicomponent technology, is to identify the components your environment requires and gain a good understanding of the functionality of each component. To that end, in this chapter, you will examine the components and features of Microsoft SQL Server 2012 and determine how they fit into your installation.

Like most relational database management systems (RDBMS), SQL Server 2012 includes several components. The product itself, however, is often divided into two distinct categories: business intelligence (BI) and the Database Engine.

Business intelligence

Business intelligence (BI) refers to data transformed into knowledge that can then be used to make more informed business decisions. For example, a company whose primary purpose is to sell bikes could use its data to identify sales trends and the purchasing patterns of its customers. From that analysis, the company could decide to focus sales efforts on a particular area or region, which in turn could lead to better opportunities and offer the company competitive advantages in its industry.

While the BI features of SQL Server 2012 can add highly visible and effective value to business users and data consumers, in this book you'll focus primarily on the features specific to the Database

Engine. 4 part 1 Getting Started with Microsoft SQL Server 2012

Database Engine

The Database Engine sits at the core of the SQL Server components. The engine operates as a service on a machine, which is often referred to as an instance of SQL Server. You can run multiple instances of SQL Server on a given server. When you connect to SQL Server, the instance is the target of the connection. Once an application is connected, it sends Transact-SQL (T-SQL) statements to the instance. The instance in return sends data back to the client. Within the connection is a security layer that validates access to the data as specified by the database administrators (DBAs). The Database Engine enables you to leverage the full capabilities of all of the other components, such as accessing, storing, and securing the data.

The storage component of the Database Engine determines how the data is stored on disk. When designing your databases, you will specify various aspects that will dictate how your tables, indexes, and, in some cases, views are physically organized on your disk subsystem. You will examine the concepts of tables, indexes, and views in detail in later chapters. In SQL Server 2012, you can physically distribute data across disks by partitioning it, or dividing the data into distinct, independent parts.

Partitioning not only improves query performance, but it also simplifies the process of managing and maintaining your data. With the release of SQL Server 2012, Microsoft increased the number of supported partitions to 15,000 per table.

Within the Database Engine itself, the storage engine is the primary component. Surrounding it are several additional components that depend on the engine. These components include the following:

  • T-SQL programming interface (Microsoft's implementations of the SQL ANSI standard
  • language)
  • Security subsystem
  • Replication
  • SQL Server Agent
  • High availability and disaster recovery tools
  • SQL Server Integration Services
  • SQL Server Management tools

The following sections provide a brief explanation of each component.

T-SQL programming interface

What is the value in storing data if you cannot access it? SQL Server provides a rich programming language that allows you to write simple and complex queries against the underlying storage structures. Using T-SQL, you can write data manipulation queries that enable you to modify and access the data on demand. You can create objects such as views, stored procedures, triggers, and user-defined functions that act as a means of surfacing that data. Applications written in programming languages such Chapter 1 Overview of Microsoft SQL Server 2012 5 as Visual Basic and C# .NET can send T-SQL queries from applications to the Database Engine. The Database Engine will then resolve the queries and send the results back to the client.

In addition, you can write data definition queries to create and modify objects that act as mechanisms for surfacing the data. T-SQL also allows you to manage server configurations and security

seamlessly. T-SQL is a set-based language, meaning that it performs optimally when interacting with data in sets as opposed to manipulating strings or iterating over rows of data. While T-SQL is capable of these cursor-based operations, these types of operations are less efficient than a properly designed set-based approach. If you find you are using T-SQL to perform cursor-based operations, consider leveraging a common language runtime (CLR) language. Using your favorite compiler (Visual Studio, for example), you can extend the functionality of T-SQL.

SQL Server 2012 introduces several new T-SQL programming enhancements, including a simpler form of paging, windowing functions, and error handling. A THROW statement is introduced that provides a way to elegantly handle errors by raising exceptions. You can now create a FileTable that builds on the FileStream technology introduced in SQL Server 2008. Coupling the FileTable with FullTextSearch allows you to run complicated queries against massive amounts of text data (such as the complete text of this book). SQL Server 2012 also introduces several new conversion, string, logical, data, and time functions.

Security subsystem

In most organizations, data is the most valuable asset, and keeping that data secure is a major concern. Any vulnerability in an organization's security might end up triggering a series of events that could prove catastrophic to the business. This is why SQL Server 2012 consists of a robust security subsystem that allows you to control access via two modes of authentication, SQL and Windows. As an administrator, you are able to configure SQL Server security at multiple levels. Using T-SQL or SQL Server Management Studio, you can control access to a particular instance of SQL Server, to specific databases, to objects within those databases, and even to columns within a particular table.

SQL Server also includes native encryption. For example, if you want to secure employees' Social Security numbers, using column level encryption, you could encrypt a single column in a table.

SQL Server also includes Transparent Data Encryption (TDE), which allows you to encrypt an entire database without affecting how clients and applications access the data. However, if someone were to breach your network security and obtain a copy of a data file or backup file, the only way that person could access the data is with an encryption key that you set and store.

Even with all of these security capabilities, SQL Server provides you with the ability to audit your server and databases proactively. In SQL Server 2012, you can filter audit events before they are written to the audit log. Chapter 26, “Security,” describes how to plan and deploy your SQL Server security strategy. You will learn specific concepts around creating logins and users, and you will examine how to create a security approach and maintain security accounts.

Also in SQL Server 2012, you can create user-defined server roles, which can assist in providing a more secure method of allocating server-level access to server administrators. Microsoft has included 6 part 1 Getting Started with Microsoft SQL Server 2012

the ability to create users within a database without requiring you to create a server login, known as contained databases. In past versions of SQL Server, prior to granting access at the database level, an administrator was required to create a server login. With the advent of SQL Server 2012, a user can be self-contained within a database.

Replication

SQL Server replication has been available in most releases of the product. Over time, replication types were introduced to ensure that users could configure replication architectures that satisfied a wide range of scenarios. Using SQL Server replication technology, you can distribute data locally, to different locations, using File Transfer Protocol (FTP), over the Internet, and to mobile users. Replication can be configured to push data, pull data, and merge data across local area networks (LANs) and wide area networks (WANs).

The simplest form of replication, snapshot replication, periodically takes a snapshot of the data and distributes it to servers that are subscribed to the publication. Snapshot replication is typically used to move data at longer intervals, such as daily or nightly. While this method is effective, it is often insufficient in satisfying the high demands of users for near real-time data. If higher throughput is required, users often leverage transactional replication. Instead of distributing snapshots of data, transactional

replication continuously sends data changes as they happen to the subscribers. Transactional replication is typically used in a server-to-server topology where one server is the source of the data and the other server is used as a backup copy or for reporting.

Both replication types are one-way data movements. But what if you need bidirectional movement? For example, assume you have mobile users who work offline. While they are offline, they enter

information into a database residing on an instance of SQL Server running on their laptops. What happens when they return to the office and connect to the network? In this scenario, the local instance will synchronize with the company’s primary SQL Server database. Merge replication will move transactions between the publisher and subscriber since the last time synchronization occurred.

SQL Server professionals debate the use of replication as a high availability (HA) or disaster recovery (DR) technology. Could it be used for either? There is a possibility; however, replication moves

only schema changes and data. To provide an effective HA or DR topology, every aspect of the instance should be included such as security, maintenance, jobs, and so on. Therefore, using replication in either case could pose potential problems in the event of hardware failure or a disaster.

See Also Chapter 19, "Replication," discusses the replication in depth.

SQL Server agent

SQL Server Agent runs as a separate service on an instance of SQL Server. Each instance of SQL Server has an accompanying SQL Agent service. The primary use of SQL Server Agent is to execute scheduled tasks, such as rebuilding indexes, backing up databases, loading the data warehouse, and so on.

It allows you to schedule the jobs to run at various intervals throughout the day or night. Chapter 1 Overview of Microsoft SQL Server 2012 7

To ensure that you are notified in the event of a job failure, SQL Server Agent allows you to configure operators and alerts. An operator is simply an individual and an email address. Once you configure an operator, you can send notifications or alerts to that person when a job succeeds, completes, or fails. high availability and Disaster recovery tools with growing demands on server availability and uptime, it is vital that your RDBMS include several

mechanisms that will ensure the consistency and availability of your data. SQL Server 2012 provides four technologies for high availability:

  • AlwaysOn Availability Groups In SQL Server 2012, Microsoft introduces AlwaysOn Availability Groups. An Availability Group supports failover for a set of databases and leverages the existing database mirroring technology to maintain secondary replicas of the database on local or remote instances of SQL Server. This technology differs from traditional failover clustering in two ways:
  • You can configure automatic failover without the use of a Storage Area Network (SAN).
  • You can configure one or more of the secondary replicas to support read-only operations. Since a SAN is no longer required, you now have the ability to configure HA and DR using one technology. By leveraging the database mirroring capability to move data over distances using TCP/IP, you can have a copy of the database stored in a data center located in a different geographic area.
  • Failover clustering SQL Server failover cluster instances provide high availability support at the server level. Prior to building an AlwaysOn SQL Server failover instance, you must create and configure a Windows Server failover cluster.
  • Database mirroring A predecessor of AlwaysOn, database mirroring provides high availability at the database level. It maintains two copies of the database on instances of SQL Server running on separate servers. Typically, the servers are hosted in separate geographic locations, not only ensuring HA, but also providing DR. If you want to incorporate automatic failover, you must include a third server (witness) that will change which server is the owner of the database. Unlike with AlwaysOn, with database mirroring you cannot directly read the secondary copy of the database. You can, however, create a snapshot of the database for read-only purposes. The snapshot will have a different name, so any clients connecting to it must be aware of the name change. Please note that this feature has been deprecated and replaced by AlwaysOn; therefore, going forward, you should use AlwaysOn instead of database mirroring.
  • Log shipping This is another technology that provides high availability at the database level, which is ideal for very low-latency networks. The transaction log for a specific database is sent to a secondary server from the primary server and restored. Just as with AlwaysOn and database mirroring, you can configure log shipping in a way that allows the secondary database to be read. 8 part 1 Getting Started with Microsoft SQL Server 2012

Note If you are familiar with SQL Server, you may be wondering why replication does not appear in the preceding list. This is because replication lacks a few key features, such as holistic database synchronization (as opposed to object-level movement).

SQL Server Integration Services

SQL Server Integration Services (SSIS) is a platform that allows you to build high-performance extraction, transformation, and loading (ETL) frameworks for data warehouses. So why is it included in here in a list of Database Engine components? In most cases SSIS is used for ETL; however, it offers a number of tasks and transformations that extend its usage well beyond ETL.

For example, if you are new to administering a SQL Server environment, SSIS provides you with the tools needed to perform several administrative tasks, including rebuilding indexes, updating statistics, and backing up databases, which make up the primary list of maintenance items that should be performed on any database. Without SSIS, as a new administrator you could spend a lot of time writing T-SQL just to get these activities running on a regular basis. But this is not the extent of the capabilities of SSIS for administrators. How often are you asked for an export of data to Microsoft Excel or to move data from one server to another? Using SSIS, you can quickly export or import data from various sources, including Excel, text files, Oracle, and DB2.

SQL Server Management tools

SQL Server 2012 includes two graphical user interfaces that enable you to manage, monitor, maintain, and develop in a SQL Server environment. The first is SQL Server Management Studio (SSMS), which allows you to perform just about any action you can think of against an instance of SQL Server. It is an integrated environment where you can access many instances of SQL Server. It consists of a broad set of tools with a rich set of interfaces and script editors that simplify the process of developing and

configuring SQL Server instances.

In addition to SSMS, SQL Server 2012 introduces SQL Server Data Tools (SSDT). SSDT is another integrated environment, but it was designed specifically for database developers. You can explore

the database and database objects using the SQL Server Object Explorer. So far, some of the most talked-about features of SSDT are the ability to easily create or edit database objects and data, and run queries directly from the interface. Using the visual Table Designer, you can change table schemas for both database projects and online database instances.

Summary

SQL Server offers a robust set of components and tools to enable you to design an efficient, flexible, and highly available database topology for your organization. Each component either complements or supplements the capabilities and functionality of the others. Throughout the rest of this book, you will discover how the components work independently and together. Spreading the knowledge of innovators oreilly.com

Want to read more?

Microsoft Press books are now available through O’Reilly Media.

You can buy this book in print and or ebook format, along with

the complete Microsoft Press product line.

Buy 2 books, get the 3rd FREE!

Use discount code: OPC10

All orders over $29.95 qualify forfree shipping within the US

This content was used from this link http://shop.oreilly.com/product/0790145338273.do?sortby=publicationDate.