At first database migration from SQL Server to SQL Azure may seem like a pretty easy thing to do. In fact when it comes to a small project of around 20 tables and about a dozen of stored procedures, this really is the case. However if you get a project of hundreds of tables, stored procedures, functions and triggers, if cross-database access is being used, and there is dynamic objects creation, then you will have to face a number of complex and interesting tasks which would require a not so obvious and simple approach, and solutions.
In this article I will try to address some of the challenges I have faced when developing SQL Server to SQL Azure migration solutions.
The first less complicated, but definitely not pleasant issue, is that SQL Azure requires each table to contain a clustered index, otherwise you won’t be able to insert any data to it. One would think: so what’s the big deal- you create a clustered index for each table, and proceed with the development. However this minor inconvenience is the core of the second problem: SQL Azure does not support statements like: Read the rest of this entry »
My last blog (Extract-Transform-Load (ETL) Technologies – Part 1) discussed the purpose, function, and some of the inherent benefits of ETL technologies for moving data from source applications into target reporting and analytic environments. Hopefully from that discussion one can gain some general understanding of ETL; what it is, how it can be used, and why we would want to use it. Now let’s dive a bit deeper and discover who some of the key vendors are in the ETL Tools marketplace.
ETL tools have been around for some time, have evolved, matured, and now present us with productive environments for Big Data, Data Warehouse, Business Intelligence, and analytics processing. The ETL data processing cycle itself can be simple or highly complex. Most vendor offerings address the diverse requirements well enough although some do better than others. The challenges with ETL tools come from the other important considerations, like:
One of our core value propositions at DB Best is to help our clients lower the costs of running their enterprise level applications and be more productive. After 10 years in the database migration business, we now have top experts, smart tools and established methodology which allow us to seamlessly switch apps over to Microsoft products. Let us give you an example of a very successful 3-week Siemens Teamcenter Oracle to Microsoft SQL Server migration project.
Many of our customers ask: “If my application runs and I’m happy, why do I need to migrate?” Being in the database migration business for over 10 years now, we’ve found that a value proposition based on hardware savings, licensing advantages, and professional relationships – used together or separately – makes customers interested in moving to the SQL Server platform.
As you probably know, our key migration offerings include conversion of Oracle, Sybase ASE, Sybase ASA, DB2, DB2 UDB, MySQL, Informix, Access applications and databases to SQL Server 2005/2008. Watch this video to learn more about our migration package:
So now we at DB Best have a packaged migration offering that helps us look at this whole end-to-end migration as one project. As a part of the service, we offer Portfolio Assessment, a 12-step migration methodology that allows us to estimate the cost and effort required for application migration. It also enables us to scan the entire environment to do a quantitative analysis of the database. Based on the findings, we deliver a detailed document – internally, we call it Technical Roadmap – that describes all the steps involved in the migration of that database to SQL Server. Read the rest of this entry »
My last blog (Column Oriented Database Technologies) discussed the differences between Row and Column oriented databases and some key players in this space. Concepts and technologies on Big Data have been discussed in previous blogs (Big Data & NoSQL Technologies & NoSQL .vs. Row .vs. Column). From these blogs one should surmise that deciding upon the best database technology (or DBMS vendor) really depends on schema complexities, how you intend to retrieve your data, and how to get it there in the first place. We’re going to dive into this next, but before we do it is imperative that we briefly examine the differences between OLTP and OLAP database designs. Then let’s leave OLTP details for a future blog as I expect most readers already know plenty about transactional database systems. Instead we’ll focus here on OLAP details and how we process Big Data using ETL technologies for data warehouse applications.
Generally the differences between OLTP and OLAP database applications center upon how frequently data must be stored and retrieved, the integrity of that data, and how much of there is and its growth. OLTP database schemas are optimized for processing transactions by an increasing number of users while OLAP database schemas are optimized for aggregations against an increasing amount of data and exponential query permutations. Design considerations involve normalization, indexing, datatypes, user load, storage requirements, performance, and scalability. We will need to defer the many interesting details on these considerations for a future blog.
We have a new product in our database migration lineup – MySqlMigrator, a neat tool that helps you transfer data from MySQL tables to existing SQL Server 2008/2012 tables in a few steps. It comes with a wizard-based interface that allows restarting migration from any step. The tool uses the bulk copy mechanism for loading data from MySQL tables to SQL Server.
Here’s a quick video tutorial to get you up to speed on what MySqlMigrator can do for you:
In 2007 I worked for a company which provided data warehousing solutions. Our biggest client database was about 2-3 TB which was not so much even five years ago, but because of extremely complicated ETL and complex reports we had a lot of troubles.
We spent hundreds of hours monthly on code optimization, but every 30 minutes saved on ETL and processing were compensated with monthly data growth and we had to start over and over.
Finally it comes the time when we couldn’t improve anything else in programming code and company decided to drastically upgrade the hardware. But extremely expensive servers and disk storages gives us only several percent performance improvement. We thought about migration to another platform but preliminary estimations showed that every traditional RDBMS has the same bottleneck – disk storage. Maybe, if it was happening today, we probably would turn to fundamentally new technologies.
The whole software development industry is highly innovation-oriented, but RDMBSs always are perceived as something very conservative. I still have record about ANSI-92 SQL knowledge in my CV and almost all fundamental works from 70s are up to date, but in spite of that data management software industry is very fast developing and great new products comes to market each year.
When migrating Oracle tables to SQL Server, the problem of sequence conversion is one of the most common. SQL Server 2008 does not have any object even distantly resembling an Oracle sequence, and some tradeoffs are inevitable when you make decisions about the mechanisms to implement its behavior in the target system. In SQL Server 2012 Microsoft introduced sequences, but several incompatibilities with Oracle implementation still exist.
If you ask any experienced SQL Server developer: “Can I build a view on temporary table?” the answer will be “Of course not. And why on Earth you need it?” Well, Oracle developers can tell you why. Temporary tables are very useful when you have to separate data between users/sessions and creating view on them is just convenient way to store some logic. SQL Server developers used to deal without it but when they are trying to move database from Oracle they will have to find workaround because it’s pretty common pattern in Oracle.
Let’s take a look at how it works.
First thing that comes to mind is to use regular table instead of temporary. To emulate data separation between sessions we can add “spid” column with default value @@spid (system function that returns the session ID of the current user process). Than we need to create view named exactly like original temporary table (of course we have to name our regular table differently) and add where condition “spid = @@spid”. So select from the view will return only that part of data, which was populated in current connection. Next challenge here is that in Oracle data from temporary table is automatically deleted on rollback/commit. Read the rest of this entry »
You probably heard about autonomous transactions in Oracle. In a few words, using just one pragma directive in a PL/SQL block you can isolate it from the callers’ context, so it becomes independent transaction. It’s quite useful if you want organize data audit and is pretty widely used in production databases. As you might guess these autonomous transactions do not have direct equivalent in Microsoft SQL Server. The only way to isolate a Transact-SQL block from a transaction context is to open a new connection. There are several options here, so let’s start with SSMA approach.
Assume that we have some logging logic in a stored procedure named “dbo.LogError” and we want to commit it even in case when main transaction will be roll backed.