Modifying XML Indexes. The ALTER INDEX (Transact-SQL) Transact-SQL DDL statement can be used to modify existing XML and non-XML indexes. However, not all the ALTER INDEX options are available to XML indexes. The following options are not valid when modifying XML indexes: The rebuild and set option IGNORE_DUP_KEY is not valid for XML indexes. The rebuild option ONLINE must be set to OFF for secondary XML indexes. The option DROP_EXISTING is not permitted in the ALTER INDEX statement. The modifications of the primary key constraint in the user table are not automatically propagated to XML indexes. In the following example, an XML index is created and then modified by setting the option ALLOW_ROW_LOCKS to OFF.
CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML) GO -- Create primary XML index. By default, an XML index is enabled. CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML) GO CREATE PRIMARY XML INDEX PIdx_T_XmlCol ON T(XmlCol) GO ALTER INDEX PIdx_T_XmlCol on T DISABLE Go -- Verify index is disabled. SQL Server 2008 R2 Migration: Migrate & Manage. Download details: Microsoft SQL Server 2008 Service Pack 2 Feature Pack. Assessment and Planning (MAP) Toolkit for SQL Server 2008 R2. Accelerate your migration to SQL Server Get a complete network-wide inventory of SQL Server and Oracle instances, edition identification, and hardware assessment, when you use MAP as part of a comprehensive process for planning and migrating legacy database instances to the latest version of SQL Server. The wide-ranging details of databases and server instances MAP provides is information that you can use to consolidate databases and better utilize hardware and database resources.
With MAP you get the following reports for migration to the latest version of SQL Server: Discovery and inventory: Database inventory and reporting of SQL Server and Oracle instances on Windows and Linux based servers and Oracle schemas in your organization.Detailed reporting: Covers specific SQL Server instances that include component name, version, edition, and more. Includes wide-ranging details of databases and server instances that can be used for consolidation. Tutorial: SQL Server Management Studio. The SQL Server Management Studio tutorial introduces you to the integrated environment for managing your SQL Server infrastructure. SQL Server Management Studio presents a graphical interface for configuring, monitoring, and administering instances of SQL Server.
It also allows you to deploy, monitor, and upgrade the data-tier components used by your applications, such as databases and data warehouses. SQL Server Management Studio also provides Transact-SQL, MDX, DMX, and XML language editors for editing and debugging scripts. This tutorial will help you understand the presentation of information in Management Studio and how to take advantage of the features. Note that this tutorial applies to the complete installation of Management Studio that is included with all editions of SQL Server except SQL Server Express. The best way to get acquainted with Management Studio is through hands-on practice.
This tutorial is divided into four lessons: Lesson 2: Writing Transact-SQL. Migrating to SQL Server 2008 R2. Planning a SQL Server Installation. To install SQL Server, follow these steps: Review installation requirements, system configuration checks, and security considerations for a SQL Server installation. Run SQL Server Setup to install or upgrade to a later version. Use SQL Server utilities to configure SQL Server. Regardless of the installation method, you are required to confirm acceptance of the software license terms as an individual or on behalf of an entity, unless your use of the software is governed by a separate agreement such as a Microsoft volume licensing agreement or a third-party agreement with an ISV or OEM. The license terms are displayed for review and acceptance in the Setup user interface. Unattended installations (using the /Q or /QS parameters) must include the /IAcceptSQLServerLicenseTerms parameter. You can review the license terms separately at Microsoft Software License Terms.
Cloud Computing: An Introduction to SQL Azure. With SQL Azure, you can realize the benefits of a familiar relational database platform, plus the benefits of cloud computing. Data is the lifeblood of business. Ensuring that it’s secure, available and easily accessible are fundamental requirements of any IT department. More importantly, ensuring that data is used well—to drive processes, inform decision making and react intelligently to changing circumstances—is what differentiates successful businesses from those left behind.
The manner in which businesses ensure the availability of data is rapidly changing. Hosted services—and the very idea of software as a service—for everything from core datacenter functions like e-mail and business intelligence, to personal applications like photo-sharing and file synchronization, have become an everyday part of how we interact with our information. Cloud computing has enjoyed a meteoric rise over the past few years, both as a concept and as a practical component of IT infrastructure. Let PowerShell do an Inventory of your Servers. If you run a regular and comprehensive inventory of all the servers you manage, you can solve problems more quickly and answer most questions from management. If you then repeat these reports over a time period, it helps to to track trends such as disk space usage so you can spot trouble before it becomes a problem.
Allen describes a PowerShell script to do the inventory. Whether you're a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage. This inventory can take any number of forms but, ideally, will allow some aggregation of information. For example, your database data and log files will grow in size over time. PowerShell makes it easy to issue WMI (Windows Management Instrumentation) queries, which we can use to collect important non-SQL Server information about our servers. Win32_PingStatus: is the Server online? As a quick demonstration of how the Win32_PingStatus query works, here's the basic PowerShell script: $responds = $false break if (!
Features Supported by the Editions of SQL Server 2008 R2. Feature Name Enterprise Business Intelligence Standard Web Express with Advanced Services Express with Tools Express Maximum Compute Capacity Used by a Single Instance (SQL Server Database Engine) 1 Operating System maximum Limited to lesser of 4 Sockets or 16 cores Limited to lesser of 1 Socket or 4 cores Maximum Compute Capacity Used by a Single Instance (Analysis Services, Reporting Services) 1 Operating system maximum Maximum memory utilized (SQL Server Database Engine) Maximum memory utilized (Analysis Services) Maximum memory utilized (Reporting Services) Maximum relational Database size 1 Enterprise Edition with Server + Client Access License (CAL) based licensing is limited to a maximum of 20 cores per SQL Server instance.
Top Server Core support 1 Yes Log Shipping Database mirroring Yes (Safety Full Only) Witness only Failover Clustering Yes (Node support: Operating system maximum Yes (Node support: 2) Backup compression Database snapshot AlwaysOn Availability Groups SQL Server Multi-Subnet Clustering Yes 1.