Friday, May 29, 2009

SQL BPA command line has encountered a problem and needs to close.

I got following error message while performing upgrade from SQL Server 2000 to SQL Server 2005.
SQL BPA command line has encountered a problem and needs to close.
After little googling i found a solution that
  • Go to C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA
  • Make folder BPAClient
  • Go to C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin
  • Copy BPAClient.dll
  • Paste it in to the newly created directory BPAClient on following path C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\BPAClient
To accomplish the above task I have created a cmd script in .bat file. On double click the batch script will run and above task will be performed. Please click here to download .bat file. After these small steps upgrade process will go on without any problem. As a follow up to this post i will try to list here some reasons and background for this error message.

Tuesday, May 26, 2009

Change recovery model of SQL Srever database before index rebuild

Index rebuild may produce a lot of transaction logs. Hence it may cause of heavily populate your log file. So it would be better option to change recovery model of that specific database to simple before index rebuild process in it. Once index rebuild process is finished database may again be reverted to recovery model full. Assuming that AdventureWorks is in single user mode, we may run following commands to change and revert back the recovery model of a database for index rebuild operation.
USE AdventureWorks
GO

-- Set recovery mode to simple before index rebuild
ALTER DATABASE AdventureWorks
SET recovery SIMPLE
GO


-- Begin index rebuild process for selected tables
DBCC DBREINDEX ("HumanResources.Employee",'',90)
GO

-- Set recovery model back to full
ALTER DATABASE AdventureWorks
SET recovery FULL
GO

While implementing above mentioned procedure make sure to reattain your backup sequence as without a new full backup and later log backup you will not be able to recover to the point if required. The reason is that backup sequence of log becomes disturb while changing recovery model.

Importance of terminology

Often i noticed that most of mechanics and electricians who are not with great educational background, they can complete various tasks efficiently than many of qualified engineer. They are good in work but are not able to communicate or enhance their knowledge due to lack of standard terminology for their field.
Being among a flood of knowledge all around me in the form of blogs, portals, mails, feeds, as IT professional i strongly believe that in order to get most in less time i should also update my terminology asset about my profession. As much as i am equipped with terms and key words of my field i would be able to grasp knowledge from all information passing in front of me.
I would like to share here the glossary of SQL Srever Terms in BOL
Consulting it regularly would surely help to increase the terminology and key words

Saturday, May 23, 2009

The primary data or log file cannot be removed from a database.

The error message
Msg 5009, Level 16, State 9, Line 1
The primary data or log file cannot be removed from a database.
is run time error of severity level 16 and can occur on any version of SQL Server. It appears when you try to remove the primary data or log file from a database. This operation is not allowed. You can not remove the primary data or log file from a database.

Thursday, May 21, 2009

List SET options for current connection through DBCC USEROPTIONS

Often i am required to verify any of batch level options for my current connection. This task may be easily completed through use of informational DBCC command (DBCC USEROPTIONS) in following way
DBCC USEROPTIONS
GO


Options are listed in following format

Also it is relevant to mention here that options may be of
  • SQL Server instance level
  • SQL Server database level
  • T-SQL batch level
  • T-SQL Statement level
OLEDB applications can control some ANSI options through their connection options. Microsoft recommends not to change SET options through SET statements. Instead using the connection properties of ODBC/OLEDB or system stored procedure sp_configure is preferable.
  • A database level option overrides the instance option
  • Batch level option overrides database option
  • Statement level option overrides batch level options

Choosing between system objects and customized scripts

Most of the times i am amazed to notice that there are several commands, stored procedures and functions provided by SQL Server that may be effectively used during several tasks. We know these objects as DBCC commands, system stored procedures, DMVs, and system functions. For efficiency and effectiveness it is important for a DBA to get update of all such system provided objects. It would prevent the reinvention of wheel.


  • Click here to get list of SQL Server T-SQL system stored procedures
  • Click here to get list of SQL Srever T-SQL system functions
  • Click here to get list of SQL Srever T-SQL DBCC commands

Wednesday, May 20, 2009

Stepped out of shadows on May 20 a year ago

Today is first birthday of my SQL Server blog. I started DBDigger blog with aim to manage and share my SQL Server knowledge base. Blogging provided me a chance to excel my expression power and to be a helpful member of SQL Server community. I am happy that in its infant stage with about 400 posts DBDigger tends to help an average of 50 visitors daily. Also I would like to thank Google for their best free blog related services on blogspot.

Tuesday, May 19, 2009

Get list of all databases through SP_DATABASES

While planning to drop many of my test databases, i was tired to write name of all such databases. While looking for any stored procedure to get list of all databases on my SQL Server instance, i found SP_DATABASES. It provided me complete list of databases along with over all size statistics for each.
SP_DATABASES
GO


From here is copied names of required databases to be dropped.

Only base table columns are allowed in the TEXTPTR function.

The error message
Msg 280, Level 16, State 1, Line 15
Only base table columns are allowed in the TEXTPTR function.
is a run time error message and is raised when you try to use constructs such as computed columns in the TEXTPTR function. To resolve the error provide base columns as input to TEXTPTR function. The TEXTPTR function only accepts "base" columns as valid input.

Friday, May 15, 2009

Analyzing SQL Agent Jobs and Jobs Steps History in SQL Server

My article related to Analyzing SQL Agent Jobs and Jobs Steps History in SQL Server is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Analyzing SQL Agent Jobs and Jobs Steps History in SQL Server

One or more files listed in the statement could not be found or could not be initialized.

The error message
Msg 5009, Level 16, State 8, Line 1
One or more files listed in the statement could not be found or could not be initialized.
is a run time error message and appears when you try to use a file in a statement but this file could not be found or initialized. There may be several root causes for this error. So to execute the statement you have to remove the root cause.
Please see comments for an example.

Wednesday, May 13, 2009

Performance Configuration Issues for SQL Server Analysis Services

From an operational prospective, you can improve Analysis Services performance by keeping partition sizes reasonable, setting partition data slices, defining appropriate aggregations on all partitions, and running the Optimize Schema Wizard in the cube editor.

