# Wednesday, 12 September 2007

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
Ident OrderedSteps   Ident OrderedSteps
1 The first step.   1 The first step.
2 The second step.   2 The second step.
3 The fourth step.   3 The fourth step.
4 The fifth step.   4 The fifth step.
      5 The third step.

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
Ident OrderOfSteps OrderedSteps   Ident OrderOfSteps OrderedSteps
1 10 The first step.   1 10 The first step.
2 20 The second step.   2 20 The second step.
3 30 The fourth step.   3 25 The third step.
4 40 The fifth step.   4 30 The fourth step.
        5 40 The fifth step.

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

Wednesday, 12 September 2007 19:05:57 (W. Europe Daylight Time, UTC+02:00)
# Thursday, 30 August 2007

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... 
  1. 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. 
  2. 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).
  3. 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...

Thursday, 30 August 2007 09:25:41 (W. Europe Daylight Time, UTC+02:00)
# Tuesday, 28 August 2007

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

Thanks Trika.

Tuesday, 28 August 2007 22:35:23 (W. Europe Daylight Time, UTC+02:00)
# Friday, 24 August 2007

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)

Technorati tags: ,
Friday, 24 August 2007 22:44:23 (W. Europe Daylight Time, UTC+02:00)
# Saturday, 18 August 2007

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).

Technorati tags:
Saturday, 18 August 2007 14:27:37 (W. Europe Daylight Time, UTC+02:00)
# Friday, 03 August 2007

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.

Friday, 03 August 2007 20:44:12 (W. Europe Daylight Time, UTC+02:00)

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).

Exploring the Future of Web Development and Management with Internet Information Services (IIS) 7.0 (Level 200)

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
Friday, 03 August 2007 09:57:26 (W. Europe Daylight Time, UTC+02:00)
# Monday, 30 July 2007

Updated 2007-08-03 with added stuff on IIS, WDS and Windows Activation. 

I haven't done much in terms of blogging about my preparations the past couple of days. Mainly because I've taken a more structured approach after I caught myself reading an RFC to prepare for an MCTS exam (see part 5). To keep track of what I did, I use the table below so I can match the skills to be tested to the resources I used. So far I’ve gone about the following route;

Don’t forget you’re an MCSE. Windows Server 2008 is yet another evolution in the Windows Operating System. Your skills will evolve along with it (in other words, there’s only a little real new stuff). Or as Lukas Beeler stated: “An MCSE on 2003 could probably answer 50% of the questions without having touched WS2008”

  • Search resources (find this documented in some of the previous posts).
  • Watch the IPv6 white-paper webcast, followed by selectively reading through the white paper itself. Link (Resource A).
  • Skim / glance trough the reviewers guide (Resource B).
  • Watch the screencasts by Keith Combs (Resource C).
  • Get some hands-on experience with IPv6 (but don’t overdo it). (Resource D).
  • The E-Book, well only the chapters from Windows Server 2008 (Resource E). I haven’t looked at the PowerShell Step-By-Step chapters, as PowerShell is not on the exam.
  • Some background information on Rights Management Services from Windows Server 2003 (Resource F).
  • E-Learning 5934 collection. For a little more detail on the E-Learning and why I didn't add the last clinic, see my previous post.
    • Course 5936 Hindsight, take this after reading the EBook, the clinic is lacking in overview and seems to miss some essential bits (I toke this module before reading the EBook) (Resource G).
    • Course 5937, good clinic but not much new info after having worked with resources B, C and E (Resource H).
    • Course 5938 (Resource I).
  • Windows Deployment Service Role Step-By-Step guide (Resource J).
  • Volume Activation 2.0 Frequently Asked Questions for Windows Vista and Windows Server codenamed "Longhorn"- Beta 3 (Resource K).
  • I’m not sure if "Custom application directory partitions" means the same as in the Windows Server 2003 exams. If yes, check page 5-26 from the MCSA/MCSE Self-Paced Training Kit for Exam 70-291 (isbn: 0-7356-2288-4). Online Chapter 5 included as Resource L.
  • IIS was a story for itself, which I described in my last preparation post. For the table below, I labled this post as Resource M (which means the whole post in general) and some further qualified resources (like M_1, M_2, etc.) with specific links in that post (Resource M).
  • Get some hands-on experience with Server Core (Hindsight, not a priority. Keith’s screencast probably shows enough) (Resource Z).

