GPWindow.com - Dynamics GP- GP TECH INFO > SQL Server

Understanding how Microsoft Dynamics GP works with Microsoft SQL Server continued

Thu, 17 Jun 2010 02:10:55 GMT

What is the best recovery model for SQL Server?

Thu, 17 Jun 2010 18:14:34 GMT

From a recent email:

What, in your opinion, is the best recovery model for a GP database? Full? Bulk_Logged? Simple? They all seem to have their pros & cons but I can't find anyplace where someone says this model is better than the other two because of X, Y or Z reasons.

Simple
If you use Simple, transaction logging is disabled. Simply backing up the database will truncate the transaction logs. (If the logs are already too big, run this script http://vstoolsforum.com/blogs/sqlserver/archive/2007/01/11/compact-sql-logs.aspx). So, if the client is OK with their restore point being last night, when the backups ran, then Simple is fine. It's easier to maintain.

Bulk_Logged
I've never used Bulk_Logged, so I can't speak to that.

Full
Usually the client will want to be able to restore a crashed server up to the last hour, so we use Full. Using Full, the act of running a transaction log backup is what truncates the log. So, you're running a backup every evening and running transaction log backups every hour.

Some of the system databases don't support Full, so I usually have two Maintenance Plans - one for the system dbs and one for the user dbs. I never use the 'these specific databases' option because the next time that a database is added to the server, you'll forget to add it to the backup. Choosing the 'all user dababases' option stops that worry.

How to stop the transaction log of a SQL Server database from growing unexpectedly?

Wed, 16 Jun 2010 20:12:50 GMT

Great article by Microsoft Support, check it out here.

Regards,
--
Mohammad R. Daoud
MVP, MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com

Microsoft Dynamics GP and SQL Server Collations

Thu, 17 Jun 2010 18:13:55 GMT

I have seen this question posted in multiple shapes and forms, but could well be rounded up as follows: "I installed Microsoft SQL Server with an Arabic_BIN collation. In addition, I installed Microsoft Dynamics GP on my server and a few clients. All my clients can access the system with no problems. However, I have this one user who 'accidentally' switched his/her Windows locale to English (United States) and is getting all sorts of errors when accessing the system or trying to enter transactions or master records."

This is not at all uncommon, but to put an end to the myths sorrounding this issue it is necessary to understand how collations work in both Microsoft Windows and Microsoft SQL Server.

Windows Collations

Windows collations are collations defined for SQL Server to support Windows locales. By specifying a Windows collation for SQL Server, the instance of SQL Server uses the same code pages and sorting and comparison rules as the Microsoft Dynamics GP client that is running on a computer for which you have specified the associated Windows locale. For example, the French Windows collation for SQL Server matches the collation attributes of the French locale for Windows.

There are more Windows locales than there are SQL Server Windows collations. The names of Windows locales are based on a language and territory, for example, French (Canada). However, several languages share common alphabets and rules for sorting and comparing characters. For example, 33 Windows locales, including all the Portuguese and English Windows locales, use the Latin1 code page (1252) and follow a common set of rules for sorting and comparing characters.

The SQL Server Windows collation, based on the Latin1_General code page and sorting rules, supports all 33 of these Windows locales. Also, Windows locales specify attributes that are not covered by SQL Server Windows collations such as currency, date, and time formats. Because countries and regions such as Great Britain and the United States have different currency, date, and time formats, they require different Windows collations. They do not require different SQL Server collations, because they have the same alphabet and rules for sorting and comparing characters.

In SQL Server, Windows collations are combined with a series of suffixes to additionally define sorting and comparison rules based on case, accent, kana, and width sensitivity.

SQL Server Collations

SQL collations are a compatibility option to match the attributes of common combinations of code-page number and sort orders that have been specified in earlier versions of SQL Server. Many of these collations support suffixes for case, accent, kana, and width sensitivity, but not always.

In SQL Server 2005, you should primarily use Windows collations. This is particularly true if you have a mix of Unicode and non-Unicode columns in your database. Windows collations actually apply Unicode-based sorting rules to both Unicode and non-Unicode data. This means that SQL Server internally converts non-Unicode data to Unicode to perform comparison operations. This provides consistency across data types in SQL Server and also provides developers with the ability to sort strings in their applications that use the same rules that SQL Server uses.

SQL collations, on the other hand, apply non-Unicode sorting rules to non-Unicode data, and Unicode sorting rules to Unicode data, by using a corresponding Windows collation for the Unicode data. This difference can cause inconsistent results for comparisons of the same characters. Therefore, if you have a mix of Unicode and non-Unicode columns in your database, they should all be defined by using Windows collations so that the same sorting rules are used across Unicode and non-Unicode data.

You should use SQL collations only to maintain compatibility with existing instances of earlier versions of SQL Server or to maintain compatibility in applications that were developed by using SQL collations in earlier versions of SQL Server.

There can be differences in performance between Windows collations and SQL collations, but that would be topic for another blog. For now, if you need to change your DYNAMICS or company databases collation, make sure to check back in the future where I will discuss a few methods to accomplish this.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Auto Shrink and SQL Index Fragmentation

Mon, 21 Jun 2010 18:11:20 GMT

Paul Randall is a guy a I listen to in the SQL Server space. Eight and half years on the SQL Server team at Microsoft will do that.

Today Paul dropped a post on Auto Shrink for SQL Server and why it's bad. It causes severe index fragmentation for starters and Paul has the scripts to prove it, so you can reproduce the problem yourself. Read the whole thing here.

Ok so, Auto Shrink for waistline, good. Auto Shrink for SQL Server, Bad. Got it. Now all I have to do is find that Auto Shrink for waistline app.

What does SQL Maintenance really do?

Wed, 30 Jun 2010 08:03:34 GMT

This one comes straight off the Microsoft Dynamics GP newsgroup (even the title given to this article).

Business Situation

During the course of a test upgrade the user came across a situation where records in a Smartlist table were not upgrading at all. In his attempts to have the upgrade bypass the problem, the user deleted the records from the SmartList table in question and the upgrade was able to succeed.

Nonetheless, the user attempted to run a few of the built-in SQL Maintenance routines found under Microsoft Dynamics GP > Maintenance > SQL and came back empty handed. This series of routines did not correct the damaged records and did nothing in aiding the upgrade, which brings us to the question on the subject:

What does SQL Maintenance really do?

This question can be answered by looking at what SQL Maintenance does not do. SQL Maintenance does not take any actions on table records, except of course, when you drop a table :-).

