My article related to Move SQL Server transaction log files to a different location via T-SQL and SSMS is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Move SQL Server transaction log files to a different location via T-SQL and SSMS
My article related to Copy Only Backup for SQL 2005 and SQL 2008 is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Copy Only Backup for SQL 2005 and SQL 2008
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.
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.
Controlling changes in SQL Server 2008 that require table re-creation (saving changes is not permitted)
My article related to Controlling changes in SQL Server 2008 that require table re-creation (saving changes is not permitted) is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Controlling changes in SQL Server 2008 that require table re-creation (saving changes is not permitted
My article related to Auto generate change scripts in SQL Server Management Studio (SSMS) for tables is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Auto generate change scripts in SQL Server Management Studio (SSMS) for tables