Learn about Enterprise Business Intelligence with SharePoint and Dynamics GP
Our next webcast, which takes place on Jan. 25 at 11 a.m. Pacific, provides an introduction to SharePoint 2010 features available in Microsoft Dynamics GP for SharePoint developers and architects.
Based on best practices presented at Microsoft Convergence 2011, instructor Marie-Michelle Strah walks through document management, workflow and lists in Dynamics GP Business Portal to help you increase client satisfaction. She reviews enterprise architecture, licensing and development considerations when working across these platforms, and how to plan top of the line solutions that incorporate best of both environments.
Go Register
Move Database Using BCP
To bring things back around to the original problem, I had a client needing to move data from a database with the same schema from a newer version of SQL Server to an instance of an older version of SQL Server. As a result they could not simply perform a backup, and restore the data on the other server…database backups are not reverse compatible.
We talked about transferring the data using data export options built into SQL Server. This option was tried but failed in that it did not transfer the data in order of referential integrity. There may be ways to do that, but I haven’t used database transfer since SQL Server 2000.
Another thing that was considered was scripting out the database into a file, and executing the script using SQLCMD. For a large database this results in a problem if the script file length is over 4 gig.
PowerShell is a great tool providing access with a number of options. Certainly it could be used to script transfer of data using SQL SMO.
One technique I promised to consider today is BCP (Bulk Copy). The BCP command line (goes way back to Sybase) has a command line interface for extracting data. The export may be formatted using formatting options in the command line, or a format configuration file. BCP can perform both Import and export capabilities.
In this case you still have the problem of exercising the import in the order of referential integrity. Again, PowerShell would be a great tool to write a utility that would traverse the database structure using INFORMATION_SCHEMA views or SQL SMO to determine what order to import the data. It would be able to execute the BCP statements as well.
Bulk Insert may also be used executing the command from TSQL. This uses the functionality of BCP, but wraps the command in TSQL like syntax, easier for someone to understand.
Out of all these options, probably the scripting technique is the most flexible. I say this because INSERT statements don’t vary much (if at all) between one version or vendor of SQL. Very likely, scripted insert statements from SQL Server could be applied to a MySQL instance, etc.
Lest I forget a most excellent tool, remember that this is what SSIS is all about; ETL (data Extract, Transform and Load). SSIS has built in connectivity to many different forms of data persistence, and handles workflow nicely.
In case you’re wondering what I finally did, I simply wrote a batch file using a TSQL Query. The Batch File ran a series of BCP Export commands. I wrote it in SSMS because I could determine the order to execute the tables based on INFORMAITON _SCHEMA views. Quick, reliable, one time solution didn’t require an elegant solution.
Tomorrow we’ll be moving on to logging in your Dot Net Applications. Feel free to drop me a note with your preferred logging techniques by sending an email to btaylor@sswug.org.
Cheers,
Ben
SSWUG TV
With Stephen Wynkoop and Sam Brace
Continuing in this edition of SSWUG TV find out about Oracle and Cloudera’s joint agreement on Hadoop and more.
Watch the Show
$$SWYNK$$
Featured Article(s)
Troubleshooting SQL Server 2008 System Stored Procedures (Part 1)
In this article, Alexander Chigrik explains some problems that you can have when you use SQL Server 2008 system stored procedures. He also tells how to resolve these problems.
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)