Uncategorized

Cluster or mirror?

Should I cluster or mirror?  A few short months ago, the answer to that question would have been easy, now with the increasing popularity of database mirroring to achieve redundancy and geographic replication the answer is split…while each solution provides its own unique benefits, those benefits can be distributed across the answer to what are trying to achieve?


Database mirroring replays transaction log records on a standby server and as a result can failover more rapidly in most cases than a traditional SQL cluster with no loss of committed data; however, as a relatively new solution available to SharePoint Products and Technologies database mirroring has the highest operational costs when considering the learning curve required for the server support staff and database administrators.  While a database mirroring partnership can failover automatically through the implementation of a witness server (high availability/synchronous), there are no methods to enable automatic failover of SharePoint Products and Technologies introducing the added cost of developing a mechanism to manage the failover of the SharePoint service in the event the principal server is lost.  In many cases additional hardware will be required to support the witness server role in a mirroring partnership.  Also inherent of database mirroring is the requirement to duplicate storage across the principal and mirror servers; SharePoint Products and Technologies will consume only from one single physical server at a time unless databases are mirrored in a bidirectional manner.  To offset storage costs database mirroring can leverage traditional DAS storage and/or consume its storage from a SAN.


Clustering is the most common database architecture and as such is the least expensive in operational overhead.  Clustering enables running the same application on two or more servers providing a high availability solution if one of the servers fails.  Cluster software (Cluster Service) natively handles the failover process enabling SharePoint Products and Technologies to run uninterrupted on the second server.  Clustering when compared to mirroring offers greater processing power in addition to scalability since it does not carry the same two (2) physical server limitation as database mirroring and shares resources across each member node, reducing storage costs when compared to database mirroring; however, clustering comes with the requirement of shared storage and does not support the DAS storage options of database mirroring.  Geographic redundancy is limited with clustering since technology limits the distance by which two (2) servers can be separated, Fibre Channel for instance provides the greatest overall distancing, but is limited to a few miles.


Database mirroring and clustering both are capable of data replication, commonly through backing up the transaction logs from a database to a different server and applied there on a standby database (log shipping).  Before you consider log shipping in either a mirroring or clustered database architecture you should carefully review the latency between the publishing and subscribing servers.


There are many other considerations to understand prior to implementing either solution as part of your database architecture for SharePoint Products and Technologies that are not mentioned here that should be carefully reviewed and are complimented by an appropriate implementation plan.


In Microsoft IT we have a mixture, though unbalanced, distribution of SQL server clusters and mirror partnerships and by slowly introducing database mirroring in limited locations as one more option in our SharePoint Products and Technologies database architecture we allow allow more efficient growth and sustainability by not overburdening our operations staff with the support and maintenance learning curve and allow them to continue focusing on current deployments while gradually evolving our database mirroring architecture.


For additional information on SQL Server Database Mirroring with SharePoint Products and Technologies, see my three part series:


SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007 – Part 1 (Introduction, Overview, and basics)


SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007 – Part 2 (Configuration)


SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007 – Part 3 (Failover)


Additional Resources


Database Mirroring in SQL Server 2005


Inside Microsoft.com – Getting Started with Database Mirroring


Database Mirroring Best Practices and Performance Considerations


SQL Server 2005 Failover Clustering White Paper


Top Tips for SQL Server Clustering

Standard
Uncategorized

Stress Testing Microsoft Office SharePoint Server 2007/Windows SharePoint Services 3.0

TechReady5 concluded on Friday and I’m finally returning to work after several customer sessions that immediately followed and one question was shared between the two – what do you recommend or what are you using to monitor performance and how do you determine load and stress when architecting a SharePoint Products and Technologies infrastructure?  The answer is, there are a variety of tools to stress test your Microsoft SharePoint Products and Technologies deployment; let’s cover some them:


SPSiteBuilder

SPSiteBuilder was originally a component of the SharePoint Utility Suite. As many of you know there are no future plans to redevelop this application set and SPSiteBuilder packaged with the SharePoint Utility Suite won’t work with Microsoft Office SharePoint Server 2007/Windows SharePoint Services 3.0. The good news is that making the application compatible with Microsoft Office SharePoint Server 2007/Windows SharePoint Services 3.0 does not require a great deal of effort or a large investment of time.

Let’s step through fixing the code to support Microsoft Office SharePoint Server 2007/Windows SharePoint Services 3.0:

First locate the line:


globalAdmin = new SPGlobalAdmin();
virtualServer = globalAdmin.OpenVirtualServer(uri);


SPGlobalAdmin is maintained for backward comparability; replace the globalAdmin variable with Farm and the SPGlobalAdmin() method with new SPFarm().


Farm = new SPFarm();


Now we need to replace the OpenVirtualServer() method since it is obsolete and use the Lookup() method of the WebApplication object.


WebApp = WebApplication.Lookup(uri);


Now locate the line:


if (!virtualServer.Config.Prefixes.Contains(strPrefix))
virtualServer.Config.Prefixes.Add(strPrefix, Microsoft.SharePoint.Administration.SPPrefixType.WildcardInclusion);


This enumeration member is obsolete in Windows SharePoint Services 3.0 because it is no longer necessary to tell Windows SharePoint Services which URL paths should not be handled by Windows SharePoint Services. So we should replace it with the WebApp variable we specified earlier.




if (!webApp.Prefixes.Contains(strPrefix))
webApp.Prefixes.Add(strPrefix, Microsoft.SharePoint.Administration.SPPrefixType.WildcardInclusion);
}



Finally to complete the retrofit we need to replace all instances of globalAdmin with Farm and all instances of virtualServer with WebApp and ensure we are referencing Microsoft.SharePoint.dll from a Windows SharePoint Services 3.0 build and recompile.


MOSSDW.EXE


MOSSDW.EXE is a performance testing tool that provides data population/stress testing capabilities for Microsoft Office SharePoint Server 2007.  If your plans include only data population, consider retrofitting SPSiteBuilder to support Microsoft Office SharePoint Server 2007/Windows SharePoint Services 3.0 as described above.


