Spring Texas Questions and Answers SQL 2005 (9-13-2005)

  • Article

Good morning everyone!  Thank you for coming to the event in Spring, TX.  I am glad you asked a lot great questions.  I was able to find a lot of great information in the Books Online, it is a free download and you can get it here.  You will also need to download the .NET framework 2.0 to use the books online.  Here are the questions I took down at the event.  Please comment if you have any further questions or if I missed any.

So with any further delay and absolutely no ado here you go :

Q: Why is the integration of the CLR in SQL 2005 so important?
A:   This is a great question and willing to learn something new, I went searching, among all the household cleaners that $19.95 could buy I stumbled upon a great blog on SQL and CLR integration.  This blog seems to have quite a few postings on tips and tricks on how to effectively use CLR integration.  CLR integrations is so incredible because it solves some problems with writing procedural code in T-SQL.  Developers will without doubt include a stored procedure or two.  This was a huge challenge in previous versions of SQL.  You either lived with writing T-SQL code, or you had to use an extended stored procedure/function and you still had the challenge of making those objects inter-operate.  There is a great article here, that overviews why this is so important.

Q: How do you accomplish field level security in SQL 2005?
A: That is a great question on one the fantastic new enhancements in SQL 2005.  SQL 2005 uses a common paradigm for defining this type of control called, security labeling.  There are three key assumptions (yeah I know assuming but these are great design principles:

1. Rely on the SQL Server and Microsoft Windows® security models. Avoid hand-rolled authentication mechanisms.
2. Keep it simple. In some common scenarios, the number of possible security label combinations can grow very large. Avoid a proliferation of fine-grained security groups, while still allowing very fine-grained control of data.
3. The design must scale to very large datasets, containing tens of millions of rows.

The Implementing Row level and Cell level Security white-paper details the following 6 steps to accomplish this type of security:

1. Adds structures for defining arbitrary labeling categories and markings.
2. Allows the user’s label to be defined intuitively through role memberships for basic markings (e.g., Top Secret, Confidential; USA, UK, Task-force Z, etc.)
3. Provides for write-up, write-down, or other control models for writing data.
4. Makes selective use of encryption within the database to provide cell-level security, exploiting the fully internal, self-managing certificate store in SQL Server 2005.
5. Provides formulaic guidelines, such that tools could be developed to automate much of the implementation given basic input choices by developers or administrators.
6. Provides strategies for protecting against certain narrow vulnerabilities in row-level security—vulnerabilities that are shared by multiple vendors’ row-level security solutions.

Q: Can you link tables in SQL Express?
A:   It does not appear that you can link tables with SQL express.  However, I did find an excellent web site for SQL Express, and did find a list of the top 10 new features:

  1. Integrated common language runtime (CLR) support. Stored procedures and functions can be written using your favorite programming language.
  2. Native XML support.
  3. XCopy support for moving databases from one location to another.
  4. Robust and reliable installation using either graphical user interface (GUI) or silent modes.
  5. Automated servicing and patching.
  6. Deep integration with Visual Studio to provide access to rich data controls like the DataGridView, DataNavigator, and DataConnector.
  7. Support for databases up to 4GB.
  8. Support for disconnected systems with SQL Server Service Broker and Replication.
  9. SQL Server Express Manager, a new, easy-to-use database management and query analysis tool.
  10. Easier deployment via Visual Studio ClickOnce (available in Beta 3).

Q: What are some good Resources for SQL 2005 and mobile development?
A: That is a great and extremely interesting question.  I was only able to find two white-papers on the environment but they are very good.  The link I would add to your favorites is this: SQL 2005 Mobility and look for updates down the road.  As for the question I believed in centered around data replication improvements and SQL 2005 mobility. Microsoft SQL Server 2005 Mobile Edition 3.0 (SQL Server Mobile) supports two methods of exchanging data with a SQL Server database (full white-paper):

  • Merge replication, which provides a robust full-featured solution that allows a mobile application to make autonomous changes to replicated data, and at a later time, merge those changes with a Microsoft SQL Server database, and resolve conflicts when necessary.
  • Remote data access (RDA) provides a simple way for a mobile application to access (pull) and send (push) data to and from a remote Microsoft SQL Server database table and a local SQL Server Mobile database table. RDA can also be used to issue SQL commands on a server running SQL Server.

There is also a whole section on the new features in the books online.

Q: What SQL 2000 stored procedures (SP’s) are left?
A:   It appears that there are still a lot of the same sp’s left in SQL 2000.  However, their usage is dramatically fading away.  Mainly due to the CLR integration built into SQL 2005 (see the first question).  Additionally with the new Dynamic Management Views (DM’s) the need to run stored procedures for system information are really not needed anymore.  (i.e. there used to be a be SP to look up transaction locks, now there is a dm that does the same function.

Q: How do SQL 2000 users and passwords migrate over to SQL 2005?
A:   In the books online and in the beta the user accounts and passwords are migrated over as is.  You will need to go in an enforce your administrative password policy after the accounts are migrated over.  Although it appears there are a nice list of options when migrating the accounts.  Of special note in the current release, SQL Server Setup may not enforce the strong password requirement on some default configurations of Microsoft Windows Server 2003 where the computer is not a member of a domain.

Q: How do you upgrade a SQL 200 Cluster?
A: This is a great questions and there are a lot of detailed steps to successful do this.  The books online has a lot of great information on this procedure, you can also check out the article on the web: How to Upgrade a SQL 2005 Cluster.  One of the fantastic tools that you can use here as well to assist you is the Upgrade Advisor.  One of the key steps during this upgrade or really any upgrade of a cluster is to set up a private “heartbeat” on the clustered servers.  All your doing is trying to eliminate possible communication issues and remove all unnecessary network traffic from the network adapter that is set to Internal Cluster communications only.   You can learn more about the private heartbeat here.

Q: Any good information SQL 2005 64 bit?
A:   Unfortunately I was unable to dig up any really good information the SQL 2005, aside from the system requirements.  I also did find a great list of features that are supported by the different versions of SQL here.

Q: Some more resources on Peer to Peer Replication, in regards to table structure conflicts?
A: This all has to do with the limited capabilities of Peer to Peer to handle conflict.  There is a great article that discusses how conflict and resolution occurs, and it can be found here.  In a nut shell in table structures changed the replication will fail, at least it did in the early beta, normally you can configure conflict resolution in one of three ways (conflicts are recorded and can be viewed using the Conflict Viewer):

  • Publisher wins (the default)
  • Publisher wins and the subscription is reinitialized
  • Subscriber wins