Partition Size
You can divide a cube into multiple partitions if you are using SQL Server 2000 Enterprise Edition (or SQL Server 7.0 Enterprise Edition) to increase query and processing performance. You should consider increasing the number of partitions if a partition file exceeds 5 GB or 20 million records. To determine the size of each partition, review the partition files in the Data folder.

Data Slice
When you partition a cube, you should define the data slice for each partition using the Partition Wizard. The Partition Wizard does not require you to set this data slice when you create a partition. As a result, it is possible (even easy) to create a partition without setting the data slice. In fact if you simply click through the Partition Wizard pages taking the default values, you end up with a partition without a data slice being set. You should verify that each partition of each cube in each Analysis Services database has a data slice defined. The only exception to this is if you have a cube with only one partition-in that case the data slice should not be set, because you want all of the cube data to be placed in that one partition. To verify that a data slice is defined, edit the partition in Analysis Manager and then step through the Partition Wizard pages. Defining the data slice enables Analysis Services to quickly eliminate irrelevant partitions from query processing. The data slice identifies the actual subset of data contained in each partition. Unless Analysis Services knows the range of data contained in each partition, it must query each partition, which negates much of the query performance benefit of partitions. To draw an analogy with SQL Server, creating a partition without a data slice is like creating a partitioned view without the CHECK clause. While you can do it, you force the query optimizer to scan all of the partitions in the view because you haven't given it enough meta data to figure out what partition to access when a query is issued. While the Analysis Service's runtime engine does not use a relational query optimizer (it has its own component that accomplishes a similar operation), it uses the data slice in roughly the same way: as meta data to tell it which partitions to scan if an aggregate cannot be
used or is not available. If you partition a cube by month, and have 36 months worth of data (in 36 partitions), and if you don't specify the data slice, then the runtime engine must scan all 36 partitions to answer a query. If you specify the data slice, it could potentially only have to scan 1/36th the amount data, with an obvious improvement in
performance.. Setting a data slice also causes Analysis Services to add a join and a WHERE clause to the SQL statement used for retrieving data from the source database during processing. The WHERE clause limits the data retrieved by the SQL statement to the data that belongs in the data slice. For example, if you say that a partition's data slice
is June 2003, then Analysis Services adds a join to the time dimension and adds the WHERE clause: WHERE = 'June' AND = '2003'
or whatever the appropriate member/level names are. If you do not define a data slice and you have multiple partitions, Analysis Services does not restrict the data that is retrieved from the source database. Without the data slice, if you just happen to have July 2003 data in the June partition. By specifying the data slice, the system can add these JOIN and WHERE clauses that assist in maintaining the integrity of the data. If Analysis Services is loading data for each partition from separate tables in the source database, this may yield some performance benefits. However, you should not disable the automatic generation
of the WHERE clause unless you are totally sure that the relational database partitioning is 100% correct when loading data. You must be 100% certain of the correctness of the data in all partitions of all cubes on the server in order to use this setting safely. Without the WHERE clause for your protection, double-counting data (or many-times counting of data) may occur, which could lead to server crashes if inconsistent data is processed. If you disable the generation of the WHERE clause, you assume all responsibility for enforcing data integrity between the data source and the data slice.

Aggregations
The most effective technique you can use to improve overall query responsiveness (assuming that the Analysis Services computer has sufficient memory and hard disk resources) is to design effective aggregations of fact data in each cube partition. However, too many aggregations will unnecessarily increase processing time without significantly improving query performance. When you use multiple partitions to increase query and processing performance, it is possible to deploy a new partition with no aggregations. While deploying partitions with different aggregation designs is a common optimization technique, deploying a partition with no aggregations generally indicates an error in deployment that can cause performance problems. You should verify that a minimum number of aggregations exist in each partition. You can quickly determine whether aggregations are defined on a partition by looking at the combined sizes of the .agg.flex.data and .agg.rigid.data files for each partition. Designing too many aggregations will slow processing and designing too few aggregations will slow
querying. Ensure that all partitions have a minimum number of aggregations - perhaps 10%.

Schema Optimization
Running the Optimize Schema tool on a cube eliminates unnecessary joins between dimension tables and fact tables, if certain conditions are met. By default, when you first create a cube, Analysis Services constructs a SQL query against the fact table that is a "N+1"-way join (where N is the number of dimensions). From the join, the Analysis Services query extracts the lowest-level key. From that key, Analysis Services begins the aggregation process. Having a 6-way join is typically not a major performance problem in most modern relational database systems. However, if your cube has 15 or 20 dimensions, the resulting multi-table join may suffer significant performance problems. Regardless of the number of dimensions in your cube, the Analysis Services query to the relational database is resolved faster and the data flows into Analysis Services more quickly during processing if you eliminate some of these joins. Fortunately, there is a common design technique that can greatly help the situation. Many star or snowflake schema designs are constructed in such a way that the foreign key that points from the fact table to the lowest level dimension table is not some random number, but it is also the member key itself. If that is true, then Analysis Services can "optimize away the join" and pull the member key directly from the fact table instead of using a join to the lowest-level dimension table. These conditions are checked for join removal are
  • The dimension must be a shared dimension.
  • The dimension must have been processed before you optimize the cube schema.
  • The member key column for the lowest level of the dimension must contain the keys that relate the fact table and the dimension table, and this must be the only key necessary to relate the fact table to the dimension table.
  • The keys in the member key column for the lowest level of the dimension must be unique.
  • The lowest level of the dimension must be represented in the cube; that is, the level's Disabled property must be set to No. The level can be hidden.