WSSDW.EXE


WSSDW.EXE is a performance testing tool that populates data for testing deployments of Windows SharePoint Services 3.0 (see also SPSiteBuilder and MOSSDW.EXE).


Excel Services Ocracoke Performance Testing Sample Scripts


Excel Services Ocracoke Performance Testing Sample Scripts are performance testing scripts used in conjunction with MOSSDW.EXE that provide data population/stress testing capabilities for Excel Services.


Internet Information Services (ISS) 6.0 Resource Kit Tools


The IIS 6.0 Resource Kit Tools can help you administer, secure, and manage IIS; using the Web Capacity Analysis Tool Version 5.2 you can stress test your application.


IIS Diagnostic Toolkit


The IIS Diagnostic Toolkit is a compiled set of tools aimed at reducing the overall time to resolve problems with Internet Information Services (IIS) products.  Use Debug Diagnostics 1.0 to run diagnostic tests for web servers hosting SharePoint Products and Technologies.


Web Application Stress Tool


The Web Application Stress Tool is designed to realistically simulate multiple browsers requesting pages from a web site. You can use this tool to gather performance and stability information about your web application. This tool simulates a large number of requests with a relatively small number of client machines.


SQLIO.EXE Disk Subsystem Benchmark Tool

The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server.  SQLIO.EXE can be used to determine the I/O capacity of a given configuration.  An example of what we may see on a typical day during peak usage on a x64 A/P cluster hosting 100+ databases and supports 8 unique server farms, each with a local Shared Service Provider is:


IOs/sec:  7808.43

MBs/sec:    15.25

sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
parameter file used: param.txt
file c:testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 360 secs to file c:testfile.dat
using 8KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 100 MB for file: c:testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 3434.19
MBs/sec: 26.82
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 4
Max_Latency(ms): 282


SQLIOsim

SQLIOsim simulates the I/O patterns of Microsoft SQL Server 2005, of SQL Server 2000, and of SQL Server 7.0. The I/O patterns of these versions of SQL Server resemble one another.  You can use SQLIOsim to simulate read, write, checkpoint, backup, sort, and read-ahead activities for Microsoft SQL Server 2005  (see also SQLIO.EXE Disk Subsystem Benchmark Tool).

Performance/System Monitor

This is a standard component of the Windows operating system. You can use it to monitor performance objects and counters and instances of various hardware and software components.

Joel Oleson has a good list of counters for measuring Web front-end and backend performance – http://blogs.msdn.com/joelo/archive/2007/01/16/good-list-of-performance-counters.aspx.

NOTE At minimum consider monitoring PhysicalDiskDisk Transfers per second or LogicalDiskDisk Transfers per second measuring the amount of IO reads and writes per second to the database drives (see illustration).


Microsoft Operations Manager (MOM)


You can install MOM agents on individual servers that collect data and send it a centralized MOM server. The data is stored in the MOM database, which can be a dedicated SQL Server or the Microsoft SQL Server 2000 Desktop Engine (MSDE) version of Microsoft SQL Server. MOM is suitable for collecting large amounts of data over a long period of time.   MOM packs to consider for a typical SharePoint Products and Technologies deployment include:



Microsoft Operations Manager Packs


Microsoft Windows SharePoint Services
Microsoft SharePoint Portal Server 2003
Microsoft SQL Server 2000 & 2005
Microsoft Internet Information Server 6
Microsoft Cluster Service
Microsoft Windows 2003 Server


Web Site Monitoring


Web Sites and Services MP


Management Pack Catalog – http://www.microsoft.com/technet/prodtechnol/mom/catalog/catalog.aspx?vs=2005.


Stress tools such as Application Center Test (ACT)


You can use tools such as ACT to simulate clients and collect data during the duration of a test.


Network Monitor (NetMon)


You use NetMon to monitor the network traffic. You can use it to capture packets sent between client and server computers. It provides valuable timing information as well as packet size, network utilization, addressing, and routing information and many other statistics that you can use to analyze system performance.


SQL Profiler


Identify slow and inefficient queries, deadlocks, timeouts, recompilations, errors, and exceptions for any database interactions.  For example, one measure we implement is identifying queries exceeding two (2) seconds excluding search.  Profiling can be used when reports of latency are received in conjunction with monitoring the web front-end components of the server farm.


SQL Query Analyzer


This tool is also installed with SQL Server. You can use it to analyze the execution plans for SQL queries and stored procedures. This is mostly used in conjunction with the SQL Profiler.


SQLDiag


Collects valuable information about the configuration of the computer running SQL Server, the operating system, and the information that is reported to the SQL Server error logs.


 


SharePoint Products and Technologies Performance and Capacity Planning Resources


Plan for availability (Windows SharePoint Services)


Plan for availability (Microsoft Office SharePoint Server 2007)


Plan for performance and capacity (Microsoft Office SharePoint Server 2007)


Determining the hardware requirements for a single farm (Microsoft Office SharePoint Server 2007)


Performance and capacity planning (Windows SharePoint Services)


Performance and capacity planning factors (Microsoft Office SharePoint Server 2007)


Estimate performance and capacity requirements for Windows SharePoint Services collaboration environments (Microsoft Office SharePoint Server 2007)


Microsoft Office SharePoint Server 2007 on HP ProLiant Servers – Performance Summary


White Paper: Intel Performance Testing of Windows SharePoint Services

Standard
Uncategorized

SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007 – Part 3 (Failover)

Database mirroring is increasing in popularity and becoming an integral part of high availability and disaster recovery solutions within the SharePoint Products and Technologies arena. I’ve spent much of the last few months building labs, testing scenarios, impacts on platforms whether it be WSS or MOSS.  This post is the third and final on my series [SQL Server 2005 Database Mirroring and Windows SharePoint Services/Microsoft Office SharePoint Server 2007].

