Exam 70-431

Had a great exam yesterday.  Took me the full 2.5 hours.  I've never done Simulation questions before.  They were... interesting.  I found them quite easy.  The simulator only allowed certain actions to be performed in the SSMS IDE, so finding the right sequence was relatively simple.
35 ordinary questions and 12 simulations.
My study technique this time worked really well.  Each number below stands for the date in February:

1. Picked up the self-paced study kit on February 1st.  Completed Chapter 1: Installing SQL Server 2005
2. Chapter 2: Configuring SQL Server 2005
3. Chapter 3: Creating Tables, Constraints, and User-Defined Types
4. Chapter 4: Creating Indexes
5. Chapter 5: Working with Transact-SQL (very light)
6. Chapter 6: Creating Partitions
7. Chapter 7: Implementing Views
8. Chapter 8: Managing XML Data (very important)
9. Chapter 9: Creating Functions, Stored Procedures and Triggers
10. Chapter 10: Working with Flat Files
11. Chapter 11: Backing Up, Restoring and Moving a Database
12. Chapter 12: Using Transact-SQL to Manage Databases
13. Chapter 13: Working with HTTP Endpoints
14. Chapter 14: Working with SQL Server Agent Jobs
15. Chapter 15: Monitoring and Troubleshooting SQL Server Performance
16. Chapter 16: Managing Database Snapshots
17. Chapter 17: Implementing Database Mirroring
18. Chapter 18: Implementing Log Shipping
19. Chapter 19: Managing Replication
20. Chapter 20: Working with Service Broker
21. Chapter 21: Creating Full-Text Catalogs
22. Concentrated (using practice exam questions) on Exam Objective 1: Installing and Configuring SQL Server 2005
23. Objective 2: Implementing High Availability and Disaster Recovery
24. Objective 3: Supporting Data Consumers
25. Objective 4: Maintaining Databases
26. Objective 5: Monitoring and Troubleshooting SQL Server Performance
27. Objective 6: Creating and Implementing Database Objects
28. On February 28th sat the exam

Name: TS: Microsoft SQL Server 2005 - Implementation and Maintenance
Required score: 700
My Score: 876

Busy Studying for Exam 70-431

Microsoft Certified Technology Specialist:
Microsoft SQL Server 2005 Implementation and Maintenance
Studying via Self-Paced Training Kit by Solid Quality Learning
Chapter 8: Managing XML Data is a killer...

Microsoft UNPLUGGED

Just got back from attending Microsoft UNPLUGGED - 'Web Development with Visual Studio 2008' with Nigel Parker (Auckland's version of Wellington's Darryl Burling).

Highlights included:

• VS 2008 multi-Targeting Support
• Web Designer and CSS Support
• Nested Master Page Support
• JavaScript Intellisense and Debugging
• Code Editing Improvements

I won Windows Live OneCare!  "The All-in-One Security and Performance Service for your Windows XP or Vista PC"

Just in time too, because my McAfee subscription was due to expire this week!

I asked the question "Does all that IntelliSense also go with VBScript"?  The answer was yes.

[wgn-sql-server] Adam's presentation from last night now online

http://turtle.net.nz/sqlug/

Best Practices - SQL Server Reporting Services

Rules to Better Reporting Services 2005

• Adam Cogan
• www.ssw.com.au
• SQL Server User Group

About Adam

• Chief Architect
• Runs teams of developers
• Present .NET User Group, .Sydney
• Speaker for Microsoft Roadshows, Dev Conn, VSLive
• Microsoft regional Director, Australia
• adamcogan@ssw.com.au

Complaints

• VS.NET (resolution: Report Builder)
• Security not very definitive (resolution: make everyone Admin.  Preferable make Security Model same as SQL Server)
• Lack of control over interface (e.g. Textboxes don’t break over pages).
• RTF/HTML
• Easy way to put the RS box on the web
• IIS on the RS box
• No CSS
• Data Bars

Best Practices

• Dev, Test, Production
• Automated Deployment

1. Which User Experience?

• Report Builder
• Web Site (Report viewer control)
• Emails (aka Subscriptions)
• Windows

2. Do you know when to use Reporting Services?

• Good when:
  • Faster to develop read-only reports
  • Has a designer – non .Net developers needed
  • No code – maintenance is easier
  • Groups and totals is easier (in ASP.Net you would need to iterate…)
• Bad when:
  • Licensing
  • Parameters – forced to use built-in controls
  • Query string – when you change the parameters and refresh a report, the values do not appear directly in the query string
  • Can’t separate SQL in to a strongly-typed dataset or middle-tier object like in ASP.Net – if worried about it use stored procs

ASP.NET – the Good

• Can edit data
• Control over parameters e.g. you can page through months using a hyperlink
• Can use query strings to pass parameters for easy URL creation

ASP.NET – the bad