Resources reviewers guide (B) and E-Book (E) have their respective chapter denoted as well, like (B_2) for chapter 2 from the reviewers guide.

What’s up with the table? First of all, these are the skills being measured from the 2007-05-25 prep-guide with a priority column and a resource column. Each time I encounter a comment on the internet about the skill being heavy tested, it receives a plus. Plusses are direct or inherited from the group, that is; if I felt a comment could be pointed to a group, that’s where the plus landed. Resources point to thing I used, did or read to cover that topic; finally comments are things I want to mention on the particular topic.

One general comment though, the exam is said to heavily focus on command-line tools. So I specifically paid to attention to the command-line tools used in the various topics.

70-649 Priority Resources  Comments
Configuring Network Access    
Configure Remote Access.   B_5, G  
Configure Network Access Protection (NAP) components. + B_5, G  
Configure Network Authentication.   B_5  
Configure data transmission protocols.   B_5, H  
Configure wireless access.   B_5, G  
Configure certificate services. + B_5, E_7, G  
Configure DHCP. + D, B_5, G  
Configure IPv4 and IPv6 Addressing. ++ A, D   
Configure Routing.   B_5  
Configuring Terminal Services +  
Configure Terminal Services Remote Programs. + B_3, I  
Configure Terminal Services Gateway. + B_3, I  
Configure Terminal Services load balancing. + B_3  
Configure resource allocation for Terminal Services. + B_3, I  
Configure Terminal Services licensing. + B_3  
Configure Terminal Services client connections. + B_3, I  
Configure Terminal Services server options. + B_3, I  
Configuring a Web Services Infrastructure +++ B_6 is heavily underpowered to cover the subjects
Configure File Transfer Protocol (FTP) Server. +++ M, M_1  
Configure backup. +++ M,  
Configure Web applications. +++ M, M_4, M_5  
Configure Application Pools. +++ M, M_5  
Configure IIS components. +++ M, M_3  
Publish IIS Web sites. +++ M, M_4  
Migrate sites and Web applications. +++ M, M_5  
Configure SMTP service. +++ M,  
Configure Universal Description, Discovery, and Integration (UDDI) service. +++ M,  
Configuring Security for Web Services +++ B_6 is heavily underpowered to cover the subjects
Configure handlers to reduce attack surface. +++ M, M_3  
Configure .NET Trust levels. +++ M,  
Configure authentication. ++++ M, M_4  
Configure rights. +++ M, M_6  
Configure permissions. +++ M, M_6  
Configure authorization. +++ M, M_6  
Configure certificates. ++++ M, M_2  
Deploying and Monitoring Servers      
Configure Windows Deployment Services (WDS). +++ B_7, J  
Capture WDS images. +++ B_7, J  
Deploy WDS images. +++ B_7, J  
Configure Windows Activation.   C, K  
Create virtual machines. +++ B_2, E_3  
Configure Virtual Server settings. +++ B_2, E_3  
Install Windows Server Enterprise.   C  
Install server core. + C, Z, B_7, E_6  
Configuring Server Roles      
Implement server roles by using Server Manager.   B_7, E_4, E_5  
Configure ADLDS. + B_5, E_7 Formerly known as ADAM (Active Directory Application Mode)
Configure ADRMS. + B_5, E_7, F  
Configure the Active Directory server core. + B_5, E_7  
Configure the read-only domain controller (RODC). +++ C, B_4, H  
Configure Active Directory Certificate Services. ++ B_5, E_7  
Configure Active Directory Federation Services (ADFS). + B_5, E_7  
Maintaining the Active Directory Environment +    
Configure backup and recovery. + B_5, B_7  
Perform offline maintenance. + B_5, E_7, H  
Configure custom application directory partitions. + L  
Configuring the Active Directory Infrastructure +    
Configure communication security for Active Directory. + B_5  
Configure the global catalog. +    