We know with asynchronous mirroring we can automatically manage SQL Server 2005 failover by introducing the witness role in our server farms, the most challenging question to answer is how to manage web front-end failover.  In this post I will outline several possible solutions for managing SharePoint Products and Technologies failover in a mirrored database architecture.

Solution # 1 Network Load Balancing

In a high-availability environment, you can create a NLB cluster to easily route client requests from the original principal server to a promoted mirror server without having to update each client directly. NLB setup and configuration are fairly painless; however, it comes with the requirement that each server be on the same subnet and the active mirror remain in a suspended state in the load balancing rotation.  With this in mind you should carefully consider the limitations when using native NLB, for example you will not be able to geo-cluster in most circumstances. 

Solution # 2 Manual Failover

Manual failover requires the least in respect to infrastructure and configuration; however, has the highest operational costs and without proper management and monitoring can present the largest client impact in the event the of a failover. The steps required to instantiate a manual failover of SharePoint Products and Technologies are described in the first part of this series.

Solution #3 Custom Solutions (Example)

Custom solutions can be implemented monitoring the state of the principal and mirror servers redirecting the application calling the original principal server to the new principal server – typically implemented through a Windows service.

For example, using an aliasing scheme where SharePoint Products and Technologies accesses the SQL database server through an alternate name;  SharePoint Products and Technologies accessing the alternate name is redirected to the proper physical name of the principal server. Since SharePoint Products and Technologies is only aware of the alternate name and never accesses the physical name of either node it does not need to be failover aware.

From a logical perspective in the example solution proposed SharePoint Products and Technologies would make the request to the SQL alias, for example contoso, requests for contoso are intercepted and modified to reference the active principal node referenced in the web front-end server Registry as contoso1.  In a failover scenario, the Windows service would detect the principal role is on the new principal server, previously the mirror server, and replace the web front-end server Registry entry for contoso1 with the new principal physical server contoso2.

Standard
Uncategorized

Requests Per Second Required for SharePoint Products and Technologies

One of the most common questions making its way to my Inbox as of recently is how to determine the required requests per second (RPS) to support a SharePoint Products and Technologies deployment.  While many IT Pros opt to use the recommend values associated with RPS and Internet Information Services (IIS) the transactions are considerably different between a light-weight .NET application or common IIS Web site.  To establish a general requirement for requests per second for a SharePoint Products and Technologies deployment you will need answers to the following questions:



  1. What is the total or anticipated number of users that will potentially access the server farm?  It is usually best to consider all seats in this figure, overestimating in this case is preferable to underestimating.

  2. What is the estimated percentage of users that would potential access the server farm concurrently on a given day?  Again, overestimating is preferable to underestimating.  Generally we would assume at least 35% of users would access the server farm concurrently on a given day, though, depending on the nature of the users, the purpose of the deployment and any competing technologies, e.g. file shares, this number can fluctuate significantly.

  3. What is the average number of common requests per user on a given day?  Common requests include basic operations such as updating an item in a List or Document Library.

  4. What is the anticipated ratio by which peak usage will exceed average usage on a given day?  As a general rule, your users will be more active at certain periods throughout a business day, such as early in the morning or later in the afternoon.  Considering you’ve profiled your users utilization of the server farm, by how much does peak usage exceed the average usage.  2x is generally a safe estimate for most deployments, but again may vary depending on the nature and overall purpose of the deployment.

  5. How many hours are in a business day?  A business day can generally be considered the operating hours of the business; however, for extranet deployments, centralized or regional deployments, a business day may stretch beyond 8:00 A.M. and 5:00 P.M.  Consider this when determine business hours.

Now that questions 1 through 5 have an answer associated with them, simple mathematics can be applied to determine the required requests per second to support your user base.


Step 1


Take the sum of 1 * 2 * 3 * 4


Step 2


Take the sum of 5 * 360000 where 360000 is the number of seconds per hour


Step 3


Divide the sum of 1 * 2 * 3 * 4 by the sum of 5 * 360000 to determine the required requests per second to support your user base.


For example, let’s assume Contoso has 95,000 total users, where 50% are assumed to access the server farm concurrently, each averaging 248 requests per day and peak usage is 2x the average usage.



95000 * 50% * 248 * 2 = 23560000


Using the result above anticipating Contoso will have users accessing the server farm 24 hours a day gives us the result of 8640000 seconds.


Now we divide 23560000 by 8640000 to determine the required requests per second to support the potential Contoso user base giving the result 2.726. 


Now we round 2.726 and move the decimal for a final result of 273 required requests per second.


Resources


Capacity Planning for Windows SharePoint Services


http://www.microsoft.com/resources/documentation/wss/2/all/adminguide/en-us/stsb07.mspx?mfr=true


Estimate Performance and Capacity Requirements for Search Environments


http://technet2.microsoft.com/Office/en-us/library/5465aa2b-aec3-4b87-bce0-8601ff20615e1033.mspx?mfr=true

Standard
Uncategorized

Site Collection Sizing Considerations

Site collection sizing is an important consideration in an overall capacity planning and governance solution.  This article details the considerations when planning for capacity and determining how site collections should be sized.

SharePoint Administration Tool (STSADM)