The routines found under SQL Maintenance are designed to perform preventative database maintenance and each option can be explained as follows:

Recompile

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes

Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.

When the Recompile option is selected in the SQL Maintenance window, GP forces the selected tables auto-stored procedures and triggers to recompile by executing the SQL Server sp_recompile statement.

Update Statistics

Update Statistics updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

The Database Engine keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index or indexes to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.

If lots of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated by using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

To see when the statistics were last updated, use the STATS_DATE function from SQL Server.
Drop Table

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. Drop table will not remove the associated table views or auto-stored procedures, hence, it is recommended to run the Drop Auto Procedure option in conjunction with this option. The Drop Table option executes SQL Server DROP TABLE statement.

Create Table

The Create Table option creates a Dynamics GP table (that has been dropped with the Drop Table option or dropped from SQL Server) with the structure defined in the DYNAMICS.DIC dictionary file. When this option is executed, GP will check for the table existence and drop if it already exists. In addition, all table auto procedures (zDP_) will be dropped and recreated and security granted to the DYNGRP role.

Drop Auto Procedure

This option will remove all selected tables auto stored procedures. The zDP_ procedures aid Dynamics GP in retrieving, saving, updating, and deleting records from a table.

Create Auto Procedure

This option will create all selected tables auto stored procedures. If the procedures already exist, they are dropped and recreated.

Other Resources

Developing for Dynamics GP - "What do the zDP auto-generated stored procedures do?", by David Musgrave.

Developing for Dynamics GP - "What is Column desSPRkmhBBCreh?", by David Musgrave.

Microsoft Developer's Network (MSDN) - You can always search SQL Server Books Online for all topics outlined in this article.

Until next post!

MG.-
Mariano Gomez, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

SQL Maintenance blooper

Thu, 17 Jun 2010 02:05:28 GMT

