My torn data pages - Sunday, September 30, 2007

Seems I wasn't the only disappointed person who found out WLinstaller sucks on x64. No worries though, I won't have to hack the WLinstaller to get the .msi on my machine. The installer for Windows Liver Writer Beta 3 can be found here, along with those for the other products in the Live Suite.

Almost makes you wonder if the Live Team messed up the installer, or intentionally excluded x64 to promote SkyDrive.

No big deal really and since I already toke some time to work on my server, I figured I might do the other outstanding chore too; upgrading the blog-engine to dasBlog 2.0.7226. I did a test upgrade on my (virtual) dev-server first. No real problems, just the an overzealous DasBlogUpgrader.exe (but you can prevent that by taking a good look at the badWords in DasBlogUpgrader.exe.config and removing the appropriate words from that list (like where free set out to delete some referrer-url’s that were okay)). Upgrade instructions are pretty straight forward, but when hitting the uploading section you need to run the difference check on the /config/site.config file too (and not just on the /web.config). ASP.NET issues were not applicable in my case, as I was already running dasBlog 1.9.7067 on ASP.NET 2.0.

One other nice feature, dasBlog now comes with a Windows Live Writer manifest file (wlwmanifest.xml), giving you some admin shortcuts from Live Writer (or take a look before you try).

Speaking of WLW, it seems a new version (Beta 3) was released at September 5th ... save this post as draft ... download new version ... well forget just about that for a moment ... I get the message stating no Windows Live products can be installed on my OS (x64 Windows Vista).

Also Microsoft's web servers are trying to be smart again about the version you should download; Dutch version: http://g.live-int.com/1rebeta/nl-nl/WLInstaller.exe , US-English version: http://g.live-int.com/1rebeta/en-us/WLInstaller.exe. So just download the language-culture you want by substituting the path... now just waiting for the confirmation of the problem on x64 (which you can find here, semi-official).

Okay, I had the occasional SPAM message hitting my inbox, like every 1 out of 10 e-mails. But over the last couple of weeks that ratio seemed to flip. I had to do something in order to take back control; take some time for it now in order to save some time in the future. Since I don't believe in filtering in the inbox (or server based filtering of content for that matter), it had to be a solution at the receiving end of the server. Also, blocking at the receiver alerts the sender of a false positive that the e-mail they send will not be read (non-delivery error) which on the long run is far less intrusive to communication than silently dropping the false positive.

