Blogland

Blog Move

john_magnabosco@yahoo.com — Fri, 19 Sep 2008 20:18:00 GMT

I am very excited to announce that this blog is moving to Simple-Talk.com!

Simple-Talk.com is a great website filled with articles, blogs, forums and various other features that focus on SQL Server and .NET. My blog will be accompanying some well respected Industry experts such as Brad McGehee, Brian Donahue and other Red-Gate product developers.

This is a great opportunity and I thank the editors of Simple-Talk.com for taking me on as a guest blogger! The link to my new location is:
http://www.simple-talk.com/community/blogs/johnm/default.aspx

In the meantime I will re-work johnmagnabosco.com into something that enhances my blog at Simple-Talk.com.

Save Not Permitted

john_magnabosco@yahoo.com — Sun, 14 Sep 2008 23:55:00 GMT

Recently, I was working with SQL Server 2008 - SQL Server Management Studio and encountered a warning message that read “Save Not Permitted.” This message arose as I was changing the data type of a column as well as making another column larger in size… varchar(50) to varchar(100). This table was fairly new and did not contain data. There also were no foreign key relationships established.

I must admit this warning did throw me for a loop; after all, SQL Server 2005 allowed changes such as these to occur without warning.

The secret key to this issue is that changes such as the ones that I was making require the table to be dropped and re-created. This is evident when the script to the command is revealed by clicking on the script icon in SQL Server Management Studio. Repeatedly dropping and re-creating tables can present some issues as well as the execution of such commands on large tables could present some temporary performance issues. This feature does protect the database from such actions being taken in a less than careful manner.

In my scenario, this protection was not necessary. The instance was on my personal laptop and I was developing a demonstration database in which such a feature was not necessary. To turn this default feature off the following steps needed to be taken:

1. Open SQL Server Management Studio 2008

2. Select “Tools” in the menu bar.

3. Select “Options” in the “Tools” submenu.

4. In the “Option” window, click the “Designers” option.

5. Uncheck the “Prevent saving changes that require table re-creation”

6. Click “OK”

Problem solved.

Here is a link from MSDN, while very brief, provides some information regarding this issue:

http://msdn.microsoft.com/en-us/library/bb895146.aspx

Google Chrome Shines Bright

john_magnabosco@yahoo.com — Fri, 05 Sep 2008 02:04:00 GMT

Earlier this week a friend of mine called me over to his desk with an enthusiastic “Check this out!”. Without any additional word, he proceeded to open a browser and navigated to a site that typically takes a couple of seconds to load. Behold! The page loaded in a flash. In a brief moment of disbelief I blinked my eyes rapidly and exclaimed “Wow!”

As I looked around on the screen, it became apparent that this was not the Internet Explorer 7 that I have been used to seeing in my internet experience. It was something called “Chrome” By Google. As my friend and I checked out all of the nooks and crannies of this rather simple browser I was further impressed.

When I arrived at home that evening, I set aside my stack of tasks that I usually attend to each night to download and install this new browser. The download was blazing fast as was the installation. I began navigating to my favorite sites which contained various features that provided interesting fodder for review. I did notice a few gotchas:

- There was one site that contained a flash image that did not appear with Chrome. Ironically, the very next day I received a plug-in for Flash which resolved the issue.

- I visited Facebook and noticed that I could not comment on wall items. When I clicked on the word “Comment” nothing happened.

- One of the online e-mail systems that I use is SmarterMail. The appearance of the queue is unappealing in Chrome and the delete message option does not work. I question whether the send function is working. Granted, I am using version 3.3 of this product which is a bit out of date.

- I do miss the dropdown option on the address bar that appears in IE 7. I have discovered that I use that feature much more than I thought.

Don’t let these gotchas get you down. There is much to love about Chrome. Here are just a few features that I have discovered in my review:

- Simplicity rules. The features and settings of this browser not as extensive as IE 7; but that could be one of the reasons it performs so well.

- Chrome provides the now expected multiple tab interface for browsers. When I launch a new tab Chrome offers me, in graphical format, my nine most visited sites as well as a listing of my most recent visited bookmarks and recently closed tabs. Very convenient.

- The incognito window. If there is a need to navigate to a page where I do not want to be included in the navigation history, search history or leave cookies on your computer the opening of an incognito window will serve that purpose. This is nice if you are sharing a computer and plan on doing some Christmas shopping for family members… or you don’t want anyone to discover that you regularly visit the Sponge-Bob Squarepants… ah, I mean the Danica Patrick fan site.

- Chrome offers some features that will accommodate the interests of the developer and power user. With JavaScript Console, JavaScript Debugger and Task Manager these features are quite impressive.

Overall, I am very pleased with this browser. While Chrome is technically still in Beta, it is a very-near release version. To download Chrome, go to: http://www.google.com/chrome

