Columbus, OH Questions and Answers SQL 2005 (10-6-2005)

  • Article

Good afternoon everyone.  Thank you for attending my TechNet event in Columbus!  Also thank you for a lot of great questions, this is a long post with lots of great information, please take time to let me know what you think!

I am glad everyone was able to participate and I think we had a great time!  I am sorry that our mighty Buckeye's lost on Saturday, but heck there is still the Michigan game!  Beat Michigan!

There were a lot of questions asked at the event, and here are the Questions and Answers from the event.  As always feel free to comment if I missed any question or if you need additional information, enjoy!:

Q: What are the replication possibilities between a SQL 2000 and a SQL 2005 server?
A: Microsoft SQL Server 2005 supports upgrading replicated databases from previous versions of SQL Server; it is not required to stop activity at other nodes while a node is being upgraded. Ensure that you adhere to the rules regarding which versions are supported in a topology:

  • Microsoft SQL Server version 7.0 service pack 4 (SP4) is the minimum version required to participate in a replication topology with SQL Server 2005. If you use SQL Server 2000, service pack 3 (SP3) is required.
  • A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).
  • A Publisher can be any version as long as it less than or equal to the Distributor version.
  • Subscriber version depends on the type of publication:
    • A read-only Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server version 7.0 Publisher running can have SQL Server 2005 Subscribers; and a SQL Server 2005 Publisher can have SQL Server version 7.0 Subscribers.
    • An updatable Subscriber to a SQL Server 2005 transactional publication can be any version equal to or greater than Microsoft SQL Server 2000 SP3.
    • A Subscriber to a merge publication can be any version less than or equal to the Publisher version.

Q: How does merge replication work in SQL 2005?
A: Merge replication still works in SQL 2005 very similar on how it did in SQL 2000.  Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

Merge replication is typically used in server-to-client environments. Merge replication is appropriate in any of the following situations:

  • Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.
  • Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
  • Each Subscriber requires a different partition of data.
  • Conflicts might occur and, when they do, you need the ability to detect and resolve them.
  • The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher to reflect the net data change (that is, the fifth value).

Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one node, the same data may have been updated by the Publisher and by more than one Subscriber. Therefore, conflicts can occur when updates are merged and merge replication provides a number of ways to handle conflicts.

Q: What are some of the key changes in Report Services and Analysis services?
A:   The analytics in SQL 2005 really got overhauled there are two great articles that give fanatic overviews of these two features:

Q: Is there a query governor tool in SQL 2005?
A: Yes you can, what you need to set is the query governor cost limit.  This is very similar to what we did in SQL 2000 and the option will allow you to specify an upper limit for the time in which a query can run.

Q: How does schema separation impact an upgraded SQL 2000 database and any code?
A: In earlier versions of SQL Server, INFORMATION_SCHEMA column names matching the value '%SCHEMA' return the name of the user. In SQL Server 2005, these columns return the schema name. When a database is upgraded to SQL Server 2005, the schema name is always the same as the user name and any applications that reference these columns will not fail. However, users that implement SQL Server 2005 user-schema separation features in their databases should be aware that their application may fail if the expected data is a user name instead of a schema name.

Q: Is there still connection pooling in SQL 2005?
A: Yes, it still offers the same advantages of reducing the number of times that new connections need to be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration.  There is a great article located on the MSDN site to understand more about Connection Pooling

Q: Can you integrate a Surface Area Configuration (SAC) .xml file during the install of SQL 2005?
A: Unfortunately no you cannot integrate the Surface Area Configuration (SAC) file during the install.  Microsoft SQL Server 2005 selectively installs and starts key services and features. This helps reduce the attackable surface area of a system. In the default configuration of new installations, many features are not enabled.  This is in part to help ensure an efficient upgrade.  Also of note here is that no services or features are turned off during the upgrade process, but all surface area configuration options can be applied after the upgrade is completed. 

Q: Can you provide more information on the Surface Area Configuration (SAC) command line tool?
A:   The Surface Area Configuration for Features tool provides a single interface for enabling or disabling many Database Engine, Analysis Services, and Reporting Services features. Disabling unused features helps to secure your Microsoft SQL Server installations by reducing the SQL Server surface area.  The Books Online has a great article on the many switches and uses for the SAC tool.  I could not find the article online, so I have created a separate posting with all the information here: Surface Area Configuration (SAC) tool Command Line Utility for SQL Server 2005.

Q: How do maintenance plans from SQL 2000 migrate over to SQL 2005?
A: Existing database maintenance plans are upgraded and continue to work in SQL Server 2005. However, you will not be able to create new database maintenance plans by using SQL Server Management Studio. You can migrate existing database maintenance plans to the new format by selecting Migrate from the context menu for any database maintenance plan. Because the new maintenance plan feature is not a direct replacement of database maintenance plans, some functionality might be lost after migration. Migrating a database maintenance plan does not delete the old plan, so you can test its functionality as a maintenance plan before removing the old plan.  The following features are no longer supported within database maintenance plans:

  • Log shipping
  • The Attempt to repair any minor problems option of the Database Integrity Check task