The SharePoint administration tool, STSADM is most commonly used by SharePoint Products and Technologies administrators to backup and restore site collections or perform a variety of administrative tasks. Typically as the site collection size grows, the ability to use STSADM to backup and restore the site collection diminishes. Performance issues, including unscheduled recycling of an application pool result in failure of the site collection backup/restore and are often the result of resource contention when a large amount of data is backed up; the specifications as provided with SharePoint Portal Server 2003 were 2GB (http://support.microsoft.com/kb/889236). Though much improved in Microsoft Office SharePoint Server 2007/Windows SharePoint Services 3.0, the opportunity of resource contention exists nonetheless and as a result STSADM should be considered a supplemental tool to your overall backup and recovery solution.

Site collections should be sized in a manner permitting manipulation of their content and host. A site collection whose size is compatible with the limitations of STSADM allows SharePoint Products and Technologies administrators to manipulate the site collection including its movement across content databases or even database servers/server farms.

Fewer & Larger Site Collections

Many businesses may have a need to have fewer site collections to provide a better overall view in respect to their environment where a site collection of 0-15GB may not be suitable to host the amount of necessary content. Large site collections are often selected to take advantage of aggregation and specific workflow capabilities. In these situations to permit simple recovery and management, it is often a best practice to isolate those site collections in their own content database. This allows the SharePoint Products and Technologies administrator(s) to easily recover the site collection and/or content. A consideration before making a decision on fewer larger site collections is the potential performance implications if the site collection will host a large amount of content, either at its root level or within residing webs. Typically performance will become progressively worsened as the number of total objects exceeds 2,000. As a site collection grows to a point to where performance suffers, a SharePoint Products and Technologies administrator can use the STSADM export and import operations to manipulate the fastest growing webs into their own site collection, thereby reducing the overhead associated with maintaining it side-by-side with other webs within a site collection. As with the site collection recommendation, this web should reside in a dedicated content database; however, the move should occur when the web is still within the parameters of the SharePoint administration tool (STSADM).

Another consideration of enabling large site collections is the ability to delete a site collection/web both through the web user interface and the SharePoint administration tool. The SharePoint Products and Technologies delete process is most often an end-user request resulting from submission of the request to the web front-end computer through the user interface. The request arrives at the SQL database server at which point the stored procedure Proc_DeleteSite is executed. Proc_DeleteSite execution results in a batch transaction consisting of many nested transactions dependent on the number of items in the ownership chain. The batch transaction is executed against the lowest level of ownership and works upward on row by row basis. The SQL database server will instantiate a ‘deleted’ table in memory to host the requests, in the event the memory allocation for the operation is exceeded, the SQL database server will use TempDB to commit the transaction. Each nested transaction within the batch transaction is confirmed and then committed against each item in the ownership chain. An item in this case refers to a document, list item, etc. In the event a nested transaction fails; the batch transaction is rolled back to the outermost begin transaction requiring the SQL database server to instantiate an ‘insert’ table in memory to host the requests; as with the ‘deleted’ table mentioned above, in the event the memory allocation for the operation is exceeded, the SQL database server will use TempDB to commit the transaction. The results can cause SQL blocking in the event a large number of items must be removed in the ownership chain prior to completing the request and occasionally jobs within the enclosing transaction may not be successfully rolled back. Large site collections hosting a significant number of documents and/or list items are particularly susceptible to this issue as a result of the number of transactions occurring on the SQL database server.

Data change rate can also impact a large site collection in which SQL log growth for the site collection when isolated to an individual content database should be closely monitored and maintained. SQL log growth should also be closely monitored for a content database hosting a large site collection in the event database mirroring and/or log shipping are selected as a disaster recovery option for the server farm; greater churn rates can significantly impact the performance of these technologies.

An additional consideration in maintaining a large site collection is the maintenance of permissions and subsequent inheritance.

In the event a large site collection is selected to host content and serve an aggregation performance, a number of search scopes may need to be defined to support providing relative search results to the site collection consumers. An aggregation portal is the most recommended implementation in this scenario to avoid the requirement to navigate several Windows SharePoint Services site collections to retrieve content based upon search results or alert notifications.

Smaller Site Collections

Where the number of site collections is not a concern; site collections are beneficial in that they offer true ownership, storage, and usage analysis reporting which can drive governance and manageability and in addition provide insight into what areas of a business are growing at varying paces. Site collections where growth is limited to a maximum of 15GB provide both ease of management and overall sustainability in terms of resources the ability to manipulate the site collection. Maintaining an environment with many site collections can be achieved through a proper governance plan, leveraging Site Directory taxonomy and the Windows SharePoint Services search service. In the situation where aggregation is desired, Microsoft Office SharePoint Server 2007 can be leveraging establishing an aggregation portal making the results from all site collections hosted on a Web application to be available to the portal through the Office Server search service and properly defined scopes.

Recommendations


  1. Establish a single aggregation portal based on the available SharePoint Products and Technologies Enterprise site templates.
  2. Establish search scopes relative to content sources and business units enabling efficient consumer queries and accurate result sets.
  3. Establish a maximum site collection quota template supportive of the SharePoint administration tool (STSADM); 15GB is the recommendation based upon performance and scale results.
  4. Limit site collection creation to a unique security group to provide oversight and management of the environments content. This allows a group of users to offer content approval prior to the introduction of a site collection to the server farm.
  5. Make site collection templates unavailable through self-service site creation to reduce the number of site collection templates and maintain consistency within the server farm.
  6. Enable usage and advanced usage analysis to gauge the growth of site collections; fast-growing webs may be candidates to become stand-alone site collections. This is easily accomplished when the web is within the limitation of the SharePoint administration tool (STSADM) through the export and import operations.
  7. Establish and maintain a Site Directory taxonomy that is relative to regional, organizational, and business unit aspects at a minimum to provide a concise overview of the environment and ease locating content by taxonomy assignment.
  8. Establish a life cycle management plan using site confirm and usage reporting and/or an out of the box solution. Life cycle management can be tuned according to individual growth, retention, and data management planning.
  9. Establish site collections as document repositories to host content for associated site collections.

Resources

SharePoint Governance and Manageability

Standard
Uncategorized

SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007 – Part 2 (Configuration)

This is the second part of a multi-part series on using SQL Server 2005 Database Mirroring with SharePoint Products and Technologies.  This post will cover the basic configuration parameters required to enable the mirroring of content, configuration, and search databases.  Part 3 of this series will cover SharePoint Products and Technologies failover automation scripts and considerations.


Basic SQL Server 2005 Database Mirroring Implementation for SharePoint Products and Technologies


The most common implementation of SQL Server 2005 Database Mirroring includes all databases being installed on a single mirror partnership and the implementation of a witness (polling) server that provides automatic database failover between the principal and mirror servers when necessary.  A witness server additionally provides quorum capabilities for a mirroring partnership; as a result in the event a principal server is lost, the witness will bring the mirror server online.


The witness server does not serve as a content host nor perform intensive operations and as a result can be safely implemented on an application server in a SharePoint server farm in most circumstances.


After installing and configuring SQL Server 2005 you should test the communication channels between each server machine by confirming DNS name resolution and ensuring the latency between each node is within an acceptable parameter.


Database Selection and Preparation


To begin a database mirroring session you should identify the databases that will be mirrored.  To maximize availability and reduce any potential issues during failover, it is recommended to mirror each database within a SharePoint Products and Technologies deployment.  In a database mirroring partnership SQL Server allocates memory to each database mirroring connection, to avoid potential performance implications you should consider a database capacity that can support your environment and additionally host content in a minimum count of content databases.  See Database Management Concepts for Large and Growing Content Databases for more information on capacity planning and design for content databases.



Set the Database Recovery Model to FULL on all Databases



On the SQL Server 2005 Principal server, click Start, All Programs, Microsoft SQL Server 2005, and then click SQL Server Management Studio.


Expand the Databases and System Databases node.


Right-click master and select New Query from the menu.


In the rightmost panel, enter the following SQL statement:


ALTER Database <databasename>
SET RECOVERY FULL;

Select Query, and then click Execute from the SQL Server Management Studio toolbar.

The results of the operation will be displayed in the rightmost Results panel.

Backup SharePoint Products and Technologies Databases

On the SQL Server 2005 Principal server, click Start, All Programs, Microsoft SQL Server 2005, and then click SQL Server Management Studio.


Expand the Databases and System Databases node.


Right-click master and select New Query from the menu.


In the rightmost panel, enter the following SQL statement:


BACKUP Database <databasename>
TO DISK = <‘backuppath’>
WITH Format
Go


Select Query, and then click Execute from the SQL Server Management Studio toolbar.

The results of the operation will be displayed in the rightmost Results panel.


Restore the SharePoint Products and Technologies Databases to the Mirror Server


On the SQL Server 2005 Mirror server, click Start, All Programs, Microsoft SQL Server 2005, and then click SQL Server Management Studio.

Expand the Databases and System Databases node.


Right-click master and select New Query from the menu.


In the rightmost panel, enter the following SQL statement:


RESTORE Database <databasename>
FROM DISK = <‘restorepath’>
WITH NORECOVERY
Go

Select Query, and then click Execute from the SQL Server Management Studio toolbar.

The results of the operation will be displayed in the rightmost Results panel.


Backup Transaction Logs on the Principal Server


On the SQL Server 2005 Principal server, click Start, All Programs, Microsoft SQL Server 2005, and then click SQL Server Management Studio.

Expand the Databases and System Databases node.


Right-click master and select New Query from the menu.


In the rightmost panel, enter the following SQL statement:


BACKUP LOG <databasename>
TO DISK = <‘backuppath’>
WITH Format
Go

Select Query, and then click Execute from the SQL Server Management Studio toolbar.

The results of the operation will be displayed in the rightmost Results panel.


Restore Transaction Logs to Mirror Server


On the SQL Server 2005 Mirror server, click Start, All Programs, Microsoft SQL Server 2005, and then click SQL Server Management Studio.

Expand the Databases and System Databases node.


Right-click master and select New Query from the menu.


In the rightmost panel, enter the following SQL statement:


RESTORE LOG <databasename>
FROM DISK = <‘restorepath’>
WITH FILE=1, NORECOVERY
Go

Select Query, and then click Execute from the SQL Server Management Studio toolbar.

The results of the operation will be displayed in the rightmost Results panel.


Configure Database Mirroring Connections




  1. On the Principal server click Start, All Programs, Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. Expand the Databases node.

  3. Right-click the database to mirrored and select Properties from the menu.

  4. On the Database Properties – <databasename> dialog, select Mirroring under the Select a page panel, and then click Configure Security.

  5. Click Next> on the Configure Database Mirroring Security Wizard or click Cancel to quit.

  6. On the Include Witness Server dialog, if a witness server will be configured (recommended), click Yes otherwise click No, and then click Yes to continue or < Back to return to the previous screen.

  7. On the Choose Servers to Configure dialog select Principal server instance, Mirror server instance, and Witness server instance, and then click Next > to continue.

  8. On the Principal Server Instance dialog select the Principal server instance from the menu, configure the Listener port to 14999 and specify EndPoint_Mirroring in the Endpoint name field, select Encrypt data sent through this endpoint, and then click Next > to continue or < Back to return to the previous screen.

  9. On the Mirror Server Instance dialog select the Mirror server instance from the menu and click Connect…

  10. Configure the Listener port to 14999 and specify EndPoint_Mirroring in the Endpoint name field, select Encrypt data sent through this endpoint, and then click Next > to continue or < Back to return to the previous screen.

  11. On the Service Accounts dialog review and confirm the settings, and then click Next > to continue or < Back to return to the previous screen.

  12. Click Finish to close the Configure Database Mirroring Security Wizard.

  13. On the Configuring Endpoints dialog ensure all actions are Successful; otherwise, return to the Configure Database Mirroring Security Wizard and resolve any conflicts or when all actions are Successful click Close.

  14. Confirm High safety with automatic failover (synchronous) – Always commit changes at both the principal and mirror is selected  under Operating mode and then select Start Mirroring to initialize database mirroring for the selected database.

  15. Click Refresh and confirm the Status is indicated as Synchronized: the databases are fully synchronized.  If there are any conflicts the Status will be commonly indicated as This database has not been configured for mirroring.

  16. Click OK to close the Configure Database Mirroring Security Wizard.

Repeat steps 1 – 16 for each database to be mirrored.


Post Implementation Review and Testing



  • To confirm the mirroring connection has been configured successfully confirm the principal server database(s) is indicated as Principal, Synchronized, and the mirror server database(s) is indicated as Mirror, Synchronized / Restoring.

  • To test failover restart the MSSQLSERVER service.

Resources


For additional information on SQL Server 2005 Database Mirroring with SharePoint Products and Technologies see SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007 – Part 1 (Introduction, Overview, and basics).

Standard
Uncategorized

Database Management Concepts for Large and Growing Content Databases

With the introduction of Windows SharePoint Services 3.0 and Microsoft Office SharePoint Server 2007 the desire to reduce the number of content databases supporting a Web application has become a growing requirement to leverage both technologies such as SQL Server 2005 Database Mirroring, log shipping, and reduce the operational requirements to manage a high-volume of content databases.


As a general rule, reducing the number of content databases will most often result in overall larger content databases hosting more site collections.  Before considering redesigning your database architecture or reconsidering your current design, it is important to understand the method by which a content database is created, hosted, and exists on the SQL database server. 


The instructions provided in this post detail the administration of content databases using the SharePoint 3.0 Central Administration user interface, SharePoint administration tool (STSADM), and SQL Server Management Studio (SQL Server 2005).  Transact-SQL statements are not provided and direct manipulation of any SharePoint Products & Technologies databases is not recommended.


Creating Content Databases


A content database can be created through the SharePoint 3.0 Central Administration user interface or optionally through the SharePoint administration tool (STSADM).  When a content database is created, the location of the data and log file is determined by the default database settings established on the SQL database server.  A content database is created with a PRIMARY filegroup hosting one (1) data file (.mdf) and one (1) transaction log (.ldf). 


A data file can have either a .mdf or .ndf extension and serves as the physical storage for all of the data on the disk.  Pages are read into the buffer cache when a data request is made for read/write purposes.  After the data has been written to in the buffer cache, it is then written to the data file.


The transaction log can have the .ldf extension and records the modifications that have occurred in the content database as well as the initiating transactions.  The information that was stored in memory within the log cache is committed to the transaction log.



Creating Content Databases using the SharePoint 3.0 Central Administration User Interface


To create a content database using the SharePoint 3.0 Central Administration user interface click Start, All Programs, Microsoft Office Server, and the click SharePoint 3.0 Central Administration from a web front-end computer.


Click the Application Management tab and select Content databases under SharePoint Web Application Management.


Select the Web application that the new content database will be assigned and click Add a content database.


From the Add Content Database user interface, specify the database name, authentication, search, and capacity settings and click OK to commit the changes.


Database capacity settings can be determined by deciding the maximum desired size of the content database / the maximum desired/permissible site quota template for the Web application.  So a maximum content database size of 450 GB / a maximum possible quota of 5 GB = 90 site collections per content database, the site warning level is typically set to a percentage of the maximum number of site collections, in this case the result would be 81  using 10% of 90 maximum site collections.


Creating Content Databases using the SharePoint Administration Tool


To create a content database using STSADM open a command prompt and change directories to %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.


From the command prompt run the following operation:


STSADM -o addcontentdb -url http://<webapplication&gt; -databasename <contentdatabasename> where -url specifies the Web application to which the content databases will be assigned and -databasename specifies the name of content database to be created.  If your database server is on a separate server you will need to use the -databaseserver parameter to specify the database server name.  Other parameters available to the addcontentdb operation include:


-databaseuser (specifies the username used to connect to the database server *where Windows authentication is not used)


-databasepassword (specifies the password associated with the username used to connect to the database server *where Windows authentication is not used)


-sitewarning (specifies the number of sites before a warning event is generated)


-sitemax (specifies the maximum number of site collections this content database can host)


When using STSADM to create a content database you will need to run the spsearch operation to associate a content database with a specific server that is running the Windows SharePoint Services search service. 


To associate a content database with a specific server that is running the Windows SharePoint Services search service open a command prompt and change directories to %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.


From the command prompt run the following operation:


STSADM -o spsearch -action attachcontentdatabase -databaseserver <contentdatabaseservername> -databasename <contentdatabasename> -searchserver <searchserver>


Removing Content Databases


By detaching the content database through SharePoint 3.0 Central Administration user interface or optionally through STSADM a content database can be made unavailable to SharePoint Products and Technologies for purposes of administration, maintenance, and/or archival.



Detaching a Content Database using the SharePoint 3.0 Central Administration User Interface


To detach a content database using the SharePoint 3.0 Central Administration user interface click Start, All Programs, Microsoft Office Server, and the click SharePoint 3.0 Central Administration from a web front-end computer.


Click the Application Management tab and select Content databases under SharePoint Web Application Management.


Select the Web application from which the content database will be removed.


Click the Database Name of the content database to be detached from the Web application.


Select the checkbox labeled Remove content database and click OK to commit the change.


Detaching a Content Database using the SharePoint Administration Tool


To detach a content database using STSADM open a command prompt and change directories to %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.


From the command prompt run the following operation:


STSADM -o deletecontentdb -url http://<webapplication&gt; -databasename <contentdatabasename> where -url specifies the Web application from which the content databases will be detached and -databasename specifies the name of content database to be detached. If your database server is on a separate server you will need to use the -databaseserver parameter to specify the database server name.


Following the completion of any maintenance operations where the content database was detached, you will need to use the SharePoint 3.0 Central Administration user interface or optionally, STSADM to reattach the content database and associate it with a specific server that is running the Windows SharePoint Services search service.  (See Moving and Renaming Content Databases below.)


NOTE When moving a content database from one server farm to another, the preparetomove operation should be run against each content database before it is detached from the server farm to prevent synchronization errors when introduced to the destination server farm.  For information about preparetomove parameters run STSADM -help preparetomove from %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.


Moving and Renaming Content Databases


A content database can optionally be moved across SQL database server where required to support storage limitations, capacity planning, access performance improvements on an alternate SQL database server, rename the database, or for general server maintenance operations. Moving a content database requires the content database be detached from the SQL database server and Windows SharePoint Services/Microsoft Office SharePoint Server 2007 before the content database can be moved. Moving a content database can include moving either the data file(s), transaction log(s), or all files simultaneously. When moving a content database in Microsoft Office SharePoint Server 2007, you should always run the STSADM operation, preparetomove to avoid synchronization errors when reattached to the Web application. (See Detaching a Content Database using the SharePoint Administration Tool)



Detaching a Content Database using the SharePoint Administration Tool


To detach a content database using STSADM open a command prompt and change directories to %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.


From the command prompt run the following operation:


STSADM -o deletecontentdb -url http:// -databasename where -url specifies the Web application from which the content databases will be detached and -databasename specifies the name of content database to be detached.
If your database server is on a separate server you will need to use the -databaseserver parameter to specify the database server name.


Detaching a Content Database (SQL Server 2005)


NOTE When detaching a content database from a SQL database server, it is recommended to first detach the content database from the Web application in Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007.



  1. Connect to the server instance in SQL Server 2005 Management and expand the Databases node.
  2. Right-click the content database to be detached, select Tasks, and then select Detach from the context menu.

NOTE Active connections must be closed before the content database can be attached; to avoid interrupting user requests or closing pending requests resulting in possible data loss, content databases should only be detached during a maintenance window or when users are not actively accessing its data.


To close active connections, select the Drop Connections checkbox from the Detach Database user interface and click OK to commit the changes.


Renaming a Content Database (SQL Server 2005)


NOTE To rename a content database it must first be detached from the Web application in Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007.



  1. Connect to the server instance in SQL Server 2005 Management and expand the Databases node.
  2. Right-click the content database and select Properties from the context menu.
  3. Click Options under Select a page in the Database Properties user interface.
  4. In the Restrict Access field, select SINGLE_USER, and then click OK to commit the changes.
  5. After the content database access has been set to SINGLE_USER, right-click the content database and select Rename from the context menu.
  6. Enter a new name for the content database and press Enter to commit the change.
  7. Right-click the content database and select Properties from the context menu.
  8. Click Options under Select a page in the Database Properties user interface.
  9. In the Restrict Access field, select MULTI_USER, and then click OK to commit the changes.

After the content database has been renamed it will need to be reattached to the Web application in Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007.  The data file(s) and transaction log(s) can optionally be renamed while the content database is detached from the SQL database server on the file system, for more information on renaming data file(s) and transaction log(s) see http://msdn2.microsoft.com/en-us/library/ms174269.aspx.


Reattaching Content Databases using the SharePoint 3.0 Central Administration User Interface


To attach a content database using the SharePoint 3.0 Central Administration user interface click Start, All Programs, Microsoft Office Server, and the click SharePoint 3.0 Central Administration from a web front-end computer.


Click the Application Management tab and select Content databases under SharePoint Web Application Management.


Select the Web application that the new content database will be assigned and click Add a content database.


From the Add Content Database user interface, specify the database name, authentication, search, and capacity settings and click OK to commit the changes.


Reattaching Content Databases using the SharePoint Administration Tool


To reattach a content database using STSADM open a command prompt and change directories to %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.


From the command prompt run the following operation:


STSADM -o addcontentdb -url http://<webapplication&gt; -databasename <contentdatabasename> where -url specifies the Web application to which the content databases will be assigned and -databasename specifies the name of content database to be assigned.  If your database server is on a separate server you will need to use the -databaseserver parameter to specify the database server name.  Other parameters available to the addcontentdb operation include:


-databaseuser (specifies the username used to connect to the database server *where Windows authentication is not used)


-databasepassword (specifies the password associated with the username used to connect to the database server *where Windows authentication is not used)


-sitewarning (specifies the number of sites before a warning event is generated)


-sitemax (specifies the maximum number of site collections this content database can host)


When using STSADM to reattach a content database you will need to run the spsearch operation to associate a content database with a specific server that is running the Windows SharePoint Services search service. 


To associate a content database with a specific server that is running the Windows SharePoint Services search service open a command prompt and change directories to %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.


From the command prompt run the following operation:


STSADM -o spsearch -action attachcontentdatabase -databaseserver <contentdatabaseservername> -databasename <contentdatabasename> -searchserver <searchserver>


Deleting Content Databases


To delete the content database and physical files associated with a content database you must delete the database from the SQL database server.



  1. Connect to the server instance in SQL Server 2005 Management and expand the Databases node.
  2. Right-click the content database and select Delete from the context menu.

NOTE Active connections must be closed before the content database can be deleted.  This operation will remove the data file(s) and transaction log(s) from the file system of the SQL database server.


To close active connections, select the Drop Connections checkbox from the Delete Database user interface and click OK to commit the changes.


Repartitioning Content Databases


As a content database grows it may become necessary to introduce additional data files to support the growth and sustain operation efficiency.  By introducing data files a content database can be grown to support additional content through the introduction of new site collections or by repartitioning existing content databases.


To repartition an existing content database into separate data files it is often easiest to begin with one existing content database that will serve as the primary data file in the PRIMARY filegroup. To reduce the overhead associated with repartitioning a content database, it is best to use the largest content database as the primary data file. After deciding on a content database to serve as the primary data file you can use the steps detailed in the section Performance and Benefits (Multiple Data Files) below to add a secondary file. Repartitioning a content database requires the backup, removal, and restore of existing site collections into a target content database. You should carefully review the size of existing content databases and the limitations associated with the STSADM backup and restore operations (see http://support.microsoft.com/kb/889236).  For additional information on the SharePoint administration tool, see Stsadm.exe command-line tool (Office SharePoint Server) at:  http://technet2.microsoft.com/Office/en-us/library/188f006d-aa66-4784-a65b-a31822aa13f71033.mspx?mfr=true



‘Stopping’ a Content Database to Repartition


Repartitioning a content database requires that the database to be repartitioned is set to ‘stopped’ in SharePoint 3.0 Central Administration to prevent the introduction of new site collections and set to read-only to prevent the introduction of content during the repartitioning process. To ensure the content database where the new data files have been established receives the site collections from the content database being repartitioned you will need to ‘stop’ all other content databases in the server farm to ensure site collections are not written to those databases or optionally set the maximum number of sites that can be hosted in the content database to a value exceeding that of any other content database in the server farm through the SharePoint Central Administration 3.0 user interface.



‘Stopping’ a Content Database using the SharePoint 3.0 Central Administration User Interface


To ‘stop’ a content database using the SharePoint 3.0 Central Administration user interface click Start, All Programs, Microsoft Office Server, and the click SharePoint 3.0 Central Administration from a web front-end computer.


Click the Application Management tab and select Content databases under SharePoint Web Application Management.


Select the Web application on which the content database will be ‘stopped’.


Click the Database Name of the content database to be ‘stopped’ on the Web application.


Select Offline from the Database status dropdown menu and click OK to commit the change.



Reducing Overhead when Repartitioning Content Databases


By growing the data files to a size supportive of the site collections to be introduced or to a maximum size as desired in your database architecture you can reduce the overhead associated with incremental growth of the data file during the repartitioning and the reduce the possibility of fragmentation. Before deciding on leveraging multiple data files you should read the remainder of this article to understand the concepts and instructions to maximize performance and administration operations.


Performance and Benefits (Multiple Data Files)


The use of multiple data files improve database performance by allowing a database to be created across multiple disks, controls or RAID systems. When a content database is “striped” across multiple disks more read/write heads can access the data in parallel simultaneously. For instance a 450GB content database made up of three (3) data and one (1) log file enables four (4) read/write heads to access data in parallel speeding up database operations.



Managing Growth


Content database administration can be reduced by allowing a content database to grow by a designated capacity, either incrementally or by a defined percentage. Proportional fill strategy is used across all of the data files within the filegroup composed of these data and log files, when the files in the file group are full, files will be automatically expanded one at a time in round-robin load balancing scheme.  Growth can be managed more granularly using manual file growth options, but as a result, requires the most administration overhead to monitor the growth.  Autogrowth enables files to grow incrementally or at defined percentages reducing the administration overhead associated with management of data file growth and can more granularly managed through establishing a maximum data file size.


General Rules


A file or filegroup cannot be used by more than one database, the content_database_1.mdf and content_database_2.ndf objects cannot be used by any other content database other than the content_database_1 database.


A file can be a member of only one filegroup (See above).

Transaction log files are never a part of a filegroup.

Best Practices


  1. Smaller content databases or those under 400-500 GB will work well with a single data file and a single transaction log file.
  2. Create data files on as many different available physical disks as possible.  This will improve performance, because of parallel disk I/O if the files are located on different physical disks.
  3. Do not put the transaction log file or files on the same physical disk that has the other files and filegroups.
  4. The number of files in a filegroup should be equal to or less than the number of CPU cores available to the SQL database server.
  5. Use manual file growth options.

Database Design Limitations


While there are benefits associated with leveraging files there are also limitations. Site collections and their associated content cannot span multiple data files, meaning that documents, lists, etc. will reside in the same data file as the site collection. This in effect limits exposing the performance gains associated with placing different tables that are used in the same join queries in different filegroups to leverage parallel disk I/O searching for joined data or putting heavily access tables (AllDocs) and the nonclustered indexes on different filegroups. 


SharePoint Products & Technologies do not support the use of multiple filegroups; therefore, SQL Server 2005 technologies including piecemeal restore cannot be leveraged on a content database.  When designing a content database it is important to determine a suitable maximum capacity that facilitates the ability to provide efficient restore and supports a restore server farm environment, specifically when determining storage requirements for a restore server farm environment.  Since SharePoint Products and Technologies limits a database to using on one filegroup, all data files within that filegroup must be restored to make available the content database composed of those data files.


Creating Data Files (SQL Server 2005)



New Content Databases


A newly created content database provides the best foundation for the introduction of files since it does not require the manipulation of a content database hosting production content.  To introduce files to a content database follow the steps detailed in the section Existing Content Databases below.


Existing Content Databases


The use of files is not limited to only new content databases, but can be applied to existing content databases in the server farm. By introducing new data files you can benefit from the performance and administration aspects of files in SQL Server 2005.



  1. Connect to the server instance in SQL Server 2005 Management and expand the Databases node.
  2. Select the content database that will be expanded through the introduction of a new files.
  3. Right-click the content database and select Properties from the context menu.
  4. From the Database Properties dialog, select Files.
  5. Two files will already exist, the primary data and log file that were created when the content databases was added to the server farm.
  6. Click Add to add a new file.
  7. Specify a logical name for the new file and select the secondary file group from Filegroup group column to associate the new file with the secondary filegroup.
  8. Expand the data file to the desired size by providing a value correspond to the desired size in megabytes in the Initial Size (MB) column.
  9. By default the growth is set to 1MB increments, this should be modified to reflect a value of 10% or greater to reduce the overhead associated with growth of the data file reducing the possibility of fragmentation.
  10. Specify the path where the new file will be created, to maximize the performance by enabling parallel disk I/O provide a location on a separate physical disk for this data file.
  11. Click OK to create the new file.

Conclusion


Large and growing content database can be extended through the introduction of secondary/additional data files to increase capacity, reduce administration overhead, and improve data access performance when implemented and managed appropriately, as a result larger content databases can host a significantly greater amount of site collections when compared to smaller content databases and subsequently maximize the exposure of a database that has gone suspect or offline to consumers of the service.  It is important to understand the design aspects and limitations of content databases and implement an efficient scale and sustainability strategy when considering redesigning your database architecture.  See the resources below for additional information on capacity planning for SharePoint Products and Technologies.


Resources


Scale, Performance, and Capacity Planning (Microsoft SharePoint Products and Technologies Team Blog)


Sample Data for Capacity Planning (Joel Oleson’s SharePoint Land)


Plan for Performance and Capacity (TechNet)


Planning and Architecture for Office SharePoint Server 2007 (TechNet)

Standard