Transcript MAS 500 Migration Troubleshooting

MAS 500
Migration
Troubleshooting
Darrick Coles
Development
Manager MAS 500
Agenda
• Introductions
• Overview
• Data tips
• Performance tips
– What is Data Migrator? • A typical migration
– Goals
– Architecture
– Key concepts
• Existing migrations
• Migration preparation
MAS 500 Data Migrator Overview
• Currently supports MAS 90 and MAS 200 versions
3.60, 3.61 and 3.70
– MAS 90 and MAS 200cs migrations use ProvideX ODBC
driver
– MAS 200 SQL migrations can be from a DB on the
same server or any other server
– 3.71 support in MAS 500 version 7.0
• Supports ad hoc migration and is extensible
• Part of Assisted Company Setup
• Multi-step process
–
–
–
–
Extraction
Review, clean-up, etc. of the extracted data
Insertion into MAS 500
Insertion report available for detailed feedback
Assisted Company Setup
Phases
Just follow
the steps
in order
Icons for
available
activities
in the
current step
Steps
Have sales and
implementers
read this—it’s
packed with MAS
90 to MAS 500
migration tips
Enabling an Extraction
If a migration exists,
the Migrate button
will be enabled
If an extraction exists, this
button will be enabled—you
can enable it, too
Goals of Data Migrator
• Provide competitive advantage when upgrading
customers from supported products
• Provide migrations of critical and/or
voluminous data
• Provide informative feedback
• Provide a mechanism to fix problems
• Provide a means for others to plug in
their own migration extracts
• Publish the insertion APIs
• Allow “in-the-field” extension of Data Migrator
• Make the delivery of additional migrations easier
• Enable customers to modify data using existing,
well-known tools (Excel)
Supported Migration Configurations
MAS 500
Client PC
Source System
SQL Database
Source System
SQL Server
MAS 500
Database
MAS 500
SQL Server
MAS 500
Client PC
MAS 500
SQL Server
MAS 500
Client PC
MAS 90 or
MAS 200cs
Files
Source System
File Server
Source System
SQL Database
MAS 500
Database
MAS 500
Client PC
MAS 500
Database
MAS 500
SQL Server
MAS 500
Database
Other Source
Server
MAS 500
SQL Server
Key Concepts—Staging Tables
• Minimal referential integrity (RI)
– Really, only data type is enforced
• IDs instead of keys—both foreign and
surrogate
• Most enumerated types replaced by
character strings to allow for named
values
– “Yes”, “No” for 0, 1, etc.
• Flattened, de-normalized structure
Other Key Concepts
• Extraction
– Copying data from a source system to our
staging tables
• Staging Table Scrubbing
– Manipulating staging table data—we support:
• By hand, directly in the staging table
• Using Microsoft Excel
• Applying insertion steps before our insertion steps
• Insertion
– Applying business rules to staging table data
and inserting all records that satisfy the rules
Other Key Concepts (cont.)
• Data migration links
– Store information about where the source data
for a migration resides
– Example:
\\CompanyServer\AccountingFiles\MAS90\Data
• Metadata
– Data that helps define functionality
Key Points
• We have an API for every migration
– All have the same interface
• Extractions grab all data at once (usually)
• Insertions process a subset of records and
respond back in a few seconds (usually)
• ACS allows the user to interrupt the
asynchronous insertion process
• We support multiple steps on both extraction
and insertion
• Our extractions are DLLs, and insertions are
stored procedures, but we support both on
either side
So, What Do You Actually Migrate?
So, What Do You Actually Migrate?
Major Entities
•
•
•
•
•
•
GL Accounts*
Vendors* and classes
Customers* and classes
Items*
Inventory
Bill of materials and
routings
• Tax schedules
• Pricing, including:
•
•
•
•
•
•
•
•
– Price sheets
•
– Contract
– Product group pricing
– Customer/product group pricing
•
• Sales commission plans
* Including custom fields
•
•
Customer items
Vendor items
Warehouses
Transactions and balances
Open sales orders
GL transactions
GL account history
Posted invoices (open and
closed)
Posted vouchers (open and
closed)
Inventory cost tiers and bin
quantities
Inventory history
Bank transactions
So, What Do You Actually Migrate?
Other Entities
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Tax classes and codes
Processing cycles
Product categories
Commission classes
Natural accounts
Account segments
Budgets
Sales tax accounts
Journals
Allocations
Banks
Bank accounts
Tender types
Buyers
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Shipping methods
Product price groups
Purchase product lines
Sales product lines
Bins
Vendor payment terms
Sales teams
Sales persons
Sales territories
Customer payment terms
Work centers
Operations
Tools
Manufacturing employees
Migration Preparation
• MAS 90, MAS 500
market space
• Prepare!
–
Read the documentation:
MAS 500 Assisted Company
Setup has lots of information
and hints about the
differences between MAS
90/200 and MAS 500
• Clean up before
migrating
• Know and use
available resources:
sales and
implementation
• Sales preparation
• Sales
– Set expectations
– Know their business
and how they use MAS
90
– What’s their favorite
thing?
– What do they take for
granted?
– Familiarize yourself
with the differences
– Don’t assume this is a
simple upgrade: PLAN,
Preparation: The Differences
Preparation: The Differences
Work Centers
Differences
In MAS 90 and MAS 200, costs are maintained at the operation level; in MAS 500 they
are maintained at the work center level. There are several additional differences
between how
MAS 90 / MAS 200 and MAS 500 handle costs including:
• MAS 90 / MAS 200 does not allow for setup costs
• MAS 500 does not allow for per-piece costs
• MAS 500 does not allow for cost as a percent
Before migrating your MAS 90 / MAS 200 manufacturing data, carefully plan your new
MAS 500 manufacturing system to take the different costing methods into account.
Migration Issues
The work center ID in MAS 500 is created by combining the MAS 90 / MAS 200 work
center and operation codes to create a unique identifier. The full MAS 90 / MAS 200
work center and operation codes are used for the work center description in MAS
500. No schedule information is migrated from MAS 90 / MAS 200.
List of Work Centers and Operations
From Work Order Setup, select Work Center Maintenance, then click the Printer
button. From Work Order Setup, select Operation Code Maintenance, then click the
Printer button.
Choose the Right MAS 500
• Small Business Edition
– Fewer than 10 users
– Microsoft SQL Server Desktop Engine (included)
– Up to four companies and four warehouses
• Standard Edition
– Fewer than 30 users
– Microsoft SQL Server Standard Edition
– Up to ten companies and ten warehouses
• Enterprise Edition
– Unlimited users
– Microsoft SQL Server Enterprise or Standard Edition
– Unlimited companies and warehouses
Set Customer Expectations
• Not a simple upgrade
• What functionality is different
• What functionality is GAP
• What behavior is different
– Fast screen painting in MAS 90
versus fast processing in MAS 500
• Don’t migrate from a “moving
target”
Preparation: Technical
•
•
•
•
•
•
PSG APIs
Other tools
Documentation
Data cleanup
Server prep
Know data and performance tips
– Bring in-house for review and possible
migration
• Backup
• Validate
• Security
Data Tips: What’s Being Mapped
• You’ll find detailed answers to all your
“mapping” questions in the code …
• SQL to find extract routines
–
select name from sysobjects o WITH (NOLOCK) WHERE name like
'spMAS%Ext‘
• What’s in the routines
INSERT StgReasonCode (ReasonCodeID, Description, ProcessStatus,
SessionKey)
SELECT Cancellation_ReasonCode, Description, @NOT_PROCESSED,
@_iSessionKey
FROM #SO_09CancellationCode
WHERE Cancellation_ReasonCode IS NOT NULL
------------------------------------------------------------------------------
INSERT StgSalesTeam(SessionKey,
SalesTeamID, Description, ProcessStatus)
SELECT
@_iSessionKey,
Division,
Description,
@NOT_PROCESSED
FROM #ARB_DivisionMasterfile
WHERE NULLIF(Division, '') IS NOT NULL
Data Tips: Historical
• Closed invoices, vouchers need history
to migrate
– MAS 90 customers often purge
• Due to long history with the product
• 2 Gig MAS 90 file limit
– Purge helps performance (re: reporting)
Data Tips: Quirks?
• MAS 200 SQL upgrade may parse some MAS
90 data that MAS 500 cannot handle
• Can’t figure out what’s going on?
– Ask your “geek” to look at the transform
that’s occurring on extract (‘spMAS%SO%Ext’)
• MAS 90 Custom Fields ≠ MAS 500 Custom
Fields
• MAS 500 APIs are not always appropriate
for ongoing insertion of data into MAS 500
Hey, I Heard that Migrations Are Slow…
...is that true?
Migration Performance Tips
• Extraction
– Be patient—there may be lots of
records
– Do you need all that history?
– Is all that data being used?
• Insertion—monitor the feedback
– Validations occur for each record
– Report gives detailed feedback, but
costs cycles
• Do you need report on good records?
MAS 90 ODBC Driver Performance
Dirty Read:
Cache Size:
Bigger is
better
Check this, but
only do reading
with this
checked!
Burst Mode:
Will reduce
number of
times file
is locked
NOTE: Version 3.7 ODBC driver performs faster than 3.6 and 3.61
See documentation menu: ODBC 3.x at
http://pvx.com/ for ODBC setup
MAS 500 Migration Performance
• Get other traffic/users off the SQL box
if possible
• TempDB is highly utilized in migration
– Make it large before starting the migration
and avoid resizing by small amounts (or any
amount)
– Start with 1 Gig?
• More server memory --> faster migration
• Check if other SQL Agent jobs are running
What’s Involved in a Typical Migration?
• What are some of the issues?
– Sales detects a pain
– Customer decision
– Gap analysis
• Not just an upgrade
• Feature gap
• Behavior gap
–
–
–
–
Project plan
Proof of concept
Migration
Verification/sign off
A Typical Migration
• Set up a migration link to the source
system
• Move through the ACS steps in order
–
–
–
–
–
Backup DB before critical steps
Set up options as necessary
Enter data for steps not having migrations
Run migrations
Compare reports from source and destination
• Pilot the migration to gather any
additional supporting data
MAS 500 Data Migrator Work Flow
Current
Accounting
System
Database
MAS 500
Staging
Tables
MAS 500
Tables
Recap
• Become knowledgeable about the
similarities and differences
• What Data Migrator is and what it isn’t
• Existing migrations
• Data Migrator architecture
• Tips and tricks
IIS
DDRK
TCP/IP
OLTP
HTTP
VB
SDK
MMC
ODBC
DLL
BIOS
ASP
QA
C++
.Net
SCSI
ROLAP
VM
Mouse
WBS
SNTP
DML
ERD
XP
LDAP
SQL
QOS
COM
OOP
FTP
Win32
OLAP
UML
ASCII
ADSI
ANSI
VGA
DDL
AIS
OLE DB
SMTP
WinSock
USB
IE
NetBIOS