If these conditions are met with respect to a dimension used in a cube, and the cube's schema is optimized using the Optimize Schema command, Analysis Services composes a query that does not contain a join to the dimension table in the database when processing the cube. If these conditions are met for all dimensions in the cube, the Analysis server needs to read only the fact table to process the cube. Processing time reductions often can be substantial when this optimization technique is used. Cube schema optimization applies to all partitions of the cube, whether the partitions are processed independently or as a group. So, as a general rule, after you have designed the schema for a cube, you should run the Optimize Schema command. It removes the joins that meet the foregoing conditions. Next, you should determine which dimensions were not eliminated from the join and then determine how to meet the required conditions to eliminate the dimension table from the join. If you have partitioned your cube and specified the data slice, the dimension table used for the data slice cannot be eliminated. This join is set to protect you so that no additional, non-data slice data is included in the partition. If you do optimize away a dimension, you should be aware that the inner join that you have just eliminated had a side effect that may expose problems with your source data. The inner join to the dimension table eliminates fact table records that do not have matching dimension table records (this is what an inner join will do). This means that when you remove the inner join and start using the fact table member keys, you may start seeing processing errors that you were not getting before. When Analysis Services processes a record in the fact table that does not have a corresponding entry in the appropriate dimension table, Analysis Services generates an error. If you recreate a cube, add a dimension to a cube, or remove and then re-add a dimension, you must rerun the Optimize Schema command to re-optimize the cube. New dimensions are always added unoptimized.

Logging and Error Reporting for SQL Server Analysis Server

Analysis Services records a query log to enable you to analyze query patterns and improve your aggregation design. You can configure the properties of this query log. You can also enable a processing log and enable Analysis Services error reporting.

Query Log
To enable the Usage Based Optimization Wizard to design aggregations based on past usage patterns and to enable the Usage Analysis Wizard to generate reports analyzing query usage, Analysis Services records the levels touched by every Nth query in a query log. By default, every tenth query is logged. The default location for the query log is C:\Program Files\Microsoft Analysis Services\Bin\msmdqlog.mdb. This file, like any log file, should be secured from unauthorized access.

Source Data Access for SQL Server Analysis Services

The MSSQLServerOLAPService service account must also have logon account permissions to access the source data in the source database if trusted connections are used to access the source data. With trusted connections, the MSSQLServerOLAPService service account is used to connect to the data source. If trusted connections are not used, you can specify a user name and password. The permissions required when connecting to a data source also depends on the type of storage structure used for the Analysis Services partitions. When MOLAP storage is used, the service account must have at least SELECT permissions on the source database. If ROLAP or HOLAP storage is used, the service account must have at least SELECT and CREATE TABLE permissions on the source database.

Service Accounts for SQL Server Analysis Services

To understand the permissions required for the MSSQLServerOLAPService and SQL Server Agent service accounts, you need to understand the security context in which various operations are executed. Certain tasks are performed in the context of the logged-on user, and other tasks are performed in the security context of the MSSQLServerOLAPService service account. When you request that Analysis Manager create a new object or browse for an existing object, the task is executed in the security context of the interactive user account of the user performing the task. When Analysis Services processes dimensions, partitions, and mining models, this task is performed in the security context of the MSSQLServerOLAPService service account. You must grant sufficient rights to this service account to ensure that processing will succeed. It is not uncommon for users to assume that if they can create objects, then they can process them. You must ensure that the MSSQLServerOLAPService and the SQL Server Agent service accounts have sufficient permissions for the tasks they need to perform. At a minimum, the service account must be a member of the OLAP Administrators group. This permission is needed for any user (or service that is acting on behalf of a user) that manages an Analysis Services server.

Service Accounts for SQL Server Analysis Services

To understand the permissions required for the MSSQLServerOLAPService and SQL Server Agent service accounts, you need to understand the security context in which various operations are executed. Certain tasks are performed in the context of the logged-on user, and other tasks are performed in the security context of the MSSQLServerOLAPService service account. When you request that Analysis Manager create a new object or browse for an existing object, the task is executed in the security context of the interactive user account of the user performing the task. When Analysis Services processes dimensions, partitions, and mining models, this task is performed in the security context of the MSSQLServerOLAPService service account. You must grant sufficient rights to this service account to ensure that processing will succeed. It is not uncommon for users to assume that if they can create objects, then they can process them. You must ensure that the MSSQLServerOLAPService and the SQL Server Agent service accounts have sufficient permissions for the tasks they need to perform. At a minimum, the service account must be a member of the OLAP Administrators group. This permission is needed for any user (or service that is acting on behalf of a user) that manages an Analysis Services server.

Data Source Configuration for SQL Server Analysis Services

When you create a new database within an Analysis Services instance, one of your first tasks is to define the data source for the database. A data source contains the information necessary to access source data for the database objects. The term "data source" actually refers to the data source object that is created, not the source data itself. When you define the data source in Analysis Manager, the name given to the object is either - or -. However, to eliminate confusion when the database is moved to another server, you should change the default naming convention by creating a logical name for the data source unrelated to the name of the original computer on which the database was initially created.
To create a logical name for the data source object in Analysis Manager, create the data source object. Then copy the new data source object and paste it into the same Analysis Services database. You are then prompted to define a new name for the data source object. The name you choose should reflect the logical type of data such as Sales Data, or Personal Data. After you define the new logical name, delete the original data source
object. Thereafter, when you move an Analysis Services database between computers, you can simply change the underlying server and database in the connection string by modifying the properties of the data source object in Analysis Manager (or in your script).
In addition to renaming your data sources to logical rather than physical names, you should ensure that your deployment computers use the same name. If your development computer has its data source named Sales Data, then your QA computer should have its data source named Sales Data, and your production computer should have its data source named Sales Data. Using consistent names across the development, QA, and production computers makes migration of individual pieces easier by cutting and pasting between the Analysis Services databases. If you do not change the name of the data source object before you create objects in the database, you will not be able to change the name of the data source object without using third-party utilities.

Data and Temporary File Locations for SQL Server Analysis Services