If time is less of an issue, visit the TechCenter which has lots of resources (Step-By-Step guides) to get the knowledge and Hands-On experience.

Monday, 30 July 2007 12:26:27 (W. Europe Daylight Time, UTC+02:00)

Review of the free E-Learning collection 5934 towards preparation of 70-649. Certainly not a catch-all resource, but the first three out of four clinics did add value in my preparation.

Clinic 5936, covers Network Access Protection (NAP). Though the E-Learning doesn’t mention it this way, NAP basically is an extension build around NPS (Network Policy Server, Microsoft’s RADIUS implementation and replacement for Win2k3 IAS). To use NAP, you need clients that are NAP-capable and can validate their Health (think Firewall, AV, Malware protection, patching) with the servers for compliance with the companies System health policy. For better results, combine the E-Learning with the Reviewers Guide sections 5.02 and 5.03. I found this clinic quite lacking in terms of providing a decent overview, but it enhances the Reviewers Guide by adding visualization. 

Clinic 5937, focuses at the branch offices. With Windows Server 2008 this means lots of RODC, but also TCP/IP improvements (for WAN), BitLocker, some administration delegation and stopping the AD Service for maintenance (rather than rebooting the server into Active Directory Restore Mode). Good and useful clinic, but also includes some topics that bear no relevance to the exam.

Clinic 5938, with Terminal Services at the core of this clinic. Listen to the intro and stop wondering why it looks like Citrix (in other words, leverage your experience with MetaFrame or Presentation Server). This clinic throws a lot of different scenarios at you, so you may want to combine it with chapter 3 from the Reviewers Guide to keep an overview. This clinic (like the 5936) adds visualization to the Reviewers Guide.

Clinic 5939, focuses at the “initialization” (initial configuration tasks and adding roles and features) and management of a server. Many topics however, aren’t relevant to the exam (PowerShell, Remote Management, Troubleshooting and Diagnostics). It is a useful clinic in getting to know some new features of Windows Server 2008, but with next to no relevance to the exam. The parts that are relevant to the exam, are already covered by Keith’s screencasts, the EBook and the Reviewers Guide.

Monday, 30 July 2007 12:16:37 (W. Europe Daylight Time, UTC+02:00)
# Tuesday, 24 July 2007

Today I decided my efforts to get hands-on experience with Windows Server 2008 Beta 3 were noble as well as inefficient. To be honest, I don’t really think it’s inefficient in terms of getting to know the product better, but it is in terms of time management towards the exam on August 3rd.

How did I get to that conclusion? I was playing with DHCPv6 and DNS and all of a sudden I found myself reading an RFC (3596 for those interested). What was I doing? Getting DHCPv6 to lease addresses and see those addresses being registered in DNS, both the AAAA records and the PTR records. But I had a little trouble getting the ip6.arpa zone created (and in the end it turned out just to be a matter of knowing what exactly to type in the wizard). That was the detail, but I am also preparing for (just) a MCTS-exam on a broad range of topics and skills.

Anyway, this scenario will be the last “getting my hands in the dirt” for a while. After that, I will look in to the free E-Learning and the free E-Book, probably followed by working my way through IIS7. Based on all info I found, IIS7 is topic #1 on the 70-649.

There is just one possible topic I’m uncertain of: PowerShell. The PowerShell book is recommended Microsoft Press self-paced training products on the Prep-guide. However PowerShell isn’t mentioned in the skills tested and I haven’t read any comments about PowerShell questions in the various experiences. Does any of the 71-649 veterans care to drop a word on PowerShell?

Tuesday, 24 July 2007 22:21:59 (W. Europe Daylight Time, UTC+02:00)