Sometime back I received a call from one of my clients. Client was having some system setup related issue that required a SQL Maintenance to be done. I quickly guided him over phone to go to MS Dynamics GP | Maintenance | SQL and select the required tables. Prior to this, I asked him if he kept a backup of DYNAMICS database and the answer was Yes. However, the system administrator of the client being new to the Dynamics GP environment (which I came to know only later) selected all tables by mistake and hit Drop Tables. Worst part was that there were users working online at this time. More worst was that after this happened, the new system administrator realized he doesn’t have a proper backup. Needless to say, the damage was real quick. All the users working online have been kicked out. There are several companies linked to the DYNAMICS database that got totally messed up. What would I do in this scenario?

I realized there must be some way out. Then, came a flashing idea. Why can’t we try hooking up any old(not so old) copy of DYNAMICS to this. Started searching for the same in the server directories and could find a copy of DYNAMICS that was 2 months older. Client was lazy enough to keep proper backups. I restored this DYNAMICS database to the existing database. Yes, it worked. However, minor data loss in the form of say, a new user record created recently was not there, so, we could quickly create the same. Similar but minor issues like some users who are assigned modified reports security wasn’t out there so have to set it up. Finally, things are back up and running. I just told myself “Oh! Boy..Today is your day!”

Why am I sharing this out here? Is to just let my readers know how scary it can be sometimes, SQL Maintenance, if not done without having proper backups.Which is why I always tell my clients to keep proper data backups prior to doing any SQL maintenance. Specially, these are certain tasks that are recommended to be done with the help of either the Partners or experienced System administrators unless otherwise trust me, You got to pay for it!

UPDATE : Please do see a word of caution on linked Notes from David Musgrave in the Comments. Added on 18/08/2009

Please do see a similar experience shared by Doug, David Musgrave and Mariano from the link in the comments. Added on 19/08/2009

For the Dynamics GP/SQL Junkie

Mon, 21 Jun 2010 18:11:20 GMT

I know there are some SQL junkies out there always looking for the next SQL script for Dynamics GP. You know who you are. Well, take a look at the SQL Scripts area on GP-Dynamics.com.

I just stumbled on this today so I haven't tested any of these. As always, use SQL statements at your own risk.

Oh, try not to overdue it!

Why you should upgrade to Microsoft SQL Server 2008…

Thu, 17 Jun 2010 18:09:47 GMT

Understanding how Microsoft Dynamics GP works with Microsoft SQL Server

Thu, 17 Jun 2010 02:10:56 GMT

The inner workings of Microsoft Dynamics GP on Microsoft SQL Server

Thu, 17 Jun 2010 18:12:51 GMT

Unless you have been working with Dynamics GP from the days of Ctree and Btrieve, it is quite difficult to comprehend why Dynamics GP seems to behave (as in act up) in certain ways on SQL Server -- how would I say this... not quite like your other Windows applications that run on SQL Server.

To understand some of these behavioral issues, David Musgrave brings a two-part series on Understanding how Microsoft Dynamics GP works with Microsoft SQL Server -- if it was my article I would have labeled it Dynamics GP technological idiosyncrasies, but then again, I did not write it. :-)

Part 1 of Understanding how Microsoft Dynamics GP works with Microsoft SQL Server

Part 2 of Understanding how Microsoft Dynamics GP works with Microsoft SQL Server

Ever wonder why the cryptic table names and columns? A lot of it has to do with the origins of the application and the multiple operating systems and ISAM platforms supported back in the 80's and 90's. Know your Microsoft Dynamics GP history... in the early days, long before Microsoft SQL Server, Dynamics GP ran on the Mac OS platform and supported Ctree and Btrieve as file server platforms. I guess at the end of the day, the development team figured it would be a daunting effort to make those tables and columns names meaningful for SQL Server.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

SQL Server 2008 R2 (Code name Kilimanjaro) with GP 10

Thu, 17 Jun 2010 18:14:40 GMT

An Important note if you are planning to setup GP 10 with the recently released SQL Server 2008 R2 - Service Pack 5 for Microsoft Dynamics GP 10.0 required. Service Pack 5 is scheduled to be released in the summer of 2010.

You would get the error - “your current SQL Server is not a supported version” if you try to install GP 10 with SQL 2008 R2.

GP 2010 is supported out of the box - Microsoft SQL Server 2008 R2 - Enterprise Edition or Standard Edition