SQL Server 2000 databases with database maintenance plans can be migrated, but you might lose some functionality. Remove the Attempt to repair any minor problems option from Database Integrity Check tasks.

 

SQL Server 2005 introduces substantial changes to log shipping that prevent it from being included in a database maintenance plan. For more information, see "Log Shipping" in SQL Server 2005 Books Online.

Q: Can the SA account expire?
A: It appears that yes the account can expire.  However, by default the SA account is not configured to expire (Did I mention you can also rename the account?), so make sure that the check box remains clear.  The main concern around this question if I had to guess is what happens if you have applications that use the SA to run.  Hopefully you do not have those application, I loved the quote from an article I found on the web: “I thought we had all learned by now that the "sa" account is an administrative only account” I think that this extremely important to remember and if you have to use the SA account use another account name to accomplish the task.  The article I referred to is here: 10 Things You Shouldn't Do with SQL Server (Data Access Developer "Don'ts") overall this is a great article for you too look at, and has a lot of great information.

Q: What is stored in the temp-DB when you utilize snapshot isolation?
A: Snapshot isolation is one of the row versioning-based isolation levels used to increase the concurrency of the data being accessed. Enabling this option causes all data modifications for the database to be versioned. A copy of the data before modification is stored in tempdb even when there are no active transactions using row versioning-based isolation. The data after modification includes a pointer to the versioned data stored in tempdb. For large objects, only part of the object that changed is copied to tempdb.

What is in tempdb?

For each instance of the Database Engine, tempdb must have enough space to hold the row versions generated for every database in the instance. The database administrator must ensure that tempdb has ample space to support the version store. There are two version stores in tempdb:

· The online index build version store is used for online index builds in all databases.

· The common version store is used for all other data modification operations in all databases.

Row versions must be stored for as long as an active transaction needs to access it. Once every minute, a background thread removes row versions that are no longer needed and frees up the version space in tempdb. A long-running transaction prevents space in the version store from being released if it meets any of the following conditions:

· It uses row versioning-based isolation.

· It uses triggers, MARS, or online index build operations.

· It generates row versions.

When tempdb runs out of space, the Database Engine forces the version stores to shrink. During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. A message 3967 is generated in the error log for each victim transaction. If a transaction is marked as a victim, it can no longer read the row versions in the version store. When it attempts to read row versions, message 3966 is generated and the transaction is rolled back. If the shrinking process succeeds, space becomes available in tempdb. Otherwise tempdb runs out of space and the following occurs:

· Write operations continue to execute but do not generate versions. An information message (3959) appears in the error log, but the transaction that writes data is not affected.

· Transactions that attempt to access row versions that were not generated because of a tempdb full rollback terminate with an error 3958.

 

Q: What kind of security is built into replication?
A: This is a fantastic question and there is some great information in the Books Online on the security of replication, I will give a brief overview, but more details please consult the Books Online.  Specifically there are three areas that are considered when securing replication or for that matter any software system.  They are authentication, authorization and encryption.  Specifically in replication we have an additional option in filtering the published data:

Authentication and Authorization

Replication security uses authentication and authorization to control access to replicated database objects and to the computers and agents involved in replication processing. This is accomplished through three mechanisms:

  • Agent security: The replication agent security model allows fine-grained control over the accounts under which replication agents run and make connections
  • Administration roles: Ensure that the correct server and database roles are used for replication setup, maintenance, and processing.
  • The publication access list (PAL) : Grant access to publications through the PAL. The PAL functions similarly to a Microsoft Windows access control list. When a Subscriber connects to the Publisher or Distributor and requests access to a publication, the authentication information passed by the agent is checked against the PAL.

Encryption

Replication does not encrypt data stored in tables or sent over network connections. This is by design, because encryption is available at the transport level with a number of technologies, including the following industry standard technologies: Virtual Private Networks (VPN), Secure Sockets Layer (SSL), and IP Security (IPSEC). It is recommended using one of these encryption methods for the connections between computers in a replication topology.   One special consideration if you are using SSL to secure the connections between computers in a replication topology, specify a value of 1 or 2 for the -EncryptionLevel parameter of each replication agent (a value of 2 is recommended). A value of 1 specifies that encryption is used, but the agent does not verify that the SSL server certificate is signed by a trusted issuer; a value of 2 specifies that the certificate is verified.

 

Filtering Published Data

In addition to using authentication and authorization to control access to replicated data and objects, replication includes two options to control what data is available at a Subscriber: column filtering and row filtering.  When you define an article, you can publish only those columns that are necessary for the publication, and omit those that are unnecessary or contain sensitive data.