Google has also provided a rather creative explanation of the technology that was implemented with this new browser option: http://www.google.com/googlebooks/chrome/index.html

Google’s Chrome browser certainly introduces an interesting dynamic in the browser realm. There certainly has been quite a buzz about this new contender on the blogs, tweets and forums.

Utilizing DMVs

john_magnabosco@yahoo.com — Fri, 29 Aug 2008 10:50:00 GMT

In previous blog entries I have provided tips on querying databases using system tables. In SQL Server 2000, this was the way to gain the important information that a database administrator needed to monitor the database and instance. A downside to system tables is that they were discouraged for use in production applications since they could be modified or dropped at any given time. Recall any system table in BOL 2005 or 2008 and you will read:

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

When SQL Server 2005 was introduced the feature of dynamic management views (DMV) and functions were added. This presented a set of tools that could replace the use of system tables as well as provide additional information that may not have been so intuitive to obtain. Yet, when recalling a DMV in BOL 2008 you may encounter the following note:

Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in SQL Server 2008.

They key to these disclaimers is that the purpose of these valuable resources are intended for the administrator or developer who needs to obtain valuable information to maintain the health of the database and instance as well as troubleshoot its performance.

There may be times where it is tempting to incorporate these into stored procedures or user defined functions that are used by a production system; but it is a temptation that is potentially fraught with dismay for when the database is upgraded from SQL Server 2008 to SQL Server 2010 you may suddenly find that the application that depended upon the action_in_log column of sys.dm_audit_actions no longer functions because its data type has changed from bit to nvarchar. (This is an example only, no such change has occurred)

This is not intended to be a discouragement from using DMVs or even System Tables. These are valuable tools in the tool belt of any DBA/Developer. In their careful use their value is immeasurable.

To learn more about DMVs, go to: http://technet.microsoft.com/en-us/library/ms188754.aspx

Seeking Text Occurrences in the Catalog

john_magnabosco@yahoo.com — Fri, 22 Aug 2008 10:34:00 GMT