SQL Server 2008 has better support and integration for the PowerPivot feature in Office 2010 among many other things. People have reporting using PowerPivot with large amount of data. Microsoft says that with enough memory, 100 million rows can be imported.

Some other really cools things about SQL Server 2008 R2

1. SQL Server 2008 R2 can now scale with Windows Server 2008 R2 up to 256 logical processors.

2. Utility Control Point, which enables a centralized view of SQL Server instances and database applications and their utilization across the designated managed server group

3. Utility Explorer that provides a tree view of the servers, and a dashboard with summary information on the servers.

4. A new Unicode compression scheme, USC-2, can save up to 50% of storage requirements with Unicode data.

Check out the SQL Server 2008 R2 Feature Pack here

Video

Microsoft seems to be producing nice videos about everything – See R2 Videos here - http://www.microsoft.com/sqlserver/tour/en/videos.aspx

SQL Server Management Studio Standard Reports

Thu, 17 Jun 2010 18:12:49 GMT

If you have worked with SQL Server Management Studio (SSMS) in either Microsoft SQL Server 2005 or Microsoft SQL Server 2008, you may have inadvertly overlooked one of its key features: Standard Reports.

Standard Reports are Reporting Services (SSRS) reports that can provide all sort of statuses and and information about the database engine and its management components and well as the databases themselves in real time -- the reports are refreshable! When executed, the reports are embedded in tabs within a new SSMS tab. As a consultant, I find these reports particularly useful when attempting to establish the health of a Microsoft Dynamics GP SQL Server installation. I can immediately relay critical SQL Server performance information to my clients and suggest preventative or corrective actions to mitigate the issues, saving them money in the process.

So lets take a look at the available reports by node...

Node	Report
Server	Server Dashboard
Server	Configuration Changes History
Server	Schema Changes History
Server	Scheduler Health
Server	Memory Consumption
Server	Activity – All Blocking Transactions
Server	Activity – All Cursors
Server	Activity – Top Cursors
Server	Activity – All Sessions
Server	Activity – Top Sessions
Server	Activity – Dormant Sessions
Server	Activity – Top Connections
Server	Top Transactions by Age
Server	Top Transactions by Blocked Transactions Count
Server	Top Transactions by Locks Count
Server	Performance – Batch Execution Statistics
Server	Performance – Object Execution Statistics
Server	Performance – Top Queries by Average CPU Time
Server	Performance – Top Queries by Average IO
Server	Performance – Top Queries by Total CPU Time
Server	Performance – Top Queries by Total IO
Server	Server Broker Statistics
Server	Transaction Log Shipping Status
Database	Disk Usage
Database	Disk Usage by Top Tables
Database	Disk Usage by Table
Database	Disk Usage by Partition
Database	Backup and Restore Events
Database	All Transactions
Database	All Blocking Transactions
Database	Top Transactions by Age
Database	Top Transactions by Blocked Transactions Count
Database	Top Transactions by Locks Count
Database	Resource Locking Statistics by Object
Database	Object Execution Statistics
Database	Database Consistency History
Database	Index Usage Statistics
Database	Index Physical Statistics
Database	Schema Changes History
Database	User Statistics
Database	Active Full-Text Catalogs
Logins	Login Statistics
Logins	Login Failures
Logins	Resource Locking Statistics by Logins
Management	Tasks
Management	Number of Errors
Notification	Services General
SQL Server Agent	Job Steps Execution History
SQL Server Agent	Top Jobs

To access a specific report, just right-click on the desired node then choose Reports > Standard Reports, select the desired report. The following is an example of the navigation to the Server node reports.

Lets take a look at some sample standard reports...

Server Dashboard report (server > Reports > Standard Reports > Server Dashboard)

This report provides detailed configuration information including, but not limited to the SQL Server startup time, product version and edition, server collation, the number of processors in used by the SQL Server instance, CPU usage by database, and number of active databases.

Disk Usage by Top Tables report (database > Reports > Standard Reports > Disk Usage by Top Tables)

This reports shows vital table information such as the number of records in the table, the amount of disk space reserved for the table, spaced occupied by data and indexes, and the unused space. This information can be used to plan for disk space optimization and establish whether it will be necessary to increment the number of partitions or relocate the databases.

Backup and Restore Events report