Building on past experience as systems administrator (I'm talking about the year 2003 now), we had about 97% of all incoming mails being either SPAM or addressed to no-existing mailboxes. For e-mail security we used MIMEsweeper, but it first received the mail (receiver service) and then processed it according its policies (security service). This way of working was both overloading the server and producing silent false positives (not many, but still). In order to fight SPAM more efficiently and be able to better spot false positives, we needed a solution capable of denying access to the server... and we found just the product to do that: Open Relay Filter by Vamsoft. This enabled to both block blacklisted servers at the door and reject mail for non-existing recipients, and instead of having to add another mail-gateway to support the overloaded server, utilization levels of the mail-gateway dropped to an acceptable level.

About 2 years back, Vamsoft invited MCT's to sign up for ORF for free, I jumped on the offer and today that offer really helped me out!!! Installation and configuration on my Small Business Server just toke a few minutes (about 25% of time compared to blogging about it).

The IDENTITY property on a column in SQL Server allows to easily create a new number for each row added (INSERT). As a side effect, the column with the IDENTITY property also shows the natural order of records... until something is forgotten and is INSERTed at a later stage. The natural order of things is now disturbed (or has become fragmented, if you like).

CREATE TABLE OrderOfThings_1(
   Ident int IDENTITY(1,1) NOT NULL,
   OrderedSteps varchar(50) NOT NULL)
GO
INSERT OrderOfThings_1 (OrderedSteps) VALUES ('The first step.')
INSERT OrderOfThings_1 (OrderedSteps) VALUES ('The second step.')
INSERT OrderOfThings_1 (OrderedSteps) VALUES ('The fourth step.') -- Notice the forgotten third step.
INSERT OrderOfThings_1 (OrderedSteps) VALUES ('The fifth step.')
GO
SELECT * FROM OrderOfThings_1 ORDER BY Ident
GO
INSERT OrderOfThings_1 (OrderedSteps) VALUES ('The third step.') -- The forgotten third step is added.
GO
SELECT * FROM OrderOfThings_1 ORDER BY Ident
GO

Naturally we could have anticipated this scenario and set IDENTITY(10,10). But still if the thid step is to be inserted in natural order, this can only be done with significantly more effort. It requires the use of SET IDENTIY_INSERT dbo.OrderOfThings_1 ON, after which the third step can be INSERTed "in order". As the front-end application likely isn't coded for this scenario, it will require the intervention of a dba.

So it would be nice to be able to specify the natural order if needed only, and the best thing in SQL Server to just allow that is the DEFAULT. But the DEFAULT is quite limited; it can neither hold references to columns in the table nor make use of user-defined functions. So any flexibility should come from system functions, literals and operations. By using the IDENTITY property on one column and deriving the natural order from the IDENTITY with a DEFAULT based on IDENT_CURRENT, a flexible and transparent solution is available;

CREATE TABLE OrderOfThings_2(
   Ident int IDENTITY(1,1) NOT NULL,
   OrderOfSteps bigint NOT NULL
     CONSTRAINT df_OveridebleIdentity
       DEFAULT (IDENT_CURRENT('OrderOfThings_2') * 10),
   OrderedSteps varchar(50) NOT NULL)
GO
INSERT OrderOfThings_2 (OrderedSteps) VALUES ('The first step.')
INSERT OrderOfThings_2 (OrderedSteps) VALUES ('The second step.')
INSERT OrderOfThings_2 (OrderedSteps) VALUES ('The fourth step.') -- Notice the forgotten third step.
INSERT OrderOfThings_2 (OrderedSteps) VALUES ('The fifth step.')
GO
SELECT * FROM OrderOfThings_2 ORDER BY Ident
GO
INSERT OrderOfThings_2 (OrderedSteps, OrderOfSteps) VALUES ('The third step.', 25) -- The forgotten third step is added.
GO
SELECT * FROM OrderOfThings_2 ORDER BY OrderOfSteps
GO

Note that the OrderOfSteps column is based on bigint, which can hold any int multiplied by 10.

I don't make a habit of copying other people’s blogs, but after my prep-series for 70-649 / 71-649, it’s okay for me on this one. The original can be found at Trika’s blog.

Hi. You probably already heard the update on WS2008 release to manufacturing (RTM), now scheduled for 1st quarter of 2008 instead of end of year 2007. As a result...  The transition exams 70-648 and 70-649 will be available on October 29, 2007, now. They were scheduled for September 20, but the changes/slip in technology mean some items on our exams are affected, too.  If you took the beta for either of these exams (71-648 or 71-649), your result should be available no later than October 29 (or a few weeks before). The MCTS exams are still scheduled for RTM +30 days; the MCITP exams are still scheduled for RTM +60. Don't know what I'm talking about? Read about the WS2008 certification family.

Guess I have to wait for my beta-score a little longer...

It's offical and now available, passed exams stay on the transcript (thank goodness Microsoft for that)

Thanks Trika.

SQL Server Books Online states there are a number of statements you can't include in a stored procedure, including CREATE VIEW. In most cases that wouldn't be a problem, as you can create the view using a script, unless... I need an indexed view on a table that is created by a stored procedure. At the time the script runs to create the objects (views, functions and stored procedures), I can't create a schemabound view on a non-existing table. I have to create the view after the table, but if I try in a stored procedure; error messages!

CREATE PROCEDURE procCreateIndexedView
AS
IF EXISTS (SELECT *
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = 'vwDemo1Only'
                AND TABLE_TYPE = 'VIEW'
                AND TABLE_SCHEMA = 'dbo')
DROP VIEW dbo.vwDemo1Only
CREATE VIEW dbo.vwDemo1Only
    WITH SCHEMABINDING
    AS
    SELECT Col1, Col2, Col3, Col5
      FROM dbo.tblDemo 
      WHERE Col4 = 1
CREATE UNIQUE CLUSTERED INDEX ix_Demo1_Col1Col2 ON dbo.vwDemo1Only(Col1, Col2)
CREATE INDEX ix_Demo1_Col5 ON dbo.vwDemo1Only(Col5)

Msg 156, Level 15, State 1, Procedure procCreateIndexedViews, Line 9
Incorrect syntax near the keyword 'VIEW'.
Msg 319, Level 15, State 1, Procedure procCreateIndexedViews, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Fortunately, there is an escape: Dynamic SQL. Simply by encapsulating the creation of the view as dynamic SQL, the stored procedure is created without errors. Also, when executing the stored procedure the view is created as well (that is, if the table already exists )

CREATE PROCEDURE procCreateIndexedView
AS
IF EXISTS (SELECT *
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = 'vwDemo1Only'
                AND TABLE_TYPE = 'VIEW'
                AND TABLE_SCHEMA = 'dbo')
DROP VIEW dbo.vwDemo1Only
--  Add next line to encapsulated view defenition as dynamic SQL.
EXEC('
CREATE VIEW dbo.vwDemo1Only
    WITH SCHEMABINDING
    AS
    SELECT Col1, Col2, Col3, Col5
      FROM dbo.tblDemo
      WHERE Col4 = 1
')
-- Add previous line to stop encapsulation.
CREATE UNIQUE CLUSTERED INDEX ix_Demo1_Col1Col2 ON dbo.vwDemo1Only(Col1, Col2)
CREATE INDEX ix_Demo1_Col5 ON dbo.vwDemo1Only(Col5)

What's on your sidebar? Well, nothing much on mine, until today. I just read a post on Rob Farley's blog, pointing to SSIS Junkie Jamie Thomson. Jamie and his colleague John Rayner developed a useful gadget, where you can connect to your favorite database and monitor file size and usage. Actually you see the sum of your data files sizes and the sum of your log files sizes (if your database contains more than one of each). Easy to use, just type the server name, database name and the polling interval and you're set (Jamie's original post has the instructions as screen capture).

You can have this gadget more than once on your sidebar with different connections, but I would like to suggest to Jamie and John to allow for a list of databases to be polled in a single gadget (you can still keep multiple gadgets on your sidebar, one for every server / instance).

Well, I should say 71-649, because I sat the beta-exam. But how would I rate my preparations (1, 2, 3, 4, 5, 6, 7, 8) and the exam?

Let me start with the exam, 88 questions on:

• Windows Deployment Services, about 10 questions.
• Terminal Services, about 10 questions.
• Internet Information Services, about 20 questions.
• Active Directory, about 20 questions.
• Networking, about 10 questions.
• Virtual Server, about 5 questions.
• Not listed in the prep-guide, about 10 questions. These topics include Disk management, WSUS, Clustering, Recovering from boot errors; none of them really hard. I would say (apart from the different boot process and recovery options for Windows 6) basic knowledge is sufficient.

Mapping my preparation to the beta-exam, I can say IIS and Networking were well covered. Though there is one flaw on my IIS prep: .NET Trust Levels… I totally forgot about them.

My feeling on WDS and AD in general is okay, though I should have spend more time on Federation Services and Rights Management Services and gotten some hands-on experience with WDS. Towards Virtual Server, I can say I underestimated it a bit, thinking that my daily usage of Virtual Server 2005 for test and development would cover it. Not, you’ll need to invest in your skills to manage a production environment of legacy OS-es hosted on Virtual Server, including securing (the level of) access to specific machines and scripts.

And then there were Terminal Services, well actually my exam started with them and I was shocked (or maybe stunned) with the level of depth and detail in the questions. Maybe, like with Virtual Server, I underestimated TS. But with VS, I had at least the feeling the questions were fair, some the TS question however were IMHO based on look-up facts, not skill. If the spread of the exam will be the same as on my beta, prep deep and hard at ALL topics on Terminal Services.

That said about my preparation, but will I pass? Hard to say, first of all it’s a beta exam, so it’s also a test for the question pool (and some won’t make the cut). There were errors in at least two questions (which I commented) and I have my doubts about a couple of others (I’ll review what I can remember and answer that on Microsoft’s follow-up mail on the beta exam). Until then, I’ll anxiously await the result.

In a few hours I’m going to find out if my preparations (and expectations) match up with the exam (or should that be the other way around?).

Anyway, here is the final post on the preparations, covering IIS. On top of that, I’ve updated Preparing for 70-649, part 7 of many with the IIS stuff and some extras on activation and WDS.

IIS is huge and not only in terms of its share in the question pool (as reported in many experience reports in on the Internet). Surely I’m pointing at IIS.NET (www.iis.net), even than a sub selection is required. So let me sum up the resources I used, though I must admit I had next to no clues on what to prepare for other than a lot of command-line stuff, in other words: appcmd.exe.

First I had to get in the mood ;)… so I picked two webcasts (I had their links stored sometime when I was browsing resources).

