Ads

29 March 2011

Kerberos authentication

http://support.microsoft.com/kb/909801
OR

http://technet.microsoft.com/en-us/library/ee806870%28printer%29.aspx

You can use a command that is similar to the following to register an SPN for an instance:
SetSPN –A MSSQLSvc/.:1433
----------------------------------------------------------------------
How to make sure that you are using Kerberos authentication
After you connected to an instance of SQL Server 2005, run the following Transact-SQL statement in SQL Server Management Studio:

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

28 March 2011

Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory

http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_requirements


How accounts are created through wizards in failover clustering



The following diagram illustrates the use and creation of computer accounts (Active Directory objects) that are described in the previous subsection. These accounts come into play when an administrator runs the Create Cluster wizard and then runs the High Availability wizard (to configure a clustered service or application).


e8a7686c-9ba8-4ddf-87b1-175b7b51f65d

Note that the above diagram shows a single administrator running both the Create Cluster wizard and the High Availability wizard. However, this could be two different administrators using two different user accounts, if both accounts had sufficient permissions. The permissions are described in more detail in Requirements related to failover clusters, Active Directory domains, and accounts, later in this guide.






How problems can result if accounts needed by the cluster are changed



The following diagram illustrates how problems can result if the cluster name account (one of the accounts required by the cluster) is changed after it is automatically created by the Create Cluster wizard.


beecc4f7-049c-4945-8fad-2cceafd6a4a5

If the type of problem shown in the diagram occurs, a certain event (1193, 1194, 1206, or 1207) is logged in Event Viewer. For more information about these events, see http://go.microsoft.com/fwlink/?LinkId=118271.


Note that a similar problem with creating an account for a clustered service or application can occur if the domain-wide quota for creating computer objects (by default, 10) has been reached. If it has, it might be appropriate to consult with the domain administrator about increasing the quota, although this is a domain-wide setting and should be changed only after careful consideration, and only after confirming that the preceding diagram does not describe your situation. For more information, see Steps for troubleshooting problems caused by changes in cluster-related Active Directory accounts, later in this guide.







Requirements related to failover clusters, Active Directory domains, and accounts



As described in the preceding three sections, certain requirements must be met before clustered services and applications can be successfully configured on a failover cluster. The most basic requirements concern the location of cluster nodes (within a single domain) and the level of permissions of the account of the person who installs the cluster. If these requirements are met, the other accounts required by the cluster can be created automatically by the failover cluster wizards. The following list provides details about these basic requirements.




  • Nodes: All nodes must be in the same Active Directory domain. (The domain cannot be based on Windows NT 4.0, which does not include Active Directory.)



  • Account of the person who installs the cluster: The person who installs the cluster must use an account with the following characteristics:


    • The account must be a domain account. It does not have to be a domain administrator account. It can be a domain user account if it meets the other requirements in this list.


    • The account must have administrative permissions on the servers that will become cluster nodes. The simplest way to provide this is to create a domain user account, and then add that account to the local Administrators group on each of the servers that will become cluster nodes. For more information, see Steps for configuring the account for the person who installs the cluster, later in this guide.


    • The account (or the group that the account is a member of) must be given the Create Computer objects and Read All Properties permissions in the container that is used for computer accounts in the domain. Another alternative is to make the account a domain administrator account. For more information, see Steps for configuring the account for the person who installs the cluster, later in this guide.


    • If your organization chooses to prestage the cluster name account (a computer account with the same name as the cluster), the prestaged cluster name account must give “Full Control” permission to the account of the person who installs the cluster. For other important details about how to prestage the cluster name account, see Steps for prestaging the cluster name account, later in this guide.






Planning ahead for password resets and other account maintenance



The administrators of failover clusters might sometimes need to reset the password of the cluster name account. This action requires a specific permission, the Reset password permission. Therefore, it is a best practice to edit the permissions of the cluster name account (by using the Active Directory Users and Computers snap-in) to give the administrators of the cluster the Reset password permission for the cluster name account. For more information, see Steps for troubleshooting password problems with the cluster name account, later in this guide.







Steps for configuring the account for the person who installs the cluster



The account of the person who installs the cluster is important because it provides the basis from which a computer account is created for the cluster itself.


