MSDE 2'0 Deployment and Troubleshooting Setup Ananth Padmanabham and Yinn Wong Support Professionals

1
MSDE 2.0 Deployment and Troubleshooting Setup
Ananth Padmanabham and Yinn WongSupport
ProfessionalsDeveloper SupportMicrosoft
Corporation
2
Agenda

• Feature overview of MSDE 2.0
• Microsoft Windows Installer basics and merge
  modules
• Embedding MSDE 2.0 into the setup of custom
  applications
• Troubleshooting topics

3
Desktop Strategy

• Bring the power of Microsoft SQL Server to the
  desktop
• Windows 98/Windows Me/Windows XP
• Windows NT/Windows 2000
• SQL Server platform for building scalable desktop
  and notebook database applications across the
  Windows family
• Same product across all Windows platforms
• Lowest cost of ownership and development
• Scale-up from JET
• Collection of SQL Server redistributable
  components

4
Desktop Strategy (2)

• Single user or small workgroup
• Same programming model as server version
• Applications will run on all versions
• Optimized for smaller systems and memory
• Great for laptops
• Mobile/disconnected user
• Throughput and concurrency workload governor

5
Specifications

• 2 GB of RAM maximum
• 5 concurrent workload throttle
• 2 GB database limit
• Limit applies to data (.mdf and .ndf files)
• No limit on transaction log (.ldf files)
• SMP limited to 2 CPUs
• No full-text search no Analysis Services
• No SQLMail no tools (only osql.exe is available)
• No publishing for transactional replication
• Can subscribe to transactional publication
• Full publishing and subscribing for merge
  replication

6
What is Five Concurrent Workload Throttle?

• It is not a five user/connection limit
• It is a workload limit
• Throttling occurs when more than five queries are
  concurrently executing
• Throttling affects all connections
• Detect throttlingis it time to upgrade?
• Errorlog has entries like
• 2000-02-02 110317.20 spid12 This SQL Server
  has been optimized for 5 concurrent queries. This
  limit has been exceeded by 2 queries and
  performance may be adversely affected.
• DBCC ConcurrencyViolation shows a histogram

7
DBCC ConcurrencyViolation
DBCC CONCURRENCYVIOLATION ( DISPLAY RESET
STARTLOG STOPLOG ) Concurrency violations
since 2000-02-02 110317.20 1 2 3 4 5
6 7 8 9 10-100 gt100 5 3 1 0
0 0 0 0 0 0 0 Concurrency
violations will be written to the SQL Server
error log.
5 times (limit1) concurrent statements were
executing
8
What Is New in MSDE 2.0?

• Name change!
• SQL 7 Desktop becomes SQL 2000 Personal
• MSDE 1.0 becomes SQL Server 2000 Desktop Engine
• Windows Installer-based setup
• Multi-instance support
• Feature parity with JET
• Cascaded deletes, functions, extended properties
• Throttling detection
• Plus all T-SQL, XML, DTS, DMO, and replication
  enhancements

9
Windows Installer Basics

• Operating system resident install service
• Standard format for component management
• Windows Installer components
• Windows Installer features
• Windows Installer products
• Windows Installer package file
• Other Windows Installer benefits
• Transacted installation (rollback)
• Source resiliency
• Upgrades and patching
• Operation in lockdown environments

10
Windows Installer Basics (2)
App1.msi
Product
Registry
Windows Installer (Msiexec.exe)
Feature
Feature
File System
Component
Component
Component
Engine.msm
Files
Reg
Files
Reg.
Component
Component
Installer package
Merge module
11
WI and MSDE 2.0 Setup Program

• MSDE 2.0 is Windows Installer-based
• The WI engine is part of the operating system for
  
• Windows 2000
• Windows Millennium Edition (Windows Me)
• Windows XP
• Windows 2000 ships with WI version 1.1 and cannot
  be upgraded to version 1.2
• Windows Me comes with WI version 1.2
• Windows XP ships with WI version 2.0

12
WI and MSDE 2.0 Setup Program (2)

• All of the platforms mentioned above can be
  upgraded to the 2.0 engine
• WI is not included in
• Windows 95
• Windows 98
• Windows NT
• To run MSI packages on the above platforms, you
  must first install the WI

13
WI and MSDE 2.0 Setup Program (3)

• WI engine is included on the MSDE CD in the
  directory \MSDE\msi\
• The MSDE 2.0 SP2 ships the following four WI
  files
• InstMsi.exe
• InstMsiW.exe
• InstMsi20.exe
• InstMsiW20.exe
• The above redistributable WI versions are
  available for free download

14
What are Merge Modules?

• A collection of components with associated
  installation information