In a large enterprise database you will find situations where you need to quickly identify the occurrence of a column name within the tables of the catalog. An example of such a situation would be that a column size needs to be increased on the “Auto_Model” field and the database in which you are working is not normalized to a very high degree. To find all of the tables that contain the column name “Auto_Model” in the catalog the use of the query that was offered in my June 15, 2008 blog entry (http://www.johnmagnabosco.comhttp://www.johnmagnabosco.com/Blogland/tabid/160/EntryID/50/Default.aspx) would provide this very information.

Other times may demand that we need to find the occurrence of any text within many database objects. For example: In a code review, I want to evaluate all uses of the “COALESCE()” method in the database to make sure that it is being used appropriately. Assuming that SQL Server 2005 is the database in use, the following query would be executed on the database in question:

        SELECT
                 SYS.SYSOBJECTS.NAME,
                 SYS.SYSOBJECTS.XTYPE
        FROM
                 SYS.SYSCOMMENTS
                 INNER JOIN SYS.SYSOBJECTS
                       ON SYS.SYSCOMMENTS.ID = SYS.SYSOBJECTS.ID
                       AND SYS.SYSCOMMENTS.COLID IS NOT NULL
        WHERE
                SYS.SYSCOMMENTS.TEXT LIKE '%COALESCE%'

The results of this query would be a listing of the database objects that contain the text “COALESCE”. The object types will be reflected by the XTYPE column from the SYSOBJECTS system table. These are represented by a letter. The corresponding objects are:

        P: Stored procedure
        S: System table
        TR: Trigger
        U: User table
        V: View
        X: Extended stored procedure

One thing to keep in mind in regard to system tables: While they are very useful and handy it is not recommended that these are implemented into a production system since they may be removed or altered in later versions of SQL Server. This query is offered as a tool that will be helpful in your administration or development efforts.

Also as a side note: This script does work in SQL Server 2008.

SQL Server 2008 Installation Challenges Overcome

john_magnabosco@yahoo.com — Thu, 14 Aug 2008 10:59:00 GMT

Shortly after the release of SQL Server 2008, I downloaded my copy of the Developer Edition. I did run into some problems with my installation; but in a time when information is easily obtainable on the internet I quickly found the answers to my issues. I felt that others may be experiencing similar challenges; thus this blog entry.

The first issue I encountered was due to the fact that I had Visual Studio 2008 also installed on my laptop. While in a production environment this may not be the recommended situation; but on a developer's laptop it certainly is not an uncommon situation. The issue arose because SQL Server 2008 requires service pack 1 for Visual Studio 2008 as well as the .NET Framework 3.5. During my first attempt at installationthe .NET Framework 3.5 sp1 automatically was downloaded and installed. To download SP1, goto: http://msdn.microsoft.com/en-us/vstudio/cc533448.aspx -- the installation does take quite a while)

Unfortunately my eagerness to install SQL Server 2008 revealed that Visual Studio 2008 SP1 had not yet been released. I e-mailed two of my favorite Microsoft pals and they returned a message only a day later that Visual Studio 2008 SP1 was then available.

My second issue was that I kept getting a message that there were SQL Server Express Facets on my laptop. Unsure of what that really meant I again journeyed through the internet and found that my installation of Red-Gate's SQLPrompt used these elements. The suggested solution was to uninstall SQLPrompt. (A side note I also did see that Red-Gate was currently working on this known issue.) I did as suggested and to my dismay I still got the same error when attempting to install SQL Server 2008. I then ran across a very useful blog entry that solved my problems: http://jasonfollas.com/blog/archive/2008/06/19/sql-server-2008-rc0-install-sql2005ssmsexpressfacet.aspx

Despite the blog being focused on the installation of RC0, the resolution worked well for my RTM. After these two issues were resolved I finally was able to begin playing in my newest toy.

SQL Server 2008 RTM

john_magnabosco@yahoo.com — Fri, 08 Aug 2008 10:53:00 GMT

On August 6th, the press release that was long awaited by database professionals around the globe was distributed. SQL Server 2008 is now officially on the market.

Over the past 12 months I have had the opportunity to schedule a few SQL Server 2008 presentations at our local user group meeting (IndyPASS). These have been mostly overviews of new features that are included in this newly released version. In the first exposure of these features, my impression was that there was not going to be a large amount of changes beyond BI features. As more information leaked out about the other aspects of this release were presented, it was obvious that this release contains some very exciting improvements and additions that encompass the entire product.

I have also had the opportunity to experiment with SQL Server 2008 RC0, thanks to a virtual hard drive that was created by a friend of mine. My primary focus was to experiment with the new auditing feature. This is very impressive addition. It will be very interesting to see how other utilize this feature and see this feature evolve over the next few years.

As I dwell in the world of encryption, I am looking forward to learning more about transparent encryption as well as the extensible key management features of this release. Key management is critical for cryptography and it is good to see more efforts put toward that arena.

At the Indianapolis Heroes Happen Here launch event in April I saw a demonstration on the policy-based management features. This feature was impressive in that the security tasks could be administered and applied to multiple instances of SQL Server 2008. This feature is definitely something to explore more in depth in the near future.

This brief summary of features is not all inclusive. They are simply the ones that I have explored at some level and are looking forward to learning more about them and how they can be used in the real world. I am looking forward to getting my paws on the RTM and make yet another step forward in this journey of SQL Server.

Here are some links that may be of value:
     - August 6th SQL Server 2008 RTM Press Release:
       http://www.microsoft.com/presspass/press/2008/aug08/08-06SQLServer2008PR.mspx
     - SQL Server 2008 Home Page:
       http://www.microsoft.com/sqlserver/2008/en/us/default.aspx
     - What’s New In SQL Server 2008 Webcast:
        https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=139087

About GO

john_magnabosco@yahoo.com — Sun, 03 Aug 2008 21:08:00 GMT

When viewing sample code for T-SQL, you may find that the word “GO” is used at the end of the code or used multiple times in the code. While the use of this statement may be somewhat self-explanatory it is worth pause and review.

SQL Server Books On Line (BOL) simply notes the description of “GO” as:
“Signals the end of a batch of Transact-SQL statements to the SQL Server utilities.”

An example of its use would look like the following:

         [T-SQL STATEMENT 1]
         [T-SQL STATEMENT 2]
         [T-SQL STATEMENT 3]
         GO

There are two items to keep in mind when using “GO”:

1) User defined variables are only accessible from within the batch it is declared.
For example, the following will return an error:

         DECLARE @VARIABLE INT
         SET @VARIABLE = 123
         GO

         SELECT @VARIABLE
         GO

The error returned: ‘Must declare the scalar variable "@VARIABLE".’

2) There is an argument to “GO” that allows multiple execution of the batch.
For example, the following will execute the batch five times:

SELECT [FIELD1] FROM [TABLE] WHERE [FIELD2] = ‘TEST’
GO 5

An Existential Review of Data

john_magnabosco@yahoo.com — Fri, 01 Aug 2008 11:47:00 GMT

According to the Merriam-Webster Dictionary Online the word data is defined as follows:

1 : factual information (as measurements or statistics) used as a basis for reasoning, discussion, or calculation …
2 : information output by a sensing device or organ that includes both useful and irrelevant or redundant information and must be processed to be meaningful
3 : information in numerical form that can be digitally transmitted or processed.

Another interesting note: Also according to the Merriam-Webster Dictionary Online, the word “data” became an official English word in 1646.