The minimum group membership required to complete the following procedure depends on whether you are creating the domain account and assigning it the required permissions in the domain, or whether you are only placing the account (created by someone else) into the local Administrators group on the servers that will be nodes in the failover cluster. If the former, membership in Account Operators or Domain Admins, or equivalent, is the minimum required to complete this procedure. If the latter, membership in the local Administrators group on the servers that will be nodes in the failover cluster, or equivalent, is all that is required. Review details about using the appropriate accounts and group memberships at http://go.microsoft.com/fwlink/?LinkId=83477.


To configure the account for the person who installs the cluster





  1. Create or obtain a domain account for the person who installs the cluster. This account can be a domain user account or a domain administrator account (in Domain Admins or an equivalent group).




  2. If the account that was created or obtained in step 1 is a domain user account, or the domain administrator accounts in your domain are not automatically included in the local Administrators group on computers in the domain, add the account to the local Administrators group on the servers that will be nodes in the failover cluster:




    1. Click Start, click Administrative Tools, and then click Server Manager.



    2. In the console tree, expand Configuration, expand Local Users and Groups, and then expand Groups.



    3. In the center pane, right-click Administrators, click Add to Group, and then click Add.



    4. Under Enter the object names to select, type the name of the user account that was created or obtained in step 1. If prompted, enter an account name and password with sufficient permissions for this action. Then click OK.



    5. Repeat these steps on each server that will be a node in the failover cluster.












    ImportantImportant

    These steps must be repeated on all servers that will be nodes in the cluster.








  3. If the account that was created or obtained in step 1 is a domain administrator account, skip the rest of this procedure. Otherwise, give the account the Create Computer objects and Read All Properties permissions in the container that is used for computer accounts in the domain:




    1. On a domain controller, click Start, click Administrative Tools, and then click Active Directory Users and Computers. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.



    2. On the View menu, make sure that Advanced Features is selected.


      When Advanced Features is selected, you can see the Security tab in the properties of accounts (objects) in Active Directory Users and Computers.



    3. Right-click the default Computers container or the default container in which computer accounts are created in your domain, and then click Properties. Computers is located in Active Directory Users and Computers/domain node/Computers.



    4. On the Security tab, click Advanced.



    5. Click Add, type the name of the account that was created or obtained in step 1, and then click OK.



    6. In the Permission Entry for container dialog box, locate the Create Computer objects and Read All Properties permissions, and make sure that the Allow check box is selected for each one.

      Create Computer objects permission









Steps for prestaging the cluster name account



It is usually simpler if you do not prestage the cluster name account, but instead allow the account to be created and configured automatically when you run the Create Cluster wizard. However, if it is necessary to prestage the cluster name account because of requirements in your organization, use the following procedure.


Membership in the Domain Admins group, or equivalent, is the minimum required to complete this procedure. Review details about using the appropriate accounts and group memberships at http://go.microsoft.com/fwlink/?LinkId=83477. Note that you can use the same account for this procedure as you will use when creating the cluster.


To prestage a cluster name account





  1. Make sure that you know the name that the cluster will have, and the name of the user account that will be used by the person who creates the cluster. (Note that you can use that account to perform this procedure.)




  2. On a domain controller, click Start, click Administrative Tools, and then click Active Directory Users and Computers. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.




  3. In the console tree, right-click Computers or the default container in which computer accounts are created in your domain. Computers is located in Active Directory Users and Computers/domain node/Computers.




  4. Click New and then click Computer.




  5. Type the name that will be used for the failover cluster, in other words, the cluster name that will be specified in the Create Cluster wizard, and then click OK.




  6. Right-click the account that you just created, and then click Disable Account. If prompted to confirm your choice, click Yes.


    The account must be disabled so that when the Create Cluster wizard is run, it can confirm that the account it will use for the cluster is not currently in use by an existing computer or cluster in the domain.




  7. On the View menu, make sure that Advanced Features is selected.


    When Advanced Features is selected, you can see the Security tab in the properties of accounts (objects) in Active Directory Users and Computers.




  8. Right-click the folder that you right-clicked in step 3 , and then click Properties.




  9. On the Security tab, click Advanced.




  10. Click Add, click Object Types and make sure that Computers is selected, and then click OK. Then, under Enter the object name to select, type the name of the computer account you just created, and then click OK. If a message appears, saying that you are about to add a disabled object, click OK.




  11. In the Permission Entry dialog box, locate the Create Computer objects and Read All Properties permissions, and make sure that the Allow check box is selected for each one.


    Create Computer objects permission


  12. Click OK until you have returned to the Active Directory Users and Computers snap-in.




  13. If you are using the same account to perform this procedure as will be used to create the cluster, skip the remaining steps. Otherwise, you must configure permissions so that the user account that will be used to create the cluster has full control of the computer account you just created:




    1. On the View menu, make sure that Advanced Features is selected.



    2. Right-click the computer account you just created, and then click Properties.



    3. On the Security tab, click Add. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.



    4. Use the Select Users, Computers, or Groups dialog box to specify the user account that will be used when creating the cluster. Then click OK.



    5. Make sure that the user account that you just added is selected, and then, next to Full Control, select the Allow check box.

      Properties box with Full Control permission