An Analysis Services instance has a Data folder and a Temporary folder. Analysis Services uses the Data folder to store the multidimensional structures for all the objects defined on the Analysis Services instance. It uses the Temporary folder to supplement the memory allocated to each process buffer when a process buffer is too small for the aggregations being processed. The default location for both of these folders is C:\Program Files\Microsoft Analysis Services Data. You can change the location for either or both during setup or after installation. To change the location after setup, right-click the Analysis server object in Analysis Manager, and then click Properties. You can also use the sample script provided in Appendix D, "Sample Script for Changing the Data Folder Location, to change the Data folder programmatically. If you use virus-scanning software on the Analysis Services computer, you should disable scanning of the Analysis Services Data, Temporary, and Bin folders.
You should place the Data folder on its own RAID array; RAID 10 or RAID 1 + 0 provides the best performance but RAID 5 is frequently fast enough for many Analysis Services installations. The main activity of Analysis Services is reading data from the files in the Data folder in response to user queries, not writing to files in the Data folder. Once you determine the amount of space required for the data, index, and aggregation
structures, you should allocate approximately double that amount of disk space to allow sufficient space to enable you to refresh the data and hold shadow files during processing.
Note Because the Data folder stores security files that control end users' access to Analysis Services objects, you must secure the Data folder against unauthorized access. Only members of the OLAP Administrators group and the Administrators group should have access to the Data folder. If you move the Data folder location after installation, you must configure these security settings manually.
You should place the Temporary folder, if it is actually used, on a RAID array that yields excellent write performance and that is on a different physical drive than the Data folder. Consider using RAID 0, 1, 0+1, or 10 depending on your budget requirements and amount of use. However, for best performance, it is more important to allocate a sufficiently large process buffer to obviate the need for temporary files during processing. If processing requires temporary files, the algorithm is an order of magnitude slower than if the process buffer was large enough to perform the processing entirely in memory. If you find that the files in the Temporary folder structure are used extensively and you cannot eliminate their use, you can add a second Temporary file folder on a different physical drive by adding the TempDirectory2 registry key
(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Current Version) and specifying a location on a separate physical drive for the second Temporary folder. When you must use temporary files, using two Temporary folders increases processing performance because the data in one Temporary folder is sequentially read, merged with new segment data, and then written to the second Temporary folder (in 64- kilobyte segments). The data in the second Temporary folder is then read, merged with new segment data, and written to the first Temporary folder. This process continues until the calculation of aggregations is complete.

Process Buffer for SQL Server Analysis Services

Analysis Services creates a process buffer in memory for each partition it processes. It allocates memory to each buffer as it is needed, and releases this memory from each buffer when partition processing is complete. Analysis Services uses each buffer for two separate tasks.
  • First, Analysis Services loads fact data for the partition from the read-ahead buffer into the process buffer and then sorts, indexes, and writes the fact data into the fact level of the MOLAP partition file in segments. The sorting process includes as much data as can be held in the process buffer at one time.
  • Second, Analysis Services uses the process buffer for calculating aggregations. If the process buffer in memory is not large enough to hold all calculations during this phase, Analysis Services supplements the process buffer with temporary files on disk to complete the calculation of aggregations.
The Process buffer size setting on the Processing tab in Analysis Manager (the ProcessReadSegmentSize value in the registry) determines the maximum size of each process buffer. By default, the maximum size of each process buffer is approximately 32 MB. For most applications, this is probably too small and should be immediately increased. A more effective setting is at least 150 to 200 MB. If the size of each process buffer is large enough to efficiently sort and index large portions of the fact data before it is written to segments in the partition file, overall data organization and query responsiveness improve. If Analysis Services exceeds the size of a process buffer while creating aggregations, Analysis Services changes its algorithm to use temporary files that augment the memory allocated to the process buffer. If temporary files are used, Analysis Services moves aggregations between these temporary files and the memory allocated to the process buffer as the aggregations are being calculated. All aggregations for a partition are calculated at once and must fit into memory; otherwise temporary files are used.

Process Buffer for SQL Server Analysis Services

Analysis Services creates a process buffer in memory for each partition it processes. It allocates memory to each buffer as it is needed, and releases this memory from each buffer when partition processing is complete. Analysis Services uses each buffer for two separate tasks.
  • First, Analysis Services loads fact data for the partition from the read-ahead buffer into the process buffer and then sorts, indexes, and writes the fact data into the fact level of the MOLAP partition file in segments. The sorting process includes as much data as can be held in the process buffer at one time.
  • Second, Analysis Services uses the process buffer for calculating aggregations. If the process buffer in memory is not large enough to hold all calculations during this phase, Analysis Services supplements the process buffer with temporary files on disk to complete the calculation of aggregations.
The Process buffer size setting on the Processing tab in Analysis Manager (the ProcessReadSegmentSize value in the registry) determines the maximum size of each process buffer. By default, the maximum size of each process buffer is approximately 32 MB. For most applications, this is probably too small and should be immediately increased. A more effective setting is at least 150 to 200 MB. If the size of each process buffer is large enough to efficiently sort and index large portions of the fact data before it is written to segments in the partition file, overall data organization and query responsiveness improve. If Analysis Services exceeds the size of a process buffer while creating aggregations, Analysis Services changes its algorithm to use temporary files that augment the memory allocated to the process buffer. If temporary files are used, Analysis Services moves aggregations between these temporary files and the memory allocated to the process buffer as the aggregations are being calculated. All aggregations for a partition are calculated at once and must fit into memory; otherwise temporary files are used.

Paging Files for SQL Server Analysis Services

By default, Windows uses a single paging file equal to approximately 1.5 times the amount of physical memory in your computer. However, because Analysis Services makes extensive use of Windows paging files, you should always add a second paging file equal to the amount of physical memory on your computer. The SQL Server relational and multidimensional run time engines work with memory very differently. The SQL Server relational engine directly maps and controls physical memory usage, while the Analysis Services multidimensional engine relies on the Windows operating system to allocate additional memory.
Analysis Services may need to use the paging file for its memory needs. You must ensure that the total paging file space is more than that configured by default, so that Analysis Services has sufficient virtual memory if the Windows operating system has insufficient physical memory. While the Windows operating system has provisions for effectively controlling the general use of memory, Microsoft strongly recommends that customers configure servers with an adequate amount of memory so that extensive paging does not occur. If the main processing component of Analysis Services, the msmdsrv process, causes extensive paging, processing performance suffers.

