1 / 58

Unlocking the Power of SQL Server Compact

hang
Download Presentation

Unlocking the Power of SQL Server Compact

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    2. Notes (hidden)

    3. Unlock the Power of Ginny Caughey Device App Dev MVP Carolina Software Session Code: DAT325

    4. Agenda Where Does Compact Fit Within SQL Server SKUs What Makes Compact Unique Deployment Demo How To Deploy & Version Local Data Scripting Demo How To Secure Local Databases Security Demo How To Leverage Your Skills Managed Sprocs Demo How To Make Compact Perform Performance Demo What Else Can You Do Office Integration, Trace Listeners, Custom Doc Format What’s New for SQL Server Compact 3.5 Wrap up

    7. Private Deployment What good is a database if you can't deploy it? demo

    8. Deploying Target = Any Change Managed Provider reference to Copy Local = true Include both 32 and 64 bit Compact engine dlls Place 32bit version under an X86 Place 64bit version under AMD64 AMD64 includes Intel X64 Folder Name based off:

    9. Script-Based Deployment How to Deploy and Version local databases …as well as the entire app stack… SqlCeResultSet demo – mobile and desktop, using query processor and ISAMSqlCeResultSet demo – mobile and desktop, using query processor and ISAM

    10. Types of Local Data Read Only, Reference Data Is it 100% static for the life of a version? Are there changes? (list of States, Product Catalog?) Is it 100% the same for all users? User Data Each user has “their data” and they make changes Inserts, Updates, Deletes

    11. Application Deployment

    12. Initial Deployment demo How to get the first database deployed SqlCeResultSet demo – mobile and desktop, using query processor and ISAMSqlCeResultSet demo – mobile and desktop, using query processor and ISAM

    13. Deploying V2

    14. Moving to V2 demo How to version and alter user data SqlCeResultSet demo – mobile and desktop, using query processor and ISAMSqlCeResultSet demo – mobile and desktop, using query processor and ISAM

    15. Deploying V3

    16. Versioning Components Version services, supporting multiple at a time Replace local DLLs Enhance Data, as it contains user data Need a database version # to track needed changes Data Access Methods must be versioned as well

    17. Database Deployment Deploying “Data Files” are problematic Users “alter” the data in the data files How to update a file that’s been altered by the user? Local Databases aren’t “code”, their “user data” Local Databases contain Schema & Data Schemas are “fixed” per application version Data is dynamic per user ClickOnce will replace your “data files” Previous version moved to a .pre folder Only one previous version is maintained… Don’t use ClickOnce to deploy “user data” files

    18. Securing SQL Server Compact version 3.5 Password Protection Database encryption Platform Default Engine Default PPC2003 Compatibility File system encryption through the Windows Encrypted File System (EFS)

    19. PasswordManager Class How to create and securely store passwords for use with encrypted databases… SqlCeResultSet demo – mobile and desktop, using query processor and ISAMSqlCeResultSet demo – mobile and desktop, using query processor and ISAM

    24. Securing Databases with PasswordManager Class demo Managed stored procedures demoManaged stored procedures demo

    25. Leveraging your ADO.NET & Server Queries Leveraging the CLR for scalable business logic & Managed Sprocs Sqlmetal, Linq to Entities, Linq against SqlCeResultSetSqlmetal, Linq to Entities, Linq against SqlCeResultSet

    26. Sprocs - Religion or Science? Typical Reasons for Sprocs Encapsulation Control access to the database Abstraction Ability to change the internals w/o breaking apps Performance Compiled query plans Questions using SQL Server Compact Common Programming Model - no sprocs Reuse – how to reuse the sproc queries

    27. Partitioning the Layers How do you connect to the database? 2 Tier?

    28. Partitioning the Layers How do you connect to the database? N Tier, through some service layer

    29. Challenges w/using Compact

    30. Managed Sprocs Leveraging the CLR for scalable business logic & Managed Sprocs demo Sqlmetal, Linq to Entities, Linq against SqlCeResultSetSqlmetal, Linq to Entities, Linq against SqlCeResultSet

    31. Performance Eeeking out that extra bit of performance

    32. Cached Commands New Commands Each Time Caching Commands Parameterized Commands Properly Parameterized Commands

    33. Fast Queries with Compact demo Similar best practices apply to SQL Server Compact Northwind Excel demoNorthwind Excel demo

    34. "Bulk Load" Performance

    35. Deferred Index Creation

    36. Developing for Office with SQL Server Compact Recipe demoRecipe demo

    37. Developing for Office with SQL Server Compact Use Visual Studio Data Source wizard and drag data controls to your document

    38. Developing for Office with SQL Server Compact Use Visual Studio Data Source wizard and drag data controls to your document OR Just write a little code …

    39. Developing for Office with SQL Server Compact demo Recipe demoRecipe demo

    40. Using Trace Listener to Gather Information in the Field Recipe demoRecipe demo

    41. Trace Listners Thousands of clients, each with their own problems How to get a view into what’s happening in the field TraceListeners enable logging TraceListeners are synchronous All Write calls must complete How to get that data “back home” How to log network issues if the network is down? How to log quickly

    42. Monitoring Client Health

    43. Web Service TraceListner

    44. SQL Server Compact TraceListner

    45. SQL Server Compact TraceListner

    46. Using Trace Listener to Gather Information in the Field demo Recipe demoRecipe demo

    47. Custom Doc Formats Structured document Single file, code free, email attachment safe Post “Documents” to SharePoint Create custom extensions, map to your app Password protect, requiring users to open documents w/your app File Associations Supported by Visual Studio 2008 & ClickOnce

    48. Using a SQL Server Compact Database as a Document demo ClickOnce, w/.NET FX 3.5 enables file associations Recipe demoRecipe demo

    49. Summary SQL Server Compact It’s free, fast, safe/encryptable and compact It’s easy to program and manage Single file, code free, doc centric file format It’s an alternative to JET, XML persisted DataSets, SQL Server Express It’s integrated into: Visual Studio 2005 SP1 Visual Studio 2008 (Orcas) Adds Sync Services for ADO.NET SQL Server Management Studio 2008 (Katmai)

    50. Q & A

    51. Related Content MBL09-TLC Achieving Global Scalability with Mobile Merge Replication via Republishing – Rob Tiffany Tuesday 3:00 MBL403 Building Custom Sync Providers for the Microsoft Sync Framework – Andy Wigley Wednesday 2:45 MBL07-TLC Microsoft Synchronization Services for ADO.NET: From Desktops to Devices – Liam Cavanagh Friday 8:30 TEO Panel 24 Building Offline Enabled Applications Panel Discussion - Wednesday 1:00

    52. SQL Server Compact Resources SQL Server Compact www.Microsoft.com/SQL/Compact SQL Server Compact Edition Q&A Blogs.msdn.com/SqlServerCompact Blogs.msdn.com/SteveLasker Blogs.msdn.com/Laxmi Books Microsoft SQL Server 2005 Compact - Prashant Dhingra HitchhikerGuides.net - Bill Vaughn Tools Primeworks-mobile.com/Products/DataPortWizard.html http://www.gui-innovations.com/html/remotesqlce.html Microsoft Synchronization Services for ADO.NET MSDN.Microsoft.com/Sync www.SyncGuru.com blogs.msdn.com/Synchronizer Please customize this slide with the resources relevant to your sessionPlease customize this slide with the resources relevant to your session

    53. 3.5 Enhancements Data Access LINQ to SQL (Desktop) LINQ to Entities (Desktop, SP1) System.Transactions (Desktop) Upgrade API SqlCeEngine.Upgrade() Engine Enhancements Timestamp (RowVersion) datatype Nested query in SELECT FROM clause CROSS and OUTER APPLY, CAST, DECIMAL, TOP(n) SET IDENTITY INSERT Updated Encryption Native 64bit Synchronization Sync Services for ADO.NET (Desktop in RTM, Devices in SP1) Microsoft Sync Framework

    57. PowerPoint Template Subtitle color Example of a slide with a subhead Set the slide title in “title case” Set subheads in “sentence case” Generally set subhead to 36pt or smaller so it will fit on a single line The subhead color is defined for this template but must be selected; In PowerPoint 2007, it is the fourth font color from the left

    58. PowerPoint Guidelines Font, size, and color for text have been formatted for you in the Slide Master Use the color palette shown below See next slide for additional guidelines Hyperlink color: www.microsoft.com

    59. Table Format

    60. Sync Technology Roadmap

    61. Tools For SQL Server Compact

    62. ConnectionString Name/Value Pairs

    63. Using SQL Server Compact in IIS Not intended for ASP.NET Backend Database Better than other embedded solutions But not meant to replace with SQL Server Intended for Server Side Creation of .SDF files Large Datasets: Faster to create a file then send the completed file rather then create an empty file and sync

More Related