Live From Redmond: Putting the Lego set together: Inside IIS 7.0's Componentization

There is an audio problem in the original webcast starting just after 18 minutes and lasting for about 2 minutes, nothing wrong with your PC (yes, I did restart the presentation).

I was tempted to only view the admin part of the webcast (~50 minutes), but sitting through the full webcast gives you a good view of what the modularized approach for IIS 7 means in terms of extensibility.

After the webcasts I went through the IIS 7 Resources and read all articles (1, 2, 3, 4, 5, 6, 7, 8) in “Explore IIS 7”. Just to get the complete picture.  A lot of these pages have a “Learn more … ” as their next/last page. This “Learn more” page has undoubtedly useful links, but after having clicked a few I decided to keep away from them to properly manage my time. Note that having viewed the webcasts makes the reading easier.

Basically I wanted to continue reading the rest as well, but that would present an information overflow, which would probably not be relevant to the exam. I already had my doubts if I wasn’t drilling too deep anyway. Looking at the skills in the prep guide, 14 out of 16 skills towards IIS are configuring. What I learned so far from the resources; configuration is stored in XML files machine.config, applicationHost.config and web.config. What I learned from the comments, emphasizing the importance of the command-line, appdom.exe will be the tool to edit these XML files.

I started taking up the configuration tasks with FTP, based on the 9-page guide from iis.net. In this paper the configuration is done against the bare XML for several different scenarios. In preparation terms, I’ll label this link Resource M_1.

