Migrating to Active Directory or SQL Server
You must decide whether you are migrating users to Active Directory or Microsoft SQL Server. The following factors should be considered:
Migration Performance and Scalability
Migration Attribute Compatibility
Migration Performance and Scalability
Active Directory
The Windows 2000 Server Active Directory is a robust, highly secure directory store. When planning for performance and scalability, note the following:
Active Directory is necessary to support deployment requiring access control of files that map to groups of users.
Active Directory is tuned as a domain controller with write transactions representing only a fraction of the total directory input/output.
Current testing indicates that at about one million (1,000,000) users in a domain the replication input/output between Active Directory instances in the domain begins to impact read/write activity for the profile. In addition, there is a practical limit of approximately five domains with one million users in each domain before the global catalog replication overhead begins to impact profile request input/output. These numbers are subject to hardware performance and tuning optimization.
The following recommendations are based on testing conducted using a typical e-commerce user profile:
One million users per Active Directory domain
Five Active Directory domains per forest of domains (this equates to a practical limit of five million accounts in Active Directory deployments).
For more information, see the latest performance and scalability documents at https://www.microsoft.com/commerceserver.
SQL Server
Commerce Server supports both Microsoft SQL Server 7.0 and SQL Server 2000. It is recommended that you use SQL Server 2000; it has improved performance and an enhanced set of features. When you plan for performance and scalability, note the following:
SQL Server has excellent scalability and stability characteristics. SQL Server is tuned for highly dynamic data supporting read/write balances approaching 50/50 with no significant performance degradation. This maps nicely to e-commerce sites with highly dynamic attributes being kept for users (for example, Favorites and last visit date/time).
No limits have been found in testing up to 10 million users in the store with a one percent concurrency rate.
Groups created in SQL Server do not have system ACL/ACE integration that would allow you to protect static content (for example, pictures and audio files).
Groups implemented under SQL are created as multi-value strings with a practical limit of less than 200 users per group.
Using Active Directory and SQL Server
For those sites that require both the access control provided by Active Directory and the performance of SQL Server, it is recommended that both Active Directory and SQL Server be used together to store user profile information. In this scenario, you would store data that is associated with user credentials and data that is very stable in an Active Directory data store. You would store the remaining attributes in SQL Server.
For example, you may want to divide the attributes as shown below:
- User profile attributes stored in Active Directory:
UserName
Password
- User profile attributes stored in SQL Server:
Credit Card
Home Address, City, State, Zip
Home Phone Number
Date/Time of last visit
Favorite Color
Last page visited
Year-to-date total spent on site
Migration Attribute Compatibility
Not every Site Server 3.0 Membership Directory attribute has an equivalent attribute in a Commerce Server Profiles data store.
The following table lists the types of attributes in a Membership Directory, the equivalent type of attributes in a Profiles data store, and then the types in Active Directory and SQL Server. These are the attributes that are supported by Commerce Server without any modifications.
Membership Directory Attribute Type | Commerce Server Profile System Equivalent Type(s) | Active Directory Underlying Data Type | SQL Server Underlying Data Type |
String | Text, Password, SiteTerm, Profile | String | String |
Integer | Numbers Only, Decimal
Currency |
Number | Number |
DateTime | Date/Time, Date, Time | DateTime | DateTime |
Distinguished Name (DN) | Text, Password, SiteTerm, Profile | String | String |
Binary | Binary | Binary | Binary |
Multi-value String | Multi-value (Text, Password, SiteTerm, Profile) | Multi-value String | Multi-value String |
Multi-value Integer | Multi-value NUMBER | Multi-value Integer | Not supported |
Multi-value DateTime | Multi-value Date/Time, Date, Tim | Multi-value DateTime | Not supported |
Multi-value DN | Multi-value (Text, Password, SiteTerm, Profile) | Multi-value String | Multi-value String |
Multi-value Binary | Multi-value Binary | Multi-value Binary | Not supported |
Caution
Use caution when enabling multi-value attributes in the profile. The underlying data type is not checked to verify whether it is flagged as a multi-value data type.
There are currently several data types that are supported in the profile but are not enabled at the data layer on either SQL Server or Active Directory stores. These include: Integer, Long Text, and Reference.