I had a Sybase to SQL server migration related issue recently. While working on it I discovered a few things about which provider SSMA for Sybase uses for migrating database schema and actual data. I thought of blogging about it and sharing the information. I will deal it in two parts first is when we are using Client Side Data Migration Engine and the other is when we are using Server Side Data Migration Engine. The difference between these two is where the actual data migration engine resides.
Scenario One: Client Side Data Migration
In this case the data migration engine resides under the client executable process. So when we do a Client Side Data Migration the “Sybase OLE DB provider” is the only one we need and can leverage for pulling schema and data from Sybase ASE Server.
The preferred client to download would be the ASE OLEBD 15.5 which can be downloaded freely from http://m.sybase.com/ase_1500devel , the following connection string is all you need to get you connected to the Sybase Server (provided its up and runningJ)
Server=sybserver;User ID=MyUserID;Password=MyP@$$word;Provider= ASEOLEDB;Port=5000;
The same provider is used for moving both the Schema migration and Data migration. Using the ADO.net provider during client side migration is not an option.
Scenario Two: Server Side Data Migration
In this case the data migration engine resides under the SQL Server process.
When we do a Server Side Data Migration the “Sybase OLE DB provider” is the mandatory for the initial connection to Sybase ASE Server and for migrating the Schema into SQL Server.
For migrating data either of the “Sybase OLEBD provider” or the “Sybase ADO.net provider” can be used.
The key benefits to using ASE ADO.NET Data Provider are:
· ASE ADO.NET Data Provider is faster than the OLE DB provider.
· In the .NET environment, ASE ADO.NET Data Provider provides native access to ASE. Unlike other supported providers, it communicates directly with ASE and does not require bridge technology.
Since I have found it way faster than the ASEOLEDB provider have used the same to get tables having millions of rows of data which otherwise give Time Out Exception I would elaborate a bit on the same. Moreover the ASEOLEDB is pretty much covered in the Sybase help files.
I have overcome a lot of timeout issues like below using the ASEADO.NET Data Provider. Particularly for large tables, the following was for a table with 14 million records.
Standard timeout error stack thrown when migrating very large tables.
site: Void ExecuteCommandTextErrorHandling(System.Data.OleDb.OleDbHResult)
source: ASEOLEDB
error code: -2147467259
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteScalar()
at Microsoft.SSMA.Framework.Generic.Utilities.DbUtilities.DbCommandWrapper.ExecuteScalar()
at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod)
at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteScalar()
at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.TableDataMigrator.GetTableRowCount(String tableName, IDbConnection connection)
at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.TableDataMigrator.GetSourceTableRowCount(String tableName, IDataMigrationContext context)
at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.TableDataMigrator.ProcessNode(IDataMigrationContext context, DataMigrationState state, XNode node)
at Microsoft.SSMA.Framework.Generic.Factories.Utils.DefaultTreeTraverser`3.ProcessNode(TContext context, TState state, XNode node)
at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.ProcessSubTree(TContext context, TState state, XNode sourceSubtree, Set`1 objectsToProcess, Set`1 affectedNodes, IUIStopOperationProvider stopOperationProvider, IUIOutputWindowProvider outputWindow, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.TraverseTree(TContext context, TState state, XNode sourceNode)
at Microsoft.SSMA.Framework.Generic.DataMigrators.Default.DefaultTreeDataMigrator.DoMigrateData(IDataMigrationContext context, XNode subtree)
at Microsoft.SSMA.Framework.Sybase.SqlServer.DataMigration.Sybase2SqlServerDataMigrator.DoMigrateData(IDataMigrationContext context, XNode subtree)
at Microsoft.SSMA.GUI.Generic.LowLevel.AppCommandProcessing.Commands.SsmaAppDataMigrationCommand.StartMigrateData(Object context)
The client installation should install the Sybase ADO.Net provider. A quick way to check is as follows.
1. Start Visual Studio .NET and open your project.
2. In the Solution Explorer window, right-click the References folder and choose Add Reference from the pop-up menu.
The Add Reference dialog box appears.
3. On the .NET tab, scroll through the list of components until you locate the Sybase.Data.AseClient component. Select this component and click Select.
4. Click OK.
If you do not find the ASE ADO.NET Data Provider assembly listed in the components, browse to locate Sybase.Data.AseClient.dll in the <install dir>\dlldirectory. Select the dll and click Open. Then click OK.
The default location is C:\Sybase\DataAccess\ADO.NET\dll. (Note : Sybase documentation erroneously specify the path as C:\Sybase\ADO.NET\dll which is not the case)
If you get an error while using the same from SSMA the most likely cause is it’s not registered in the GAC following steps specify how to put the same into GAC.
Deploying the assembly manually
- Start the .NET Framework Configuration tool. Refer to the Microsoft documentation for your specific operating system for instructions on how to start the configuration tool.
- Select Assembly Cache from the tree view on the left.
- Click the Add an Assembly to the Assembly Cache link on the panel.
- In the Add an Assembly dialog box, find ASE ADO.NET Data Provider assembly located in the installation directory (C:\Sybase\DataAccess\ADO.NET\dll by default) and click Open.
ASE ADO.NET Data Provider assembly is now deployed into the GAC. You can verify this by selecting the View List of Assemblies in the Assembly Cache link from the panel and examining the list of assemblies in the cache.
Once we are sure the ASE ADO.Net Data Provider is properly registered in GAC the same can be used in a “Server Side Migration” for migrating the Sybase Data.
Well I think I might write another blog about SSMA migration performance in general spanning all the Databases but will leave it for another day.
Hope this helps in getting your Sybase Data migrated faster to SQL Server.
All the best for your migration and welcome to the world of SQL Server!!!
Author : Angshuman(MSFT), SQL Developer Engineer, Microsoft
Reviewed by : Ajay(MSFT), SQL Developer Engineer, Microsoft