Data exists everywhere and is in all things. An activity that is inherent in all living beings is to process this data. The level in which this data can be processed, understood, and used varies significantly upon the advancement of the organism’s processing center, often referred to as a brain. We humans enhance this processing power with external mechanisms such as computers.

A benefit of Community is that data that is discovered and processed by one entity can be shared with other entities resulting in a more effective and efficient use of the processed data. In order for this to occur the data must be communicated. Data is communicated through graphical or verbal representations such as drawing images, writing text or speaking a language. In the case of computers, data is communicated with a series of bits (ones and zeros) and converted into a form that is readable by the user.

For as long as there has been data there also has been the database. Be it a stone wall in a cave that is sketched with images of a recent bison hunt, the clay walls of a Pharaoh’s tomb in which the prayer for their journey into the afterlife is scribed, a journal recording the new discoveries by an Explorer to a new world, a document defining the principles of a new country and declaring its independence, or even the folded envelopes that are carefully folded into a President’s coat pocket that contains the words of a speech that will become famous.

The basic purpose of the database is to store and preserve data so that it can be obtained and searched repeatedly in a consistent form by many entities. A database can be a simple Post-It note with a grocery list or as complex as the Human mind can imagine.

As Database Professionals we are charged with facilitating the access, use and aggregation of data so that others can act upon it. We also are the ones who attend to the life cycle of data: Its creation, its storage, its modification, its archiving and finally its disposal. In each of the steps that mark data life, we tend to the unique needs of that milestone.

When data is terminated before its designated time, we Database Professionals act as an EMT might when the same happens to a Human. We race to try to recover the data so that it can continue its intended purpose.

Throughout the span of data life we tend to it to ensure that it is safe and healthy, much like a loving parent does with their child. Also like parents, when it is nearly impossible to understand what the data is trying to communicate we make efforts to decipher what is being said so that we can react accordingly. (“Dude my friend’s car is sick!” – Should it be taken to the Mechanic? Or is it just an impressive vehicle?)

This brief existential review of data, the database and the Database Professional in some eyes may be trivial; but it is valuable to take pause and explore the world in which we work at the most basic level. While it is not giving you the review of the latest technology, a fantastic new snippet of code, or discussing a best practice that can be applied to your daily work, it is presenting the consideration and impact of the things in which we labor on a daily basis… the proverbial smelling of the roses along our paths.

Backing Up Service Master Keys

john_magnabosco@yahoo.com — Wed, 23 Jul 2008 10:41:00 GMT

When an instance of SQL Server 2005 is installed a Service Master Key is created. This Service Master Key is the top unit in the encryption key management hierarchy for SQL Server. Its purpose is to encrypt the certificates, symmetric keys and asymmetric keys that are employed on any/all databases that reside within that instance.

The Service Master Key is based off of the service account credentials as well as the machine key from the Windows Data Protection API. Without it, all subsequent keys would not be decipherable; thus rendering the data, in which the encryption was implemented, forever a mystery.

Regular database backups are a best practice that most DBAs execute. This practice allows the database to be restored from a specific point in time in the event of data loss or other nightmare scenarios. If encryption is implemented a simple database backup will not suffice for recovery. The Service Master Key is not included in the standard database backup process. Backing up the Service Master Key is accomplished by executing the following script:

        USE [master]
        BACKUP SERVICE MASTER KEY TO FILE = 'filename'
              ENCRYPTION BY PASSWORD = 'password'
       GO

By executing this script a file is created in the path and given the file name identified in the argument. Once this file has been created it can then be backed up through the regular server backup process. The file itself is encrypted with the password that is supplied in the argument. This prevents casual snooping of its contents or unauthorized recovery of the Service Master Key onto another server.

Careful consideration of the location in which the backup file is written is strongly recommended. It would be further recommended that the file is written to a separate physical server than the one that the database backup file resides. While this will not prevent the thief from being able to restore the database onto another server it will prevent them from decrypting the encrypted data that is secured by the Service Master Key.

The act of backing up anything is useless without knowing how to recover the backed up item. The following script allows you to restore the Service Master Key:

        USE [master]
        RESTORE SERVICE MASTER KEY FROM FILE = 'filename'
              DECRYPTION BY PASSWORD = 'password'
       GO

The argument is the full path and file name of the backup file. The argument is the same password that was used to encrypt the Service Master Key backup file.

For more information regarding the encryption hierarchy, go to:
http://msdn.microsoft.com/en-us/library/ms189586.aspx

For more information about Service Master Keys, go to:
http://msdn.microsoft.com/en-us/library/ms189060.aspx

For more information regarding backing up a Service Master Key, go to:
http://msdn.microsoft.com/en-us/library/aa337561.aspx

For more information regarding restoring a Service Master Key, go to:
http://msdn.microsoft.com/en-us/library/aa337510.aspx