Steps for prestaging an account for a clustered service or application



It is usually simpler if you do not prestage the computer account for a clustered service or application, but instead allow the account to be created and configured automatically when you run the High Availability wizard. However, if it is necessary to prestage accounts because of requirements in your organization, use the following procedure.


Membership in the Account Operators group, or equivalent, is the minimum required to complete this procedure. Review details about using the appropriate accounts and group memberships at http://go.microsoft.com/fwlink/?LinkId=83477.


To prestage an account for a clustered service or application





  1. Make sure that you know the name of the cluster and the name that the clustered service or application will have.




  2. On a domain controller, click Start, click Administrative Tools, and then click Active Directory Users and Computers. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.




  3. In the console tree, right-click Computers or the default container in which computer accounts are created in your domain. Computers is located in Active Directory Users and Computers/domain node/Computers.




  4. Click New and then click Computer.




  5. Type the name that you will use for the clustered service or application, and then click OK.




  6. On the View menu, make sure that Advanced Features is selected.


    When Advanced Features is selected, you can see the Security tab in the properties of accounts (objects) in Active Directory Users and Computers.




  7. Right-click the computer account you just created, and then click Properties.




  8. On the Security tab, click Add.




  9. Click Object Types and make sure that Computers is selected, and then click OK. Then, under Enter the object name to select, type the cluster name account, and then click OK. If a message appears, saying that you are about to add a disabled object, click OK.




  10. Make sure that the cluster name account is selected, and then, next to Full Control, select the Allow check box.


    Properties box with Full Control permission







Steps for troubleshooting problems related to accounts used by the cluster



As described earlier in this guide, when you create a failover cluster and configure clustered services or applications, the failover cluster wizards create the necessary Active Directory accounts and give them the correct permissions. If a needed account is deleted, or necessary permissions are changed, problems can result. The following subsections provide steps for troubleshooting these issues.






Steps for troubleshooting password problems with the cluster name account



Use this procedure if there is an event message about computer objects or about the cluster identity that includes the following text. Note that this text will be within the event message, not at the beginning of the event message:



Logon failure: unknown user name or bad password.


Event messages that fit the previous description indicate that the password for the cluster name account and the corresponding password stored by the clustering software no longer match.


For information about ensuring that cluster administrators have the correct permissions to perform the following procedure as needed, see Planning ahead for password resets and other account maintenance, earlier in this guide.


Membership in the local Administrators group, or equivalent, is the minimum required to complete this procedure. In addition, your account must be given Reset password permission for the cluster name account (unless your account is a Domain Admins account or is the Creator Owner of the cluster name account). The account that was used by the person who installed the cluster can be used for this procedure. Review details about using the appropriate accounts and group memberships at http://go.microsoft.com/fwlink/?LinkId=83477.


To troubleshoot password problems with the cluster name account






  1. To open the failover cluster snap-in, click Start, click Administrative Tools, and then click Failover Cluster Management. (If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.)





  2. In the Failover Cluster Management snap-in, if the cluster you want to configure is not displayed, in the console tree, right-click Failover Cluster Management, click Manage a Cluster, and select or specify the cluster you want.




  3. In the center pane, expand Cluster Core Resources.




  4. Under Cluster Name, right-click the Name item, point to More Actions, and then click Repair Active Directory Object.