Next was configuring certificates, where I was surprised to learn that appcmd.exe is could not be used for a lot of certificate related configuration tasks (Resource M_2).

This link might address two skills, as I’m not sure to what extend the words components, modules and handlers are used interchangeably (Resource M_3).

A link that (in a very simple way) satisfies three skills is this one, labeled Resource M_4.

This link will hopefully satisfy another 3 skills (well, one already covered by M_4), labeled Resource M_5.

In the configuration corner for rights, permissions and authorization, you should have gotten a pretty good impression from the second webcast, but here are the four links I think add some information. 1, 2, 3, 4 (Resource M_6).

There wasn’t information on backup. But hey, how hard can that be… check out appcmd backup /?, by now you should know the IIS team got their act pretty well together.

SMTP is another story, I haven’t looked deeper in there, other than just install it. To me it seemed nothing changed from Windows Server 2003, it even requires all the IIS 6.0 bits to be installed. Then again, the prep-guide could be hinting at configuring SMTP so your apps can send mail.

And finally UDDI, well next to nothing to be found on UDDI on the iis.net, at microsoft.com UDDI points you in various developer directions. Also Microsoft, SAP and IBM seemed to have the plugs pulled on the public UDDI business registry. This makes UDDI an enterprise niche, which will require cooperation between developer teams and corporate administrators. In other words, UDDI should have no place in a MCTS exam and I’m going to take my chances here.

All information in the resources (with exception of M_2) focuses at the underlying XML-configuration, so armed with this knowledge I started to test my skills with appcmd.exe in a VirtualLab. Unfortunately I ran into some troubles with the lab (which all by itself should take just a minute or 10 (out of 90) to complete, so I booted my own VM to play appcmd.exe a bit more. The thing I liked in the VirtualLab was the inclusion of appcmdUI.exe. Speaking of appcmdUI, life with appcmd.exe can become a lot easier; check out Kanwaljeet Singla’s appcmdUI.exe, after the exam... don't get used to it yet ;). Or use one of the other options to manage IIS7;

• GUI administration
• Edit the files directly with your favorite XML-editor
• PowerShell
• WMI