This one is got to be one of the most important database level reports as it shows statistics about the backups completed on a specific database: average time, size of backups, whether the database backup was complete or differential, etc.

I hope you like this SQL Server gem and start to explore these reports. There is valuable information that can be used to administer your Dynamics GP and overall SQL Server environment.

Until next post!

MG.-
Mariano Gomez, MIS, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Weekly Dynamic: SQL Snapshots and Dynamics GP

Thu, 17 Jun 2010 22:07:15 GMT

Sometimes when implementing Dynamics GP you do a lot of backing up and restoring. Large implementations can require multiple test environments, development environments, etc. Sometimes you need to push a large number of transactions through for testing over and over again, restoring the environment each time.

Well all of those backups and restores take time. A faster alternative is to use functionality in SQL Server to take a snapshot of a company. Snapshots have lots of limitations that make them unsuitable for typical backup/restore needs but for activities that require multiple reloads they are a lifesaver. Why? Snapshot restores are fast. Smoking fast. 4 second restores fast.

In a test on an old, single processor machine, the default TWO company and the Dynamics DB took 45 seconds to backup and another 45 seconds to restore. A snapshot took 6 seconds to backup and 4 seconds to restore. What's more, the snapshots don't slow down much as the db size increases. We've seen GP databases that take an hour for a full backup and restore still create a snapshot in under 10 seconds and restore in under 10 seconds.

Unfortunately Snapshots only work on the Enterprise Edition of SQL Server but if you want to know more about both the power and the limitations of snapshots, here is a great overview.

Here is the SQL Code I used to create and restore my test snapshots:

CREATE DATABASE DYNAMICS_dbss ON
( NAME = 'GPSDYNAMICSDat.mdf', FILENAME =
'c:\DYNAMICS_dbss.ss' )
AS SNAPSHOT OF DYNAMICS;
GO

CREATE DATABASE TWO_dbss ON
( NAME = 'GPSTWODat.mdf', FILENAME =
'C:\TWO_dbss.ss' )
AS SNAPSHOT OF TWO;
GO

-------------------------------------------------

RESTORE DATABASE DYNAMICS FROM DATABASE_SNAPSHOT = 'DYNAMICS_dbss'
go
RESTORE DATABASE TWO FROM DATABASE_SNAPSHOT = 'TWO_dbss'
go

[H.T. to Ross Carlson for this]

gptip42day - Upgrading to SQL 2008

Thu, 17 Jun 2010 05:08:49 GMT

I try to stay away from the technical stuff in this blog and keep it oriented toward daily transaction processing and reporting but in my opinion this is worthy of mention, especially if you do a lot of SSRS reporting.

One of my frustrations with SSRS in 2005 and previous versions of SQL is adding a subtotal to a column of data. One would think it would be a simple matter of inserting a formula in the column, much like entering a formula in an Excel spreadsheet, but it's not nearly that easy. You have to enter a significant amount of code to enter a subtotal in a report.

However, SQL 2008 provides more flexibility in grouping, totals and subtotals using the 'Tablix' data region, which replaces the previous Table, Matrix and List elements in prior versions of SSRS.

Going into the details is beyond the scope of this blog but I encourage you to have a look at the Tablix feature in 2008 if you use SSRS. It makes entering subtotals a piece of cake.

How to transfer data between two Microsoft Dynamics GP companies using Microsoft SQL Server Import and Export Wizard

Thu, 17 Jun 2010 18:12:50 GMT

The Ins and Outs of Dynamics GP on SQL Server

Thu, 17 Jun 2010 22:07:15 GMT

David Musgrave has a great new post up on Dynamics GP and SQL Server. In it he explains how GP works with SQL Server and why some of the concepts (like dex_row_id) exist.

Weekly Dynamic: SQL Data Mining Add In

Thu, 17 Jun 2010 22:07:15 GMT

Despite the Performance Point setbacks, Microsoft BI marches forward. The latest cool tool is also free. Microsoft has released its Data Mining Add In for SQL Server. While not necessarily GP specific it should work just fine against GP databases and cubes. I could spend a whole bunch of bits here telling you about it but why don't you just go here and see for your self how cool this is.

One caveat, this requires Office 2007 and SQL Server 2008 so some of you may not be ready for it yet.

Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM

Mon, 16 Aug 2010 03:00:20 GMT