Processor usage for Analysis Server

If you are running Analysis Services on a multiple processor computer, Analysis Services schedules threads on all available processors in the computer. Unlike the SQL Server service, Analysis Services does not natively support processor affinity to control the processors on which its threads will execute. Because Analysis Services is highly multithreaded, Analysis Services can consume all available processing resources. For this reason, you should use a dedicated server for Analysis Services in most cases. If you must share the computer resources with other server applications, you should select a server application that supports processor affinity, such as SQL Server. By setting processor affinity in SQL Server, you can control the processors executing the SQL Server threads and the priority of these threads, to ensure that sufficient processor resources remain available for Analysis Services threads.
If you need to control the processors on which Analysis Services threads execute, you should also consider using Microsoft Windows Server™ 2003 Enterprise Edition or Windows Server 2003 Datacenter Edition. These editions of Windows Server 2003 include the Windows System Resource Manager (WSRM), which allows an administrator to set processor and memory allocation policies for applications running on the server. WSRM enables you to select the Analysis Services process and limit Analysis Services threads to specific CPUs or to a specific threshold of processor resources. Compared to a transactional database, OLAP systems typically take more space for the sort area but less space for the rollback area. Most transactions in an OLAP system take place as part of a batch process. Instead of having several rollback areas for user input, you may resort to one large rollback area for the loads, which can be taken offline during daily activity to reduce overhead.

Security Considerations for SQL Server Analysis Services

Administrator Security
When Analysis Services is installed, the setup program creates the OLAP Administrators local group on the Analysis Services computer and adds the user account of the person installing Analysis Services to this group. All members of the local Administrators group are automatically members of the OLAP Administrators group, regardless of whether they are explicitly added to the OLAP Administrators group. The OLAP Administrators group is granted the following rights on the Analysis Services computer:

  • Full control permission to the Server Connection
  • Write permissions through the MsOLAPRepository$ hidden share (the ..\Microsoft Analysis Services\Bin folder). The MsOLAPRepository$ hidden share is created during setup. Analysis Services uses the hidden share when reads from or writes to the repository when it is stored in an Access database (this is the default location and store for the repository).
  • Full control rights to the Bin and Data folders under the ..\Microsoft Analysis Services directory. This includes full control rights to the repository files, Msmdrep.mdb and Msmdrep.ldb. With clustering, if the Data folder is on a different computer than the computer on which Analysis Services is running, you must ensure that the members of the OLAP Administrators group on the Analysis Services computer have full control rights to this Data folder. This includes the account under which Analysis Services is running. Generally this is accomplished through the use of a domain group. There is only one level of administrative access to Analysis Services. A member of the OLAP Administrators group has complete administrative access to Analysis Services objects, full read access to all cubes and dimensions, and full write access to all write-enabled cubes and dimensions (regardless of any contrary role definitions). A domain or local user that is not a member of the OLAP Administrators group can perform no administrative tasks and has read or write access to the extent permitted based on dimension-level or cell-level security.


End-User Security
End-user security in Analysis Services is based on Windows user accounts and groups. Before you begin configuring end-user security in Analysis Services, you must first create the user accounts and groups within Active Directory. A frequently asked question is whether Analysis Services supports other kinds of authentication. The answer is Yes and No. Yes, it can support other types using HTTP access and IIS (IIS 6.0 includes some new authentication options). However, all these authentication types must ultimately map to a Windows user account in the general sense: including domain accounts, local accounts, the guest account (if enabled), or the built-in NT AUTHORITY\ANONYMOUS LOGON account. Therefore, no, Analysis Services does not support SQL standard security or any similar technology where the authentication is not based on Windows user accounts.

Security Roles
After you have created the appropriate Windows user and group accounts, you create security roles within Analysis Services that contain Windows user and group accounts, and define the access each role has to Analysis Services data. You can use database roles, cube roles, and mining model roles.

  • A database role can be assigned to multiple cubes or mining models in a database. Database roles provide default permissions for cube or mining model roles. By default, a database role specifies only read access and does not limit the dimension members or cube cells visible to end users. You can, how ever, specify read/write access and limit dimension members that are visible and updatable.
  • A cube role applies to a single cube. Defaults in a cube role are derived from the database role of the same name, but some of these defaults can be overridden in the cube role. In addition to the database role features of specifying read/write access and limiting dimension members that are visible and updatable, a cube role also enables you to specify cell-level security. Cell-level security has less memory overhead than dimension security.
  • A mining role applies to a single mining model. Defaults in a mining role are derived from the database role of the same name, but some of these defaults can be overridden in the mining role. A domain user or group can be a member of multiple roles within Analysis Services. In this case, the effective rights of the user are the combined access characteristics specified in these roles.

Dimension-, Cell-, or Application-Level Security
When you use dimension-level security to limit the dimension members that are visible or updateable, Analysis Services must create a replica dimension in memory when a user connects which reflects the dimension members that user is permitted to see.