• Higher development time
• No graphs

3. Do you check that “RS Configuration Manager’ is all green ticks? 4. Do you check out the built-in samples?

• Install, Install, Open Folder
• Deploy Adventure Works OLAP
• Deploy Model

5. Do you have your deployment strategy sorted?

• osql.exe
• rs.exe
• www.ssw.com.au/ssw/sqldeploy

Prepare

• Tip: When should this deployment story be sorted?
• Think about being Larry King before an interview…

Do you know the 2 migration options for Access?

• Import Reports
• Save and Interop with SSQ Access Reporter

6. Does your report print and display on the web correctly?

• Tip: Export your report to PDF and do a print preview, so you don’t have to print a lot of testing pages to find out the best page settings.

7. Do you include a useful footer at the bottom of your report?8. Do you avoid using word ‘Report’ in reports?9. Do you underline items with Hyperlink Action?10. Do you show errors in Red?11. 12. Do you show the past 6 months of totals in a chart?13. Do you show data and chart in one?14. Do you avoid using…?15. Are you aware of the common charts in tables problem? 

• Or do you use expressions to show the correct scale on charts?
• Or do you use red for negatives for charts in tables?

16.17. Do you avoid showing change as a percentage?18. Data Layout – do you use alternating row colours?19. Do you have nodes count like Outlook?20. Do you avoid displaying decimal places?21. Do you have consistent height for table rows?22. Do you display zero number as blank in your reports?23. Do you know the best way to show your numbers?24. Do you know too many numbers can be scary? 

• Do you use de-normalized database fields for calculated values?

25. Do you show all the report parameters in the body?

Lap Around MS Visual Studio 2008

Around MS Visual Studio 2008

• Darryl Burling
• Developer Evangelist
• http://blogs.msdn.com/darrylburling
• Darryl.burling@microsoft.com

Features

• Multi-targeting Support
• Base Class Source Code Support

Windows Developers

Designer / Developer Collaboration

· Expression Blend

o Control Templates

o Data Templates

o Animate

o Advanced Layouts

o Triggers

· Visual Studio 2008

o Layout

o XAML Editing

Windows Presentation Foundation

• Split View XAML Editing/Design
• WinForms Interop
• ClickOnce

Client Application Services

• Leverage ASP.Net Services on the client
  • Share user authentication and authorization between Client and Web applications
  • Store user settings on the web
• Client Services infrastructure
  • Handles web services communication
  • Provides offline support
• Blah

Services Enhancements

• WCF Project Templates
• Test Clients
• AutoHosting - just press F5
• HTTP Programming support

Syndication

• Managed programming model for Syndication Feeds
  • Creation – from existing data types
  • Consumption – from external RSS / Atom feeds
• Provided in the System.Syndication namespace
• Unified RSS / ATOM Feeds
• Composes with Web programming model

Web Programming Model

• Simple approach for building REST-based services
• The URI is the API i.e. http://localhost/GetOrderStatus/329301
• UriTemplate maps Uri segments to parameters
• Support for XML and non-XML data

Web Development

HTML Designer & ASP.Net Enhancements

• Expression Web WYSIWY designer
• Greatly improved CSS Support
• Vastly enhanced JavaScript support
• Nested master pages
• New server controls
• ASP.Net AJAX baked in

ASP.NET AJAX Control Toolkit

• Joint project between Microsoft and the community
• A few dozen controls, more to come
• Three goals:
  • Place to get…
  • Etc…

ASP.NET Futures (July CTP)

• First look at more upcoming features for ASP.NET
  • The will ship as a separate download after 2008
• A few features included in it today:
  • <asp:History> control
  • <asp:Diagnostics> control
  • <asp:media> control
  • <asp:xaml> control
  • CSS Control Selectors support in ASP.Net

Dynamic Data Controls

• <h1>Task List App using Oryz</h1>
• <asp:DynamicAutoData id=”Scaffold” DataContext=”LINQModel” runat=”server” />

Office Development

Visual Studio 2008 Tools for Office

• Baked into Visual Studio Professional
• New designers
  • Outlook
  • Ribbon
  • Custom task pane
• ClickOnce support

Creating SharePoint Workflows

• Today – 15 steps
• VSTO 2008
  • Create workflow in VSTO 2008
  • Design the actual workflow code, etc.
  • Press F5

Mobile Development

Mobile Development

• Works side-by-side with Visual Studio 2005
• In-box support for Windows Mobile 4.0 SDKs
• Unit Testing Integration with Visual Studio Team System
• Security Aware IDE
• Device Emulator 3.0
• LINQ

.NET Compact Framework v3.5 – Supported Platforms

• Windows CE 4.2, 5.0, 6.0
• Etc…

Data Access & Programmability

New language features

• LINQ
• Anonymous types
• Implicit types
• + much more…