I have been involved for over the past 6 months with an extensive project requiring complex integrations between Microsoft Dynamics GP 10.0, Microsoft CRM 4.0 and other custom operational systems. In the process of designing and implementing these integrations the client requested a very easy to use interface that could be maintained without having to hire an army of developers or even specialized resources.

The mission: insert/update customer addresses and inventory items from Microsoft Dynamics GP into Microsoft CRM's Product and Customer Address entities. The client also requested the integration be done using the Microsoft CRM web services in order to ensure upgrade support.

Background

Beginning with SQL Server 2005, the components required to develop basic CLR database objects are installed with SQL Server. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:

System.Data
System.Data.Sql
Microsoft.SqlServer.Server
System.Data.SqlTypes

You can find more information on SQL Server CLR integration over at MSDN. Be sure to check the following articles:

Overview of CLR Integration
CLR Stored Procedures

Solution

The solution can be broken down into two parts:

1. Creating the assembly with the CLR stored procedures that would in turn instantiate the CRM web methods to open a connection and insert or update the Product and Customer Address entity records.

2. Configuring Microsoft SQL Server and registering the assembly, creating the triggers on the RM Customer Address Master (RM00102) and Item Master (IV00101) tables that would invoke the CLR stored procedures to pass the Microsoft Dynamics GP records.

This week's series will outline the solution with the code to achieve this. The following topics will become available on the day of their release:

08/18/2010 - Creating a CLR assembly and working with CRM web methods

08/20/2010 - Configuring SQL Server and creating table triggers

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

It Pays to Know a SQL Expert, Especially if you are not!

Thu, 17 Jun 2010 02:08:27 GMT

As consultants, we all have aspects of our work that are more frustrating than others. Migration of data is one of those tasks that I prefer to do alone...I tend to swear a lot and it isn't pretty! Last week I had a payroll migration that was one of the most hairy to date and I have been doing this for years.

It started out very vanilla. After all, it doesn't matter the quantity of data...it is all about the quality, right? I only needed current year employee payroll transactions. My methodology is very tried and true. I Download the data into Excel, clean it up, save it, map it to Integration Manager and after a few hit and miss tries...the data has been successfully integrated. I can move on to something more exciting.

My client runs about 2,500 payroll checks a week and there is a massive amount of employee turnover. In the legacy system (which is housed in a separate facility from the new implementation) employees were not inactivated and, after several years, there were literally hundreds of thousands of employee master records.

As in Dynamics GP, the data that I needed was in several different tables, and had to be exported. No problem I think, and dump out the historical payroll transactions via SQL table export. I will do the same thing with the entire employee master record table, Do a VLook-up to parse out the employees that haven't been paid this year...and there you go... I have what I need to import into my new GP database.

Who knew there was a limit to how much data Excel could handle. Yes, I know, there is a max. amount of rows - and Excel 2007 can hold a lot! Apparently, not enough for my employee master and state tax file. After several attempts (and a lot of swearing) I have the data in separate workbooks. Now to get rid of the employees I do not need. Not so easy. The calculation speed was painfully slow and while I could import data to all 1M rows, Excel couldn't run the formula due to memory constraints (so I am told.) 12 hours later I am completely frustrated and at my witts end.

This is where the story gets good. My fairy godmother (a.k.a. my boss) tells me to quit pulling my hair out and call Tom Celvi. He is a SQL wizard! I am a bean-counter and application consultant. I have always thought of SQL as something to be respected and a bit feared. After all, it is the "house" for my GP data.

Silly me! There is a whole other world out there that I owe myself to learn more about. Tom has shown me that SQL is a very powerful tool and can be used for other things besides housing my precious GP databases.

Tom created a database within the clients SQL 2008 environment, imported all my raw, ugly data and through a series events that I can only explain as pure alchemy...he managed to move that cleaned up data into my GP database and I had my data in all the correct payroll tables along with only the 12,000 current employees!

This took Tom 10 hours in total and I bet he didn't swear once! I was then able to successfully integrate the current year payroll transactions via Integration Manager and my my client is live and processing payroll.

Under lessons learned:

SQL doesn't have to be feared, it can be your friend (but should be respected)

Quality over Quantity is over rated - strive for both because Excel does have its limits.

hang out with a SQL guru, it will save on hair dye