Steps for troubleshooting problems caused by changes in cluster-related Active Directory accounts



If the cluster name account is deleted or if permissions are taken away from it, problems will occur when you try to configure a new clustered service or application. To troubleshoot a problem where this might be the cause, use the Active Directory Users and Computers snap-in to view or change the cluster name account and other related accounts. For information about the events that are logged when this type of problem occurs (event 1193, 1194, 1206, or 1207), see http://go.microsoft.com/fwlink/?LinkId=118271.


Membership in the Domain Admins group, or equivalent, is the minimum required to complete this procedure. Review details about using the appropriate accounts and group memberships at http://go.microsoft.com/fwlink/?LinkId=83477.


To troubleshoot problems caused by changes in cluster-related Active Directory accounts





  1. On a domain controller, click Start, click Administrative Tools, and then click Active Directory Users and Computers. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.




  2. Expand the default Computers container or the folder in which the cluster name account (the computer account for the cluster) is located. Computers is located in Active Directory Users and Computers/domain node/Computers.




  3. Examine the icon for the cluster name account. It must not have a downward-pointing arrow on it, that is, the account must not be disabled. If it appears to be disabled, right-click it and look for the command Enable Account. If you see the command, click it.




  4. On the View menu, make sure that Advanced Features is selected.


    When Advanced Features is selected, you can see the Security tab in the properties of accounts (objects) in Active Directory Users and Computers.




  5. Right-click the default Computers container or the folder in which the cluster name account is located.




  6. Click Properties.




  7. On the Security tab, click Advanced.




  8. In the list of accounts with permissions, click the cluster name account, and then click Edit.











    noteNote

    If the cluster name account is not listed, click Add and add it to the list.








  9. For the cluster name account (also known as the cluster name object or CNO), ensure that Allow is selected for the Create Computer objects and Read All Properties permissions.


    Create Computer objects permission


  10. Click OK until you have returned to the Active Directory Users and Computers snap-in.




  11. Review domain policies (consulting with a domain administrator if applicable) related to the creation of computer accounts (objects). Ensure that the cluster name account can create a computer account each time you configure a clustered service or application. For example, if your domain administrator has configured settings that cause all new computer accounts to be created in a specialized container rather than the default Computers container, make sure that these settings allow the cluster name account to create new computer accounts in that container also.




  12. Expand the default Computers container or the container in which the computer account for one of the clustered services or applications is located.




  13. Right-click the computer account for one of the clustered services or applications, and then click Properties.




  14. On the Security tab, confirm that the cluster name account is listed among the accounts that have permissions, and select it. Confirm that the cluster name account has Full Control permission (the Allow check box is selected). If it does not, add the cluster name account to the list and give it Full Control permission.


    Properties box with Full Control permission


  15. Repeat steps 13-14 for each clustered service and application configured in the cluster.




  16. Check that the domain-wide quota for creating computer objects (by default, 10) has not been reached (consulting with a domain administrator if applicable). If the previous items in this procedure have all been reviewed and corrected, and if the quota has been reached, consider increasing the quota. To change the quota:




    1. Open a command prompt as an administrator and run ADSIEdit.msc.



    2. Right-click ADSI Edit, click Connect to, and then click OK. The Default naming context is added to the console tree.



    3. Double-click Default naming context, right-click the domain object underneath it, and then click Properties.



    4. Scroll to ms-DS-MachineAccountQuota, select it, click Edit, change the value, and then click OK.




































Community Content  
What is Community Content?









Add new content

RSS 
Annotations



















Processing



















© 2011 Microsoft. All rights reserved.
Terms of Use
|
Trademarks
|
Privacy Statement














Page view tracker


















How to change SQL Server parameters in a clustered environment when SQL Server is not online

http://support.microsoft.com/kb/953504

=====================================================================================

1. Click Start, click Run, type regedit, and then click OK.
2. Locate the quorum disk. To do this, follow these steps:
1. Locate the following registry key:
HKEY_LOCAL_MACHINE\Cluster\Quorum
2. The Path entry contains the path of the quorum disk. For example, the Path entry contains the following path:
:\MSCS
3. Locate the GUID of the SQL Server cluster resource. To do this, follow these steps:
1. Locate the following registry key:
HKEY_LOCAL_MACHINE\Cluster\Resources
2. Examine the Name column of the registry entries.