Visual Studio Team System Product Roadmap

• Capability for building next generation of applications for : Vista, Office 12, Web
• Software Factories
• VSTE DB Pro integration into VS Team Suite
• Updated Language Enhancements

Summary

• WinForms, WPF, WF & WCF
• Web design, JavaScript, CSS
• Office 2007 baked in
• …

SQL Server User Group Meeting

From SQL Server 2000 DTS to SQL Server 2005 SSIS Upgrade and Migration

• Pat Martin
• Senior consultant
• patmar@microsoft.com
• Microsoft Tech-Ed 2006

Session objectives 7 key takeaways

• Session Objectives
  • Explain the migration story for SSIS 2005
  • Describe tools and practices for migration
  • Provide guidance for current engagements
• Key takeaways
  • We will not break existing installs
  • Integration Services is first version of a new ETL product
  • Migration is not perfect
  • Redesign is a better option

Agenda

• From DTS to SSIS
• Upgrade Experience
• Support for Migration
• Migration Packages
• Guidance for DTS Users

Upgrade vs. Migration

• Two products – two questions
• Upgrade
  • How do I upgrade my server without disturbing existing DTS processing?
• Migration
  • How do I bring my existing DTS processing under the new SSIS model?

Upgrade Issues

• Remote server upgrades to SQL Server2 005
• Local server upgrades to SQL Sever 2005
• Continuing to maintain DTS [packages after upgrade
• Side-by-side vs. upgrade

Remote Server Upgrade

• Local DTS packages continue to access an upgraded remote server without interruption
• SQL Server 2000 Admin Tools will not connect to the upgraded server
  • Continue to edit DTS packages on remote file share without interruption
  • Install 2005 tools to edit DTS packages store in SQL Server 2005 msdb

Local Server Upgrade

• Tools install includes DTS 2000 runtime
• DTS packages on server, file system untouched by upgrade
• DTs packages on upgraded server remain available for execution and maintenance
• DTs agent jobs run without interruption
• Apps written to DTs API continue to work
• Server upgrade removes SQL Server 2000 Admin Tools
  • If you need legacy tools, install 2005 tools alongside 2000
  • Repository support withdrawn

Maintaining DTS 2000 Packages after Upgrade

• Use SQL Server 2005 Management Studio to manage DTS packages
• Access via Legacy node in Object Explorer
• Legacy UI components don’t ship with 2005
• Download DTS 2000 Designer Components

Side-by-Side vs. Upgrade

• Upgrade database Server?
  • Avoid updating remote DTS packages and applications by upgrading the database server
  • Run 2000 and 2005 processing in parallel with side-by-side install
• Upgrade Admin tools?
  • DTS and SSIS are essentially separate products
  • Missed…

Support for Migration

• Execute DTS 2000 Package Task
• Migration Wizard
• Upgrade Advisor

Execute DTS 2000 Package Task

• Use the Execute DTS 2000 Package task to invoke DTS packages from within an SSIS 2005 package
• Wrap legacy packages to take advantage of improved SSIS:
  • Looping and sequencing constructs
  • Package security
  • Event handling
  • Logging and error handling
• Embed legacy package in 2005 package for deployment

Package Migration Wizard

• “Best Effort” migration
• Creates new SSIS package leaving original in place
• Migrate what we can convert
  • Variables and control flow
  • Most tasks
• Wrap what we cannot convert
  • Complex Data Transformation Tasks (ActiveX script model)
  • Analysis Services Tasks
• Some constructs not supported
  • Transactions
  • Dynamic Property Task
  • Access old API; script on step

Upgrade Advisor

• Run before upgrade
• Works on server, local or remote, or on file system
• Upgrade Advisor analyses DTS packages and reports on upgrade and migration issues
• Return to report later to review, check off fixes
  • Requires DTS 2000 server
  • Overwrites report

Package Migration Cases

• Workflow
• Simple Data Transformation
• Complex Data Tran
• Self-modifying packages
• Dynamic Properties
• Custom Task

Workflow Packages

• Most tasks migrate seamlessly
  • FTP
  • Execute Process
  • Execute SQL
  • Bulk Insert
  • Copy Objects
  • Send Mail
  • Execute Package
  • MSMQ
  • Transfer Tasks
• Control Flow and Precedents migrate without issue
• Global Variables fully migrate
• ActiveX Script task may require fix-up if referencing old API

Simple Data Transformation

• DGS Data Transformation and SSIS Data Flow object models don’t map one to one
• Goal
  • Migrate all package form Import/Export Wizard

Complex Data Transformation

• Where migration not supported, migration wraps original taks in an Execute DTS 2000 Package Task
  • Script or other transforms
  • Multiphase pump, lookup, etc
• Redesign with SSIS 2005 transformation s for performance
• Add error handling for reliability

(here the battery died)