• Merge modules are used to deliver
• Shared code
• Files, resources
• Registry entries
• Setup logic to applications as a single compound
  
• file
• .msm files cannot be installed by themselves
• They must first be merged into the application's
  .msi file

15
Merge Modules Illustrated
16
Benefits of Merge Modules

• Track which applications use a shared file
• Support standardized code sharing
• Can easily embed the MSDE 2000 merge modules into
  a custom MSI, allowing the application and MSDE
  to be installed in a single process

17
MSDE 2000 Merge Modules

• Provided for use by independent software vendors
  (ISVs) to integrate SQL Server 2000 Desktop
  Engine into their own custom MSI package
• Located at \MSDE\msm \Msde\msm\1033 (language
  specific)
• Has a set of 25 merge modules
• Optional SQL replication (Repl.msm) and SQL
  Distributed Management Objects (Dmo.msm)

18
Process to Embed MSDE 2000 Merge Modules

• Ways to create the MSI package to install the
  custom application
• Use Orca.exe to author a Windows Installer
  database file (i.e. Schema.msi)
• Use Microsoft Visual Studio Installer to build
  MSI file
• Use third-party tools like Install Shield, Wise,
  etc.
• Adding application feature
• Merging MSDE merge modules into this custom
  application
• Running setup to install the custom application
  and MSDE

19
Using VSI Demo
20
Using VSI Demo (2)
21
Using VSI Demo (3)
22
Using Orca.exe Demo
23
Merging MSDE Into Custom Application

• Validate the MSI package using Orca or
  Msival2.exe
• Merging modules
• No merge conflict errors are raised
• The MSM signature is added to the Modulesignature
  table of the MSI
• The components of the MSM are added to the
  Components table of the MSI

24
MSDE 2000 Setup Properties
25
Troubleshooting MSDE 2000 Setup

• An example of installing MSDE using the Setup.exe
  that ships with MSDE
• Setup.exe /settings c\setup.ini /i
  C\MSDE\Setup\sqlrun01.msi /Lv c\logfile.txt
• Setup.ini file can be used to specify the
  external properties
• Installer writes setup information to Windows
  event logs
• In Windows Installer, the logging option can be
  enabled

26
Enabling logging for Windows Installer

• Can also be turned on by policy
• Create the Logging key and a value in the
  following registry location
• HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Win
  dows\Installer
• Reg_SZ Logging
• Value voicewarmup

27
Enabling Logging for Windows Installer (2)

• Only for troubleshooting purposes
• Adverse effects on system performance and disk
  space
• New Msi.log is created every time you use
  Add/Remove Programs tool in Control Panel
• Letters in the value field can be in any order
• Each letter turns on a different logging mode

28
Command-Line Log Options

• In format of /L iwearucmopv!Log
  file
• Typical /Lv
• But these individual options can be helpful to
  reduce log file size
• w - Non-fatal warnings
• e - All error messages
• a - Startup of actions
• r - Action-specific records
• u - User requests
• c - Initial UI parameters
• m - Out-of-memory or fatal exit information
• o - Out-of-disk-space messages
• p - Terminal properties
• v - Verbose output
• - Append to existing file
• ! - Flush each line to the log
• "" - Wildcard, log all information except for
  the v option. To include the v option, specify
  "/lv".

29
Log File Action Return Codes

• In format of Return Value code
• 1 success
• 2 user cancelled
• 3 unrecoverable errors
• 4 install suspended waiting for reboot

30
MSDE Log Samples

• Verbose logging started 1/28/2002 140427
  Build type SHIP UNICODE 2.00.2600.00 Calling
  process E\WINNT\System32\msiexec.exe
• . MSI (s) (4C34) RunEngine
• Product X\SQLMSDE2k\Setup\sql
  run01.msi
• Action INSTALL
• CommandLine

Products tried to install
Argument(s) to pass into Msiexec.exe
31
MSDE Log Samples (2)

• Action ended 141737 PublishComponents. Return
  value 1.
• Starting custom action SkuIt
• An internal error occurred during install (failed
  to load package id). Contact Microsoft Technical
  Support.
• Action ended 141922 InstallFinalize. Return
  value 3.
• What return values mean
• 0 Action not executed
• 1 Success
• 2 User cancelled
• 3 Unrecoverable error/fatal error
• 4 Suspended, and waiting for reboot

Successful
Fatal Error
32
Callback Function

• Can be used to log SQL-specific exit code or take
  appropriate actions based on return code
• Msiexec /i msdesetup.msi CALLBACKmydll!MyCallback
  Function
• You will have to develop your own DLL

33
Rollback

• Set DISABLEROLLBACK property to 1
• Eg. Msde2.msi DISABLEROLLBACK1 /Le
  c\msde2.log
• It will not rollback after a failed install

