January 2004 - Posts
A common task I run into is changing the ownership on a database table or stored procedure in SQL Server. There are times when the owner is set incorrectly or differently what what I would like.
For example, if a table is created using 'CREATE TABLE Products' but I was logged in as 'scott' to the 'orcsweb' database, the owner would be 'scott' and the fully qualified object name would be 'orcsweb.scott.Products'. This isn't always best. In most cases I prefer 'dbo' as the owner since it makes queries easier when using mulitple users.
Of course if you created the table like so: 'CREATE TABLE dbomultiple.Products' then the owner will be 'dbo' but in the case where this was created wrong in the first place how do you change this afterward?
The manual way if you only have a couple tables or objects to change is to use sp_changeobjectowner. This is great and what I use many times.
But, what about the times when you have quite a few to change and doing it manually isn't reasonable or desirable? David Penton, Microsoft MVP - SQL Server, has put together a great SQL script that I think everyone should keep handy. (Yes, I did get his permission to post this)
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = 'oldOwner_CHANGE_THIS'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
EXECUTE sp_MSforeachtable @sql
-------------------------------
The same can be done to stored procedures. This example works differently though. It doesn't actually make the change. Save the results in text and then paste the result back into Query Analyzer and run it.
-------------------------------
DECLARE @oldOwner sysname, @newOwner sysname
SELECT
@oldOwner = 'oldOwner_CHANGE_THIS'
, @newOwner = 'dbo'
select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE = 'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0
This past week I was asked a question about the new POP3 service that comes with Windows Server 2003. Since I hadn't tried it out yet I took the opportunity to install it and learn a bit about it.
My overview-summary-in-a-sentence: It is VERY basic at this point but might be a good start for someone with simple requirements.
Microsoft already has a powerful email solution in Exchange Server but have included this free POP3 service with Windows Server 2003. My guess is to give the money-tight web administrator a starter solution to work with.
What it has:
Basically you can configure the domain name, multiple POP3 accounts and whether or not Secure Password Authentication (SPA) is to be used. That's about it! Sure you can change the storage folder, the port and logging level but nothing else of significance. In fact, there isn't even a property sheet for the domain name or even the ability to rename a domain.
Note (modification): I received an email from George Sazandrishvili mentioning the ability to support multiple email addresses with the same name. Here is what he said: "Windows POP3 supports multiple email addresses with the same name. In order to use this feature, the authentication mode should be switched to Encrypted Files. Moreover, this method is very good as it eliminates the need to create Windows accounts for each mailbox. "
What it doesn't have:
Note (modification): It seems that the features that I thought it didn't have are incorrect as you'll note from the two modifications (1 above and 1 below). So, I've removed a paragraph that was here.
Although there are limited features, there is a place for this. For the administrator with Windows Server 2003 (all editions) that would like to manage a few email accounts this might be worth considering. The basic features couldn't get easier, with less than a 1/2 dozen settings it's hard to go wrong. The most difficult part for someone new to this is setting up the MX records for the domain name. I found it does what it promises. It doesn't appear to be buggy or have any issues although I only did some preliminary tests. I also found it was fast and wasn't resource intensive.
My overview-recommendation-in-two-sentences: If you have another solution available to you, use it! But if you want to dabble with managing your own POP3 and you have an available Windows Server 2003 machine, try it out, you can't go wrong.
Note (modification): added a comment about catchalls and aliases. Here is what he said: "Windows Server 2003 POP3/SMTP does have catch-all and alias ability. You just need do d/l the scripts from MS. Do a knowledgebase search. I did, it's working fine."
This is a follow-up to my last blog on IIS6 Compression: http://weblogs.asp.net/owscott/archive/2004/01/12/57916.aspx
Chris Neppes from Port80 Software gave me the URL to a white paper the covers not just compression but code optimization* and cache control as well. These three strategies work hand in hand to help speed up delivery of a web page or site. I highly recommend taking the time to read this.
You can find the white paper here: http://www.port80software.com/support/articles/webperformance
I don't want to summarize too much because I can't do justice to his points, but for those looking to further enhance the performance of their server and the delivery of their site to the end user, this is a must read.
* Code optimization in this context isn't referring to less database calls or more efficient coding although that's important. It's referring to removing white space and other characters not needed by the end users. This type of code optimization can be done on static HTML pages as well. Again, my summary doesn't do justice, be sure to read the article for more.
Hold on to your hats folks. If you don't have compression installed on your web server, either IIS Compression or a 3rd party, and you have IIS6.0 and pay for bandwidth you're missing out on something good.
In the day of IIS5 and earlier the compression built into IIS had various issues and was really not worth implementing. To enable compression you would need to go with a 3rd party solution like www.port80software.com or www.xcompress.com. This has all changed in IIS6! At www.orcsweb.com we've been running IIS6.0 compression on some servers for a number of month with few issues, just huge performance and bandwidth benefits. Expect upwards of 4 times the compression which directly translates to bandwidth savings. This means faster loading of pages for the end user also. The only time we had to disable it was for a custom audio application for one of our clients that didn't work with compression. I'll mention at the end of this how to disable compression for an individual site.
First, for those unfamiliar with what IIS6.0 is and how to get it: IIS6.0, short for Internet Information Services 6.0, is the web software that comes with Windows Server 2003. So if you run IIS on W2K3 then you have IIS6.0. If you're wondering if you can get it apart from W2K3, sorry the answer is 'no'.
One of the issues still there today with compression in IIS is that there isn't a nice interface to manage it. It's not as straight forward as other features of IIS. No need to worry though, I'll explain the ins and outs below of how to implement this properly. I set out to implement IIS Compression a number of months ago and had a hard time finding good information about it. I did find one great post here: http://dotnetguy.techieswithcats.com/archives/003475.shtml. I've since jumped into this subject in more depth and have two things to add to Brad Wilson's article. One, an iisreset is required as I'll mention below but also there is another setting that is required for compression to be more practical for dynamic compression. The other link worth bookmarking is: http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/windowsserver2003/proddocs/standard/ref_prog_iaorefcompschs.asp. (I won't make any promises that this link will always work, Microsoft seems to change their links to documents all the time)
To make it easier I'll include everything needed to properly enable IIS Compression below even though I'll repeat what Brad Wilson said.
First, before anything else, backup the metabase. This is done by right-clicking on the server in the IIS snap-in and selecting All Tasks -> Backup/Restore Configuration. The rest is straight forward.
Create Compression Folder (optional)
The first thing I do is create a folder on the D drive where the static file compression will be cached. You can call it anything you want or leave the default of “%windir%\IIS Temporary Compressed Files” if that works for you. The IUSR_{machinename} will need write permission to the folder. If you use custom anonymous users, make sure to assign the proper user. IIS will still work even if the permissions are wrong but the compression won't work properly. Once running, it's worth double checking Event Viewer to see if any errors are occurring that keep IIS Compression from working.
Enable Compression in IIS
- From the IIS snap-in, right-click on the Web Sites node and click on Properties
- Select the Service tab - Enable Compress application files
- Enable Compress static files
- Change Temporary Directory to the folder that you created above, or leave it at it's default
- Set the max size of the temp folder to something that the hard drive can handle. i.e. 1000.
- Save and close the Web Site Properties dialog
Note: The temporary compress directory is only used for static pages. Dynamic pages aren't saved to disk and are recreated every time so there is some CPU overhead used on every page request for dynamic content.
Now for the metabase changes
Now we move away from the IIS snap-in GUI and have to get our hands dirty. (well, as dirty as they can get when dealing with computer software)
Here is where the IIS team either wanted to make things a bit difficult or they didn't get the changes done in time for the final release of IIS6. Actually it's the latter as I've heard rumor that they will be improving on the GUI over time.
Note: If you want to save yourself the hassle of understanding all of this, purchase ZipEnable from Port80 Software. http://www.port80software.com/products/zipenable/. This is a tool that gives you full control down to the folder and file level and embeds itself into the IIS MMC snap-in, making things much easier. I haven't tried this out so I can't attest to it myself but Port80 Software is a company that Microsoft has recommended for years to use if you want HTTP Compression.
There are a couple ways to do this. One is to edit the metabase directly using Notepad and the other is using adsutil.vbs usually found in your C:\Inetpub\AdminScripts folder. I'll explain the direct edit method because I find it's easier to picture and understand what is happening then using a command-line tool.
- Open the metabase located at C:\Windows\system32\inetsrv\metabase.xml in Notepad
- Search for <IIsCompressionScheme
- There should be two of them, one for deflate and one for gzip. Basically they are two means of compression that IIS supports.
- First thing to do is add aspx, asmx, php and any other extension that you need to the list extensions in HcScriptFileExtensions. Make sure to follow the existing format carefully, an extra space will keep this from working correctly. Do this for both deflate and gzip.
- Now for the other thing commonly missed. HcDynamicCompressionLevel has a default value of 0. Basically this means at if you did everything else right, the compression for dynamic contact is at the lowest level. The valid range for this is from 0 to 10. I had the opportunity of receiving an internal testing summary from Chris Adams from Microsoft regarding the compression level -vs- CPU usage which showed that the CPU needed for levels 0 - 9 is fairly low but for level 10 it hits the roof. Yet the compression for level 9 is nearly as good as level 10. I write all this to say that I recommend level 9 so make sure to change HcDynamicCompressionLevel to 9. Do this for both deflate and gzip.
- Just one thing left. There are two settings that required the World Wide Web Publishing Service (WWW service) be reset. One was enabling compression and the other was HcDynamicCompressionLevel. Even with its shortcomings I simply do an iisreset from the command prompt but you can reset the service whichever way you prefer.
That's it folks. I didn't promise it would be easy but hopefully I was straight forward enough in my steps to keep this from being too difficult.
I should mention it is possible to disable or enable compression at the site or sub-folder level. This time I'll be lazy and tell you the adsutil.vbs way to do this but it can be done directly using Notepad and editing the metabase directly if you prefer. From the command prompt enter the following two commands and be sure to replace site# with the siteID that you are changing:
cscript C:\Inetpub\AdminScripts\adsutil.vbs set w3svc/site#/root/DoStaticCompression False
cscript C:\Inetpub\AdminScripts\adsutil.vbs set w3svc/site#/root/DoDynamicCompression False
Note: I was emailed by a reader, David Waters, who noticed a mistake in the Microsoft's documention on adding custom extensions as asked if I would point it out here. The example adds quotes which shouldn't be added. For example, the following:
cscript adsutil.vbs SET W3SVC/Filters/Compression/Deflate/HcFileExtensions "htm html txtnewext"
should instead be
cscript adsutil.vbs SET W3SVC/Filters/Compression/Deflate/HcFileExtensions htm html txt newext
Just remove the quotes and it will run as it is supposed to.
Follow-up blog: http://weblogs.asp.net/owscott/archive/2004/01/16/59594.aspx
Over the Christmas holidays I finished reading A First Look at ASP.NET v2.0 by Alex Homer, Dave Sussman and Rob Howard. I thought I would include my review:
A First Look at ASP.NET v2.0
Alex Homer, Dave Sussman, Rob Howard
A First Look is one of the first, if not the first, books on "Whidbey", the codename for Microsoft's new version of ASP.NET version 2.0. At the time of this writing, ASP.NET v2.0 "Whidbey" is in the Alpha stages and not even released into Beta, but already it has caused quite a stir in the ASP.NET development circles. The latest information I have heard is that Beta 1 will be out at the beginning of May 2004 and Beta 2 will be the first release that includes the "go-live" license so that anyone can host a website using Whidbey.
The first chapter of the book starts out with an introduction to "Whidbey" and briefly covers many of the impressive features, giving a strong "wow factor". The second chapter touches on Visual Studio "Whidbey" and then the rest of the book covers "Whidbey" development and administration in detail. Whidbey is something worth writing about and the authors have done a great job covering the whole spectrum of impressive new features.
So, what type of book is this? It's not just a book trying to show off the ease and power of Whidbey, otherwise I'm sure I would have been bored before too long. It's a book that is both a reference book and a readable book on "Whidbey" that gets into the features in detail and expounds on the properties, methods and other aspects of v2.0. There is really so much to cover that some parts weren't covered at all or only briefly in this book. For example XML and ADO.NET each get their own book. Also, as this is specifically about the changes that Whidbey offers, you won't see mention of anything that isn't new or changed. Again, the target audience isn't the non-ASP.NET developer.
Code examples were generously given, and in my opinion, a good balance of small and simple, offering a foundation to work with. One example where I wasn't disappointed was the Client callbacks, a new Client-Side Script feature. The description of it was good enough but I was itching to see a code example, and sure enough they gave one. Expect to see code examples in Visual Basic for most of the major features and many of the sub-features. There are dozens of great code examples that will at least get your feet wet.
I have to try hard to come up with any cons for this book, but there are two things I noticed. I found that there wasn't consistency throughout the book in regards to documentation, code examples and other reference information. Sometimes you would find reference charts, code examples, even flow charts while other times there is just a quick mention in paragraph form of a new feature or change to an existing feature. This really isn't as bad as it sounds, the features that have the largest target audience and 'wow factor' are well covered. The second thing I wish this had is a better means of locating the reference information. There isn't a cross-reference chart or means to quickly find much of the great information, and when I have used this as my reference guide for some development it look me a while to find what I was looking for. But, with a few bookmarks and a highlighter this could quickly become a great tool to use as a primary reference for development in Whidbey.
One of the things that I appreciated most was the inside knowledge that all three authors offer. They don't just cover specs and reference material, but give information on the goals of the ASP.NET Team on various features, and vision on what will be available in the Beta and final releases of the product. This is one of those books that you don't think twice about its accuracy and correctness. I found the book enjoyable although my wife (a non-developer) started getting sick of me telling her all the details of Whidbey. It's one of those topics and books that you just need to tell someone about and my wife happened to be the unfortunate recipient of my excitement.
I would highly recommend A First Look to existing ASP.NET Developers. The topic is exciting and the authors did a great job of covering the bases in an enjoyable and easy to follow format. Now it's just a matter of waiting until Whidbey is fully released so that development with Whidbey can be shown to the world.
More Posts