Analysis Services Management Enhancements


The management tools for Analysis Services have completely changed in SQL Server 2005. In the SQL Server 2000 release, Analysis Services was managed using the Analysis Manager. With SQL Server 2005, the old Analysis Manager is gone. It’s been replaced by the SQL Server Computer Manager and the SQL Server Management Studio.

SQL Server Computer Manager

The SQL Server Computer Manager is the tool to use to start and stop the Analysis Services service. You access by right-clicking My Computer and then selecting the Manage option from the pop-up menu. To start and stop Analysis Services, expand the Services and Applications node and then the SQL Computer Manager node. The display will look like the one shown in Figure 10-2.

image from book
Figure 10-2: SQL Server Computer Manager

Right-click the Analysis Services (MSSQLSERVER) service entry shown in the right pane to stop, start, and pause the Analysis Services service. More information about the SQL Server Computer Manager is presented in Chapter 2.

SQL Server Management Studio

The SQL Server Management Studio is the primary management tool for both SQL Server and Analysis Services. You can use the SQL Server Management Studio to perform a number of different management functions. It can create Analysis Services databases, plus it can script and deploy Analysis Server databases. You can use SQL Server Management Studio to set permissions for Analysis Services objects as well as to monitor the end-user access to Analysis Services.

Multidimensional Expression (MDX) Query Editor

In addition to these management functions, SQL Server Management Studio includes the Multidimensional Expression (MDX) Editor, which you can use to write and execute ad hoc queries and build Analysis Services objects using scripts. You can see the new MDX editor in Figure 10-3.

image from book
Figure 10-3: SQL Server Management Studio MDX Editor

As you can see in Figure 10-3, SQL Server 2005’s new MDX editor provides full support for color-coded keywords and an output window containing the MDX query results. There’s also an integrated cube metadata browser that you can see in left side of the figure. More information about the SQL Server Management Studio is presented in Chapter 2.

Security

Security has been a major push for Microsoft in the past couple of years, and Analysis Services is no exception to this initiative. Analysis Services for SQL Server 2005 has over 100 security enhancements some of the most important of which are listed in the following section.

Security by Default

Analysis Services is designed to be secure by default. First, the service is installed to run with least privileges, reducing your company’s exposure if the system is compromised. Next, when the product is first installed, all of the security options are turned on by default. Likewise, all features that might expose the system to threats are turned off by default. This includes

  • Disabling HTTP access

  • Disabling anonymous connections

  • Disabling stored procedures

  • Disabling Openrowset queries

If they are needed, all of these features can easily be turned on by the administrator, but they are turned off out of the box.

Encryption

Encryption is also used to enhance the security in Analysis Services for SQL Server 2005. Encryption is now present in several different areas. First, the communications channel between the client application and the server can be encrypted and the server can be configured to access only encrypted connections. The local cubes can be encrypted, plus the Analysis Services backup files can also optionally be encrypted.

Fine-Grained Administrative Privileges

The introduction of fine-grained administrative privileges is another security-related enhancement of Analysis Services in SQL Server 2005. With SQL Server 2000, you needed to be a member of the OLAP Administrators group in order to make changes to the Analysis Services database and configuration. The new administrative privileges in SQL Server 2005 Analysis Services enable you to create different administrators for each database. There are new permissions to read metadata, as well as another permission that enables an account to just process cubes.

Backup and Restore Enhancements

Analysis Services backup and restore have also been improved in SQL Server 2005. The new backup improvements include removal of the 2GB backup limit, the ability to compress and encrypt the backup, and the ability to easily restore a backup to a different instance of Analysis Services.

Removal of the 2GB Backup Limit

SQL Server 2000 Analysis Services backups were limited to backup files of less than 2GB. That limit has been lifted in SQL Server 2005, and Analysis Services’ backup supports files up the NTFS limit of 16TB.

Ability to Compress and Encrypt the Backup

Analysis Services 2005’s backup is now able to both compress and encrypt the files that are backed up. These options will add some time to the window required to perform the backup but will result in a smaller and more secure backup. You also have the option of skipping the backup of security information.

Ability to Easily Restore the Backup to a Different Instance

SQL Server 2005 Analysis Services supports the ability to back up Analysis Services databases just as you can relational SQL Server databases and restore them to a different instance of Analysis Services. This makes moving Analysis Services databases between servers very easy.




Microsoft SQL Server 2005 New Features
Microsoft SQL Server 2005 New Features
ISBN: 0072227761
EAN: 2147483647
Year: 2005
Pages: 80
Authors: Michael Otey

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net