34
Reboot

• You can control the reboot action of the system
• REBOOT Force or Suppress or ReallySuppress

35
Common Custom Actions

• Some of the common Custom Actions that can fail
  during the MSDE setup
• ConfigServer.2D02443E_7002_4C0B_ABC9_EAB2C064397B
• InstallSqlRedis.2D02443E_7002_4C0B_ABC9_EAB2C06439
  7B
• InstallDTC.2D02443E_7002_4C0B_ABC9_EAB2C064397B
• These Custom Actions can be seen in the setup
  verbose log

36
Troubleshooting MSDE 2000 Patch Upgrade

• Sqlrunxx.msi matches Sqlrunxx.msp
• How to find out which MSI file
• HKEY_CLASSES_ROOT\Installer\Products\ID_Number
• HKEY_CLASSES_ROOT\Installer\Products\ID_Number\
• Source List
• How to find the product code of the MSI file
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\
• MSSQLServer\Setup
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft
  SQL Server\INSTANCENAME\Setup
• Command line
• Msiexec.exe /p D\MSDE\Setup\sqlrunxx.msp
• REINSTALLALL REINSTALLMODEomus /Lv
  c\MSDE_msp.log

37
References

• Embedding MSDE 2000 Setup into the Setup of
  Custom Applications
• http//msdn.microsoft.com/library/en-us/dnsql2k/h
  tml/sql_embeddingmsde.asp
• Configuring SQL Server 2000 Desktop Engine
  http//support.microsoft.com/support/kb/articles/q
  301/4/13.asp

38
References (2)

• Q287416, ACC2002 Error Message Installing
  Microsoft SQL Server 2000 Desktop Engine
• Q290623, ACC2002 How to Attach an Existing SQL
  Server 2000 Database to SQL Server 2000 Desktop
  Engine
• Q290627, ACC2002 Microsoft SQL Server 2000
  Desktop Engine Is Not Installed by Office XP
  Setup
• Q275307, BUG Installation of SQL Server 2000
  Desktop Engine Fails on Computers That Already
  Have SQL Server 7.0 Tools Installed
• Q274199, INF Cannot Install MSDE 2000 on
  Windows 95

39
References (3)

• Q282017, PRB SQL Server 2000 Help File Contains
  Confusing Information About the Desktop Engine
  Setup
• Q281983, PRB Cannot Specify Instance Name Using
  SQL Server 2000 Merge Modules
• Q285097, INF How to Change the Default Login
  Authentication Mode to SQL While Installing SQL
  Server 2000 Desktop Engine by Using Windows
  Installer
• Q295022, FIX Sqlmaint.exe Is Not Available with
  MSDE 2000
• Q299351, FIX MSDE Installation Fails on Systems
  That Have SQL Server 2000 Service Pack 1
  Installed

40
References (4)

• Q299795, INF How to Author MSDE 2.0 Setup
  Packages with Microsoft Visual Studio Installer
  1.1
• Q306488, FIX Reinstall with Long Command May
  Cause Setup to Fail
• Q307196, PRB SQL Server 2000 Desktop Engine
  Error Message Error 126 loading library
  sqlcax.dll
• Q311762, INF How to Identify Which MSI File Was
  Used for an Existing MSDE Installation
• Q308816, FIX Service Control Manager Shows
  Incorrect Status on SQL Server 2000 Desktop
  Engine

41
References (5)

• Q308856, FIX SQL Server 2000 Desktop Engine SP1
  Setup Fails with Error Message 1706
• Q271070, BUG Desktop Engine and Personal
  Edition Fails When You Upgrade MSDE 1.0 Desktop
  Edition 7.0
• Q315721, INF Can Only Apply SQL Server 2000
  Desktop Engine (MSDE) Service Pack 2 Download on
  the Internet to Instances Installed from
  Sqlrun01.msi
• Q303680, PRB SQL Server 2000 MSDE Service Pack
  1 Setup on Windows 98 Computer Fails with Error
  Message

42
References (6)

• Q306491, FIX Unable To Run The Script Files
  Error Message Occurs with Desktop Engine
  Modification
• Q308212, FIX Connection Failure Errors Occur
  After Upgrade to MSDE 2.0
• Q314881, The Command-Line Options for the
  Microsoft Windows Installer Tool Msiexec.exe

43
Thank you for joining us for todays Microsoft
Support WebCast. For information about all
upcoming Support WebCasts and access to the
archived content (streaming media files,
PowerPoint slides, and transcripts), please
visit http//support.microsoft.com/webcasts/ We
sincerely appreciate your feedback. Please send
any comments or suggestions regarding the
Support WebCasts to feedback_at_microsoft.com and
include Support WebCasts in the subject line.