A user is not in any role: no access is permitted to the dimension at all.
This is actually an interesting case. If a user is allowed access to a cube (based on the user's membership in the roles), the user can see the cube as a valid cube, capable of being queried. However, when dimension security is applied, the allowed set is empty in one or more dimensions. This places Analysis Services in a difficult position because Analysis Services cannot tell the user where access is being denied (because that is a security violation in and of itself). As a result, Analysis Services forcibly disconnects the session with the user - and the user receives the purposely ambiguous error message "The connection to the server is lost."

Backup and Recovery plans for SQL Server Analysis Services

Before you back up your Analysis Services data, you must ensure that Analysis Services is not processing any dimensions, partitions, or mining models. Because Analysis Services performs some processing tasks as background processes, determining when all processing has been completed can sometimes be difficult. Also, you must ensure that another administrator is not changing any of the meta data while you are performing a backup. One way to ensure Analysis Services is quiescent is to stop Analysis Services before you perform the backup
Backup Options Analysis Services provides two techniques for backing up an Analysis Services database: archiving and copying files.

Archiving
You can archive an Analysis Services database and the repository to one or more .cab file using the msmdarch command (msmdarch.exe), either from within Analysis Manager or from a command prompt. Msmdarch uses .cab storage algorithms, which limits the size of any single .cab file to 2 GB. As a result, no individual file in the Data folder (such as any single partition) can exceed 2 GB, or else msmdarch cannot be used for backup.
When using msmdarch, always specify a log file location to capture any messages generated during the archive process. If the archive process fails, these messages can help you determine why the archive process failed. However, msmdarch does not back up the query log. To back up the query log, perform a file-based backup of the SMDQLOG.mdb file. If you do not, a new query log is created from scratch when you start a restored instance.

Copying Files
You can use Analysis Manager to copy the meta data for an Analysis Services database from one instance of Analysis Services to another Analysis Services instance, provided that both instances are registered in Analysis Manager. Because only the meta data is copied to the target server using this method for release management, you will need to process the Analysis Services database on the destination server (after updating the data source properties, if necessary) before users can query the data in the new location. Because copying and pasting is so easy and quick, and because you are frequently only working with a subset of data in the development environment, copying and pasting is generally the quickest way to deploy an Analysis Services database on a different server. The downside is that it requires all of the dimensions, cubes, and partitions to be processed (which must be incurred anyway if the data sets are different). To determine your preferred approach, you need to compare the time and overhead of fully reprocessing the database with the msmdarch.exe archive and restore time. In most cases, full reprocessing is the fastest method. But you will find that it varies based on the underlying infrastructure.

If you cannot use msmdarch, you can use a file copy program, such as Windows Backup, to back up all the files in the Data folder. With a file copy backup, you back up all databases on the server. With msmdarch, you can back up a single Analysis Services database. In addition, the file copy technique does not back up the repository or the query log file. If you use the file copy technique, you must back up the repository at the same
time you back up the Data folder, to ensure that the repository and the data in the Data folder remain synchronized. You must also back up the query log (the SMDQLOG.mdb file), or begin capturing query information from scratch. While it is possible to back up just an individual database (by copying the contents of its Data sub folder and the .DBO file). You cannot back up individual portions of the repository. The repository is needed on a full restore, or if the meta data has changed since the last backup.


File-Based Backup and Restore
If neither of the previous two methods is suitable for your situation, you can also use a file-based backup program to back up the entire Data folder and then restore it to the destination folder. With this method, you must deploy all databases within an Analysis Services instance, rather than a single database. If you use this method, you must also back up the repository and then restore the repository on the destination server. While the repository is technically not required to run the OLAP service, it is required to run Analysis Manager and thus to properly administer the server. The meta data in the repository must match the contents and structure of the Data folder. If you have data in remote partitions (a feature that is rarely used) and in writeback tables, you must first back up the data in remote partitions using a file-based backup method and the writeback tables using SQL Server backup, and then restore them before you bring your database back online.

Backup and Recovery plans for SQL Server Analysis Services

Before you back up your Analysis Services data, you must ensure that Analysis Services is not processing any dimensions, partitions, or mining models. Because Analysis Services performs some processing tasks as background processes, determining when all processing has been completed can sometimes be difficult. Also, you must ensure that another administrator is not changing any of the meta data while you are performing a backup. One way to ensure Analysis Services is quiescent is to stop Analysis Services before you perform the backup
Backup Options Analysis Services provides two techniques for backing up an Analysis Services database: archiving and copying files.

Archiving
You can archive an Analysis Services database and the repository to one or more .cab file using the msmdarch command (msmdarch.exe), either from within Analysis Manager or from a command prompt. Msmdarch uses .cab storage algorithms, which limits the size of any single .cab file to 2 GB. As a result, no individual file in the Data folder (such as any single partition) can exceed 2 GB, or else msmdarch cannot be used for backup.
When using msmdarch, always specify a log file location to capture any messages generated during the archive process. If the archive process fails, these messages can help you determine why the archive process failed. However, msmdarch does not back up the query log. To back up the query log, perform a file-based backup of the SMDQLOG.mdb file. If you do not, a new query log is created from scratch when you start a restored instance.

Copying Files
You can use Analysis Manager to copy the meta data for an Analysis Services database from one instance of Analysis Services to another Analysis Services instance, provided that both instances are registered in Analysis Manager. Because only the meta data is copied to the target server using this method for release management, you will need to process the Analysis Services database on the destination server (after updating the data source properties, if necessary) before users can query the data in the new location. Because copying and pasting is so easy and quick, and because you are frequently only working with a subset of data in the development environment, copying and pasting is generally the quickest way to deploy an Analysis Services database on a different server. The downside is that it requires all of the dimensions, cubes, and partitions to be processed (which must be incurred anyway if the data sets are different). To determine your preferred approach, you need to compare the time and overhead of fully reprocessing the database with the msmdarch.exe archive and restore time. In most cases, full reprocessing is the fastest method. But you will find that it varies based on the underlying infrastructure.

If you cannot use msmdarch, you can use a file copy program, such as Windows Backup, to back up all the files in the Data folder. With a file copy backup, you back up all databases on the server. With msmdarch, you can back up a single Analysis Services database. In addition, the file copy technique does not back up the repository or the query log file. If you use the file copy technique, you must back up the repository at the same
time you back up the Data folder, to ensure that the repository and the data in the Data folder remain synchronized. You must also back up the query log (the SMDQLOG.mdb file), or begin capturing query information from scratch. While it is possible to back up just an individual database (by copying the contents of its Data sub folder and the .DBO file). You cannot back up individual portions of the repository. The repository is needed on a full restore, or if the meta data has changed since the last backup.


File-Based Backup and Restore
If neither of the previous two methods is suitable for your situation, you can also use a file-based backup program to back up the entire Data folder and then restore it to the destination folder. With this method, you must deploy all databases within an Analysis Services instance, rather than a single database. If you use this method, you must also back up the repository and then restore the repository on the destination server. While the repository is technically not required to run the OLAP service, it is required to run Analysis Manager and thus to properly administer the server. The meta data in the repository must match the contents and structure of the Data folder. If you have data in remote partitions (a feature that is rarely used) and in writeback tables, you must first back up the data in remote partitions using a file-based backup method and the writeback tables using SQL Server backup, and then restore them before you bring your database back online.

Implementing a Continuous Analysis Services Solution

If your availability plan requires that users be able to query the cubes and dimensions on a continuous, 24-houra-day basis, there are a number of challenges that you must overcome. These include the following:
  • The repository If you use multiple servers to assure availability, you must ensure that the repository on each server remains synchronized with the Data folder, which you are also synchronizing. While the repository is not required for query processing, it is required when any structural change is made to the Analysis Services cubes and dimensions. If users are querying a copy of the Data folder on a secondary Analysis Services instance, you must make the change in the original Analysis Services instance and then update the secondary instance (using the file copy technique or msmdarch).
  • Writeback If you enable your cubes or dimensions for writeback, they can only write back to a single location (such as a SQL Server table). This creates a single point of failure (and possibly a performance bottleneck).
  • Processing Dimension processing might force cubes offline when structural changes have been made to nonchanging dimensions. When implementing a continuous Analysis Services solution, Microsoft offers two technologies to help you achieve this goal: Microsoft Cluster Services and Network Load Balancing.Memory Consumption by Connections Analysis Services allocates approximately 32 kilobytes (KB) for each client connection. By default, up to 10% of memory on the Analysis server can be allocated to each agent cache. Because more than one of these caches can be allocated at the same time (to service multiple clients issuing remote queries), reduce this value when many remote queries are being evaluated to reserve memory for the query results cache.
  • Disk The Data folder stores the data for all databases in an Analysis Services instance. The Temporary folder (or folders) stores the temporary files used during processing, if any. The usage of disk space in the Data folder will assist you in determining when partitioning of a cube will be useful. The usage of disk space in the Temporary folder will indicate that memory resources are in short supply.
  • Data Folder Within the Data folder, Analysis Services creates a separate subfolder for each Analysis Services database. Within each database folder, Analysis Services creates a separate subfolder for each cube within that database. While there are a number of different files created in the subfolder for each cube, there are two file types that you should particularly monitor:
  • Partition files Each partition file has an extension of fact.data. When a partition file exceeds 5 GB or more than 20 million records, you should begin considering the benefits of dividing the partition up into multiple partitions. While these are two general rules of thumb and may vary with circumstances, clearly smaller partitions can be processed faster than larger partitions. Also, with partitions, you frequently do not have to process all partitions in the cube in response to data change. In addition, smaller partitions can be queried quicker because, if the data slice is set properly, Analysis Services needs to scan less data to resolve many queries.
  • Aggregation Files The files containing rigid aggregations have an extension of agg.rigid.data. The files containing flexible aggregations have an extension of agg.flex.data. As these files get larger, the time required to process aggregations becomes longer. If you monitor the size of these files over time, you can see trends as they develop.

Problem and Incident Management for SQL Server

Problem and incident management with SQL Server is similar to problem and incident management with other server applications or the Windows operating system infrastructure itself. The SQL Server Logs (which should always be enabled), Analysis Services process log file , the DTS error and execution logs, and the Windows application log should be reviewed on a regular basis to attempt to detect potential problems before they become bigger problems. These same logs should be reviewed after incidents to attempt to associate events with incidents and identify patterns that lead up to the failure. The resolution of problems should be documented to help resolve future incidents and also used to train personnel in troubleshooting and understanding symptoms.

How to Tune the Process Buffer Size for SQL Server Analysis Server

Perform the following steps to tune the process buffer size on an Analysis server:
If you have 4 gigabytes (GB) or more of physical memory on the computer, you are running Microsoft Windows Advanced Server or Windows Datacenter Server, and either large dimensions or large process buffers are causing memory concerns, then enable the /3GB switch for the operating system and enable Analysis Services to use this additional memory.
Set Performance Monitor to collect the Temp file bytes written/sec counter for the Analysis Services:Proc Aggs object. By using Analysis Manager, configure the Analysis server properties to assign the Temporary file folder (on the General tab of the Server Properties dialog box) to an unused physical drive, and configure the process buffer size (on the Processing tab) to a minimal value, such as 32 megabytes (MB).
Restart Analysis Services and then use Performance Monitor or Windows Task Manager to determine what the virtual memory usage stabilizes at for the Analysis Services process (msmdsrv.exe).
Process the cube or partitions under consideration and observe the Temp file bytes written/sec counter you added to Performance Monitor. Once the aggregation calculation phase starts, you will start to see I/O to the Temporary files.
Gradually increase the process buffer size and re-process (restarting the Analysis Services service each time) until the Temp file bytes written/sec counter shows that the Temporary file is not being used. Then increase the number by 10 percent. If the virtual memory allocation for the Analysis Services service exceeds the HighMemoryLimit threshold, increase that value as well.
Repeat these steps for any large partitions (or groups of partitions) to determine the best system-wide process buffer size.

Check list for SQL Server Analysis Services Operations

Following is a set of checklist for operations in Microsoft Analysis Services. It would help to go efficiently without unexpected problems
  • Create a run book for each server.
  • Document all Analysis Services objects.
  • Check that Analysis Services has not been installed on a domain controller.
  • If you need to configure process affinity, use Windows System Resource Manager (WSRM) in Windows Server 2003.
  • Add a second paging file equal to the amount of physical memory in your computer.
  • Use the 64-bit versions of Analysis Services and Windows Server 2003 to enable Analysis Services to address more than 3 gigabytes (GB) of memory in the main process space.
  • Use the /3 GB switch with the 32-bit version of Analysis Manager, if supported by the operating system. Disable unnecessary services, in particular the Indexing service.
  • Disable virus scanning for the Analysis Services Data and Temporary folders.
  • If you add memory or enable the /3 GB switch in boot.ini, increase the Memory Conservation Threshold and Minimum allocated memory settings in Analysis Manager.
  • Disable the VLDM threshold if you have sufficient memory in the main process space.
  • If you must use VLDM, consider going to the 64-bit version of Analysis Services instead.
  • If you have sufficient memory, increase the Process Buffer Size setting to at least 150 or 200 megabytes (MB) to eliminate the use of temporary files during processing.
  • Use a RAID array for the Data folder, and allow double the space required for the data, index, and aggregation structures. This allows sufficient space during processing and for refreshing the data.
  • Use a RAID array for the Temporary folder if temporary files must be used during processing.
  • Alternatively, consider adding a second Temporary folder on a different hard disk.
  • Configure a logical name for the data source object in Analysis Manager.
  • Configure a domain user account for the MSSQLServerOLAPService service and add this account to the local OLAP Administrators group. Ensure that this account has sufficient access rights to the data source.
  • Configure security account impersonation and delegation using Kerberos, if client security credentials must be passed through a middle-tier application.
  • To use the SQL Server Agent service to automate Analysis Services tasks, add the service account used by the SQL Server Agent service to the local OLAP Administrators group.
  • Migrate the Analysis Services repository to a dedicated database in SQL Server using a case insensitive collation. This increases scalability, support, and security. Do not use the default msdb database.
  • Enable a system-wide processing log file to enable troubleshooting and analysis.
  • Use (or increase the use of) partitions to increase query and processing performance if your partitions exceed 5 GB or more than 20 million records.
  • Partitioning requires that you use Enterprise Edition of SQL Srever
  • Ensure that each partition has a data slice defined for the partition, to increase query performance.
  • Designing too many aggregations will slow processing; too few aggregations will slow querying.Ensure that all partitions have a minimum number of aggregations - perhaps 10%.
  • Use the Optimize Schema command to eliminate unnecessary joins.
  • Verify that each computer running Analysis Services and each client computer accessing Analysis Services data or metadata has the latest service pack or appropriate hot fix.
  • To deploy an Analysis Services database, use msmdarch.exe to archive and then restore the Analysis Services database, provided that no single file is larger than 2 GB. Otherwise copy and paste, use a file-based copy program, or use a third-party utility
  • Use scripts and SSIS packages where possible to effect change for repeatability and to facilitate the use of source code control. Do not use an interactive tool unless absolutely necessary.
  • Administrators must be members of the OLAP Administrators group on the Analysis Services computer, and must be able to perform any task within Analysis Services, regardless of any other role restrictions.
  • If you have many different security roles for end users, use cell-level security rather than dimension level security, to reserve memory for processing and querying. For even more control, use application-level security.
  • Use a common (same or trusted) domain structure between clients and Analysis Services.
  • Determine the level of availability required from your Analysis Services installation, and then determine how to provide that level of availability.
  • Create a mechanism to detect when Analysis Services stops running and is no longer available.
  • Perform regular backups, using either msmdarch.exe or a file-based backup method. Ensure that your backup schedule is complete, ongoing, and regularly validated.
  • Use a test or QA server to practice restorations to prepare for an emergency.
  • For continuous availability, consider deploying an NLB cluster rather than an MSCS cluster.
  • Monitor memory consumption changes over time to detect and respond to memory capacity constraints.
  • Monitor disk space changes over time, including the use of temporary files, to detect and respond to disk and memory capacity constraints.
  • Monitor processor usage changes over time to detect querying and processing bottlenecks as they appear.Use traditional problem and incident management techniques to resolve problems quickly, and then use the information learned to prevent future problems and to train staff.

Monday, May 11, 2009

Invalid text, ntext, or image pointer type. Must be binary(16).

The error message
Msg 7122, Level 16, State 1, Line 5
Invalid text, ntext, or image pointer type. Must be binary(16).
is generated at run time when you try to use a variable of an invalid type as pointer to a blob column. To solve the problem make sure that the variable that points to the blob column must be of the type BINARY(16).

Thursday, May 7, 2009

Get info about current current connection user to SQL Server

To get info about current connection user to SQL Server User use following T-SQL command.
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
GO


I got following information on my server

Get name of sql server service through T-SQL

To get name of SQL Srever Service through T-SQL use following T-SQL command
SELECT @@SERVICENAME AS [SQL SERVER SERVICE]
GO


You can run this T-SQL command in any database, result will be same. I got following result

Get Name of SQL Server instance through T-SQL

To get name of SQL Srever instance through T-SQL ue following T-SQL command
SELECT @@SERVERNAME AS [My SERVER Instance Name]
GO


You can run it any database, result will be same.
Following result is produced for me

Using sys.dm_exec_requests to monitor slow response of SQL Server

It is very important for a DBA to monitor the response time of SQL Server to connected users. Minute details like read, write and resource consumption may be analyzed later but first priority should be to point out slow response time. Several DMVs and other system data may be utilized to monitor the slow response of SQL Server. For this post I have used sys.dm_exec_requests DMV as follows

USE master
GO

SELECT start_Time,DB_NAME(database_id),st.TEXT,
[Status],
command, wait_type ,wait_time,
wait_resource,
last_wait_type,
CASE transaction_isolation_level
WHEN 0 THEN 'unspecified'
WHEN 1 THEN 'ReadUncomitted'

WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS [TRANSACTION ISOLATION],executing_managed_code
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY wait_type
GO


The field [wait_type] is important if some wait type is given for any transaction. And it become critical if wait type is due to any lock. Such wait types will begin with LCK prefix. T-SQL statement being used is also given, so DBA may get a clue that which statement is experiencing slow response.
Above script just provides a clue. If you note any problem then further fields from sys.dm_exec_requests may be included and other ways to track down the problem may be used.
For details of fields in sys.dm_exec_requests please click here
As a DMV is being used in above script so it is obvious that above script will be used for SQL Server 2005 and later.

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

The error message
Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
is a run time error message of severity level 15. This error message appears when you try to call any view, function, derived table or subquery without also specifying TOP or FOR XML. To resolve the error message add the TOP or FOR XML clause to the statement