Note Several registry entries include "GUID" in the name of the entry.
3. For the default instance, locate the SQL Server cluster resource that includes "SQL Server" in the Name column.

For named instances, locate the SQL Server cluster resources that include "SQL Server ()" in the Name column.
4. Locate the checkpoint file name. To do this, follow these steps:
1. Locate the following registry key:
HKEY_LOCAL_MACHINE\Cluster\Resources\{GUID}\RegSync
2. In the details pane, view the checkpoint registry hives and the corresponding numbers that resemble the following:
For the default instance
00000004 SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLSERVER

For a named instance
00000004 SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLSERVER
Note For a named instance, X corresponds to the instance ID.

The number is the checkpoint file name. In this example, the checkpoint file name is 00000004.cpt.
5. In Registry Editor, click HKEY_LOCAL_MACHINE.
6. On the File menu, click Load Hive.
7. In the :\ folder, locate the checkpoint file that you found in step 4.
8. In the Key Name box, type 1, and then click OK.
9. Locate the following registry key to correct the invalid checkpoint registry key value:
HKEY_LOCAL_MACHINE\1\
Note The following examples correct the MSSQLSERVER checkpoint registry key:
* Example 1
To correct the invalid path of the Master.mdf file, follow these steps:
1. Locate the following registry key:
HKEY_LOCAL_MACHINE\1\Parameters
2. Correct the SQLArg0 key.
* Example 2
To disable the incorrectly enabled VIA protocol, follow these steps:
1. Locate the following registry key:
HKEY_LOCAL_MACHINE\1\SuperSocketNetLib\Via
2. Change the value of the Enabled entry from 1 to 0.
10. After you correct the registry key, click HKEY_LOCAL_MACHINE\1, click the File menu, and then click Unload Hive.

Note After you follow these steps, this checkpoint is fixed and is replicated to the specific node automatically during failover. You can bring the instance of SQL Server online.
=====================================================================================

A SQL Server cluster resource goes to a "failed" state when you try to bring the resource online in SQL Server

http://support.microsoft.com/kb/883732/en-us

=====================================================================================
1. Click Start, click Run, type Regedit, and then click OK.
2. In Registry Editor, locate and select the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Resources\\Parameters
3. Create the following registry values in the Parameters registry key:

For a default instance of SQL Server:
* InstanceName

Value Name: InstanceName
Value Type: REG_SZ
Value Data: MSSQLSERVER
* VirtualServerName

Value Name: VirtualServerName
Value Type: REG_SZ
Value Data:
For a named instance of SQL Server:
* InstanceName

Value Name: InstanceName
Value Type: REG_SZ
Value Data:
* VirtualServerName

Value Name: VirtualServerName
Value Type: REG_SZ
Value Data:
4. Quit Registry Editor.

=====================================================================================

23 March 2011

Report Deployment Issue - Unable to deploy to remote server

Needs to do the following settings, Which will fix this issue

1 ) UrlRoot>> http://localhost/reportserver<

2) Add Key="SecureConnectionLevel" Value="0" <<--This should be Zero, it will be 2

3)WebServiceAccount >>NT AUTHORITY\SYSTEM<


I am using windows integration hence
NT AUTHORITY\SYSTEM

17 March 2011

SQL Server Licensing

  • Processor License: This is the simplest option. It is also the most expensive. You pay a flat rate for each CPU running SQL Server, and that's it.

  • Server plus device CALs: CAL stands for Client Access License. Under this scheme, you pay one price for the computer running SQL Server (no matter how many CPUs it has) and a separate price for each device that accesses the data.

  • Server plus user CALs: Almost the same, but in this case you pay for the server plus you purchase a CAL for each user that accesses the data.

SQL Server Edition Differences

http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx

Best Link For Ever

KB MB GB TB PB EB ZB YB

KB Kilobyte 1,024 Bytes
MB Megabyte 1,048,576 Bytes
Gb Gigabit 1 million bits
GB Gigabyte 1,073,741,824 Bytes | One billion Bytes
TB Terrabyte 1024 GB, 1,048,576 MB, 8,388,608 KB, 1,099,511,627,776 Bytes and 8,796,093,022,208 bits.
PB Pettabyte 1024 TB, 1,048,576 GB, 1,073,741,824 MB, 1,099,511,627,776 KB, 1,125,899,906,842,624 Bytes and 9,007,199,254,740,992 bits.
EB Exabyte 1024 PB, 1,048,576 TB, 1,073,741,824 GB, 1,099,511,627,776 MB, 1,125,899,906,842,624 KB, 1,152,921,504,606,846,976 Bytes and 9,223,372,036,854,775,808 bits.
ZB Zettabyte 1024 EB, 1,048,576 PB, 1,073,741,824 TB, 1,099,511,627,776 GB, 1,125,899,906,842,624 MB, 1,152,921,504,606,846,976 KB, 1,180,591,620,717,411,303,424 Bytes and 9,444,732,965,739,290,427,392 bits
YB Yottabyte 1024 ZB, 1,048,576 EB, 1,073,741,824 PB, 1,099,511,627,776 TB, 1,125,899,906,842,624 GB, 1,152,921,504,606,846,976 MB, 1,180,591,620,717,411,303,424 KB 1,208,925,819,614,629,174,706,176 Bytes and 9,671,406,556,917,033,397,649,408 bits

15 March 2011

SQL Server 2005 Features Comparison

Scalability and Performance

Feature

Express

Workgroup

Standard

Enterprise

Comments

Number of CPUs

1

2

4

Max OS supported

Includes support for multicore processors.

RAM

1 gigabyte (GB)

3 GB

Operating system maximum

Operating system maximum

Memory limited to maximum supported by operating system.

64-bit Support

Windows on Windows (WOW)

WOW

Available

Available


Database Size

4 GB

No Limit

No Limit

No Limit


Partitioning




Available

Support for large-scale databases

Parallel Index Operations




Available

Parallel processing of indexing operations

Indexed Views




Available

Indexed view creation is supported in all editions. Indexed view matching by the query processor is supported only in Enterprise Edition.

High Availability

Feature

Express

Workgroup

Standard

Enterprise

Comments

Database Mirroring



Available 1

Available

Advanced high availability solution that includes fast failover and automatic client redirection

Failover Clustering



Available 2

Available


Backup Log-shipping


Available

Available

Available

Data backup and recovery solution

Online System Changes

Available

Available

Available

Available

Includes Hot Add Memory, dedicated administrative connection, and other online operations

Online Indexing




Available


Online Restore




Available


Fast Recovery




Available

Database available when undo operations begin

1 Single REDO thread and the safety setting is always on.

2 Supports two-node clustering support for the Database server only.

Manageability

Feature

Express

Workgroup

Standard

Enterprise

Comments

Auto Tuning

Available

Available

Available

Available

Automatically tunes database for optimal performance.

Profiler

1

2

Available

Available


SQL Server Management Studio Express

See Comments

Available

Available

Available

Easy-to-use graphical management tool available as a separate download or included with the SQL Server Express w/Advanced Services download

Management Studio


Available

Available

Available

Full management platform for SQL Server; includes Business Intelligence (BI) Development Studio.

Database Tuning Advisor



Available

Available

Automatically suggests enhancements to your database architecture to improve performance.

Serviceability Enhancements

Available

Available

Available

Available

Dynamic management views and reporting enhancements.

Full-text Search

See comments

Available

Available

Available

Available for SQL Server Express in the SQL Server Express w/ Advanced Services download

SQL Agent Job Scheduling Service


Available

Available

Available


1 Local and Relational Servers only.

2 This edition can be profiled from a Standard or Enterprise Edition of SQL Server 2005.

Security

Feature

Express

Workgroup

Standard

Enterprise

Comments

Advanced Auditing, Authentication, and Authorization

Available

Available

Available

Available


Data Encryption and Key Management

Available

Available

Available

Available

Built-in data encryption for advanced data security.

Integration with Microsoft Baseline Security Analyzer

Available

Available

Available

Available

Scans your system to check for common security vulnerabilities.

Integration with Microsoft Update

Available

Available

Available

Available


Programmability

Feature

Express

Workgroup

Standard

Enterprise

Comments

Stored Procedures, Triggers, and Views

Available

Available

Available

Available


T-SQL Enhancements

Available

Available

Available

Available

Includes exception handing, recursive queries, and support for new data types.

Common Language Runtime and .NET Integration

Available

Available

Available

Available


User-defined Types

Available

Available

Available

Available

Extend the server with your own custom data types.

Native XML

Available

Available

Available

Available

Includes XML indexing and full-text XML search.

XQuery

Available

Available

Available

Available


Notification Services



Available

Available

Allows the building of advanced subscription and publication applications.

Service Broker

See comments

Available

Available

Available

SQL Server Express can exchange messages with other editions. Messages between instances of SQL Server Express must be routed through another edition.

Integration and Interoperability

Feature

Express

Workgroup

Standard

Enterprise

Comments

Import/Export


Available

Available

Available


Integration Services with Basic Transforms



Available

Available

Provides graphical extract, transform, and load (ETL) capabilities.

Integration Services Advanced Transforms




Available

Includes data mining, text mining, and data cleansing.

Merge Replication

Available 1

Available 2

Available

Available


Transactional Replication

Available 3

Available 4

Available

Available


Oracle Replication




Available

Transactional replication with an Oracle database as a publisher

Web Services (HTTP Endpoints)



Available

Available

Support for native Web services, Web Services Description Language (WSDL), and Web authentication

1 Subscriber only.

2 Publish to up to 25 subscribers.

3 Subscriber only.

4 Publish to up to five subscribers.

Business Intelligence

Feature

Express

Workgroup

Standard

Enterprise

Comments

Report Server

See comments

Available

Available

Available

Report Server is available for SQL Server Express in the SQL Server Express with Advanced Services download.

Report Builder


Available

Available

Available

End-user reporting tool

Reporting Data Sources

1

Available

Available

Available


Scale Out Report Servers




Available


Data Driven Subscriptions




Available


Infinite Clickthrough




Available


Data Warehousing



Available

Available


Star Query Optimization

Available

Available

Available

Available


SQL Analytical Functions

Available

Available

Available

Available


BI Development Studio

See comments2

Available 3

Available

Available

Integrated development environment for building and debugging data integration, OLAP, data mining, and reporting solutions.

Enterprise Management Tools


Available

Available

Available

Integration with SQL Management Studio, SQL Server Profiler, SQL Server Agent, Backup/Restore.

Native Support for Web Services (Service Oriented Architectures)

Available 4

Available 5

Available

Available

Allows access to data from any device.

Analysis Services



Available

Available

Powerful analytics and data mining capabilities.

Unified Dimensional Model (UDM)



Available

Available

Enterprise business data model enables fast, interactive, ad hoc analysis of large data sets. Builds smarter reports that leverage centralized business logic and key performance indicators (KPIs), as well as the performance of UDM.

Business Analytics



Available

Available

Multidimensional Expression (MDX) scripts and MDX debugger, .NET stored procedures, Time Intelligence, KPI Framework.

Advanced Business Analytics




Available

Account intelligence, metadata translation, perspective and semi-additive measures.

Proactive Caching




Available

Provides automated caching for greater scalability and performance.

Advanced Data Management




Available

Partitioned cubes, parallel processing, server synchronization.

Full Writeback Support




Available

Dimension and cell writeback

Data Mining



Available

Available

Nine algorithms including decision and regression trees, clustering, logistic and linear regression, neural networks, naive bayes, association, sequence clustering, and time series. Build smarter reports that leverage centralized business logic and KPIs, as well as the performance of UDM.

Advanced Performance Tuning




Available

Additional options for tuning data mining models for the highest accuracy, performance, and scalability.

SQL Server Integration Services Data Flow Integration




Available

Perform data mining prediction and training operations directly in your operational data pipelines.

Text Mining




Available

Convert unstructured text data to structured data for analysis via reporting, online analytical processing (OLAP), or data mining.

1 Local and Relational Servers only.

2 Report Designer only.

3 Report Designer only.

4 Reporting Services only.

5 Reporting Services only.