Archive for October, 2012

Data migration from Oracle to SQL Server with SQL Server Management Studio (SSMS)

October 4, 2012 Leave a comment

It all depends on the goal. You may be required to transfer all the data from all the tables on Oracle DB side to SQL Server, or just some of those tables or just certain data, i.e. applying filtering. If you encounter such a task for the entire database (AKA Schema in Oracle), especially with filtering, I would say go with any ETL Tool you have in hand: SSIS, Oracle Warehouse Builder,  Cognos Data Manager, Data Services of Business Objects XI, with Informatica and DataStage among the industry biggest giants. These are all powerful, enterprise ETL solutions. There are quite a few less well-known, yet too powerful Open Source  ETL Solutions like TalendPentaho, CloverETL.

The former two one, – Pentaho and Clover, do declares themselves Open Source, yet the same time you can’t just download neither of them for free, apart from trial versions. At least, I couldn’t fine a link to freely downloadable, full-featured installation package. Not sure what kind of Open Source those Pentaho and Clover are than. That’s why listed them as commercial ETL tools. It’s only Talend, as for the current date, doesn’t charge anything, being the only true Open Source among those three.

Anyhow, back on the track. If you don’t have any of those tools, including SSIS, which you really should have just because it comes with SQL Server installation, but by whatever reason it’s not available (like in my case where I can’t have anything but SSMS working on my 32-bit Win7, installed on x64 laptop… don’t ask why), so you can count only at SSMS as the only major tool which has the data import/export, this post might be just what you need.

The idea is simple: get table(s) DDL(s) and use it while setting up data import from Oracle with SSMS. In order to do that, you would have to have Oracle Client in place. Another trick links to SQL Server 2008 Enterprise / 2008R2 Enterprise Data Compression feature. Please, refer to my post on Data Compression for details and a reason why I believe Data Compression shall be used at least in this kind of scenario.

Chapter 1. Downloading and Setting up Oracle Client

Intended for those poor soles who never worked with Oracle and don’t have a clue of what it is and how to approach it even from the point of how to install the clients. Any others of you, at least to a certain degree Oracloids or especially Oracle DBAs, please roll forward to the next chapter as you definitely wouldn’t find anything useful for yourself in this one.

1.1  Download the media from Oracle Downloads.

I assume, Oracle’s marketing people made the decision of removing links to any previous Oracle version from the Downloads page. Luckily enough, it’s Oracle’s techies who’s made current version Oracle Client perfectly compatible with any previous ones. Well, “perfectly compatible” is a bit of exaggeration, bit it does work fine for the total majority of time, leaving users with only few scenarios and moments here and there when Oracle 11gR2 client, which is the current AND THE ONLY AVAILABLE FOR DOWNLOAD FROM ORACLE WEBSITE, would cause troubles when working with Oracle Servers of previous version. (using exp/imp might sometimes, – don’t remember those minor hiccups by now, but they are easily workaround-able anyway) In any case, Oracle 11gR2 client would be totally fine to use for the purpose of data migration with SSMS in general and this post’s context in particular.

There is an alternative of going with so called Instant Client, but working with it is full of surprises. I don’t stand Oracle Instant Client and don’t advise you on using it because it will fail here and there all the time making you constantly googling for just problem solution. I highly encourage you to download and work with the full client.

– Click on “Accept License Agreement” – it’s important. Not the license agreement itself or reading it, of course.  It’s the radio button – if you don’t click on it, the download won’t start.

– Choose the right installation for your platform and click “See All”

– Accept the license agreement once again

– Click on Client link

– The download would start. Wait for it to finish, unzip and run setup.exe

1.2  Setting up Oracle Client

When the setup is done, a new dialog of Oracle Net Configuration Assistant  is being popped up, but more often than not it doesn’t come on top of the installation window. To make it appear, either minimize all the opened windows one by one, or simply click on Windows’ Show Desktop icon. This Net Configuration Assistant window would remain the only one on your screen.

Chapter 2.  Get the table(s) DDL(s)

1.1  Run SQL*Plus

First, let’s alter SQL*Plus shortcut a bit

Now, run the executable, connect with credentials of the schema you want to get table(s) DDL(s) from. Otherwise, if you have your own user, you would need to either make sure you have SELECT_CATALOG_ROLE role or be granted one.

Type or copy/paste this code:

set echo off;
set heading off;
Set pages 1000;
set long 5000;

select dbms_metadata.get_ddl(‘TABLE’, ‘TRAN_STATS_SRI_DLY’, ‘DATAW’) from dual;

Copy the result and paste in SSMS query editor.

Start with replacing double quotes with empty strings – only if you feel this way, as it’s not required. SQL Server accepts double quotes and square brackets in tables and columns naming conventions

Replace “NUMBER” with “NUMERIC”

Then replace VARCHAR2 with VARCHAR

Finally, replace any non-SQL Server keywords, such as “ENABLE” in this example

Comment out the bottom part of table’s DDL, so that it doesn’t include constraints and storage settings


and that’s it – you have the right table DDL to use on SQL Server-side when creating a table to host data pooled over from Oracle.

Chapter 3.  Importing data

Create SQL Server schema of the same name as Oracle schema which owes the table to be imported.


(Actually you can create schema of any name or choose not to create schema at all, – this way the imported table would be created under the default dbo schema, but this kills he consistency and clarity as the result)

Right click on the to-be-data-destination database -> Tasks -> Import Data

Here you can go by a number of different ways. Normally, to fascilitate “better”  performance in data import from Oracle to SQL Server, Oracle Data Access Components (ODAC) should be installed, – this way  Oracle Data Provider for .Net (ODP.Net) could be used instead of MS .Net Framework Data Provider for Oracle. But if you have installed Oracle Client 10gR2 then you will have to install ODAC 10gR2 or ODAC, which can’t be installed on Windows 7, for example. Long story short – it’s not always straight forward with ODAC. On the other hand, ODAC is not really needed. I mean Oracle claims it to be very fast and superb over any Microsoft Data Providers, but if it takes to scratch your left year with your right arm and that by pooling it over all the way behind your back… heck with ODAC! Microsoft .Net Framework Data Provider for Oracle is indeed quite slow, but Microsoft OLE DB Provider for Oracle is really quite fast. Fast enough to be used out of the box without having to go through the mess with installing ODAC.

Destination – should come up itself (because the Data Import wizard was ran with cursor on the destination db)

Write a query to specify the data to transfer

Key in your select * from <source_table_name> statement

Put in the full destination table name, i.e. including earlier created schema and hit “Edit Mappings”

Click “Edit SQL”

Delete everything (RMB + “Select All” + Del. Ctrl+A won’t work here) and then paste earlier prepared table DDL (Chapter 2)




Hit OK, ignore any warning messages

OK once more, Next, change Fail to Ignore under OnError(Global) and OnTruncation(Global)

Next, leave “Run Immediately” checkbox checked and click Finish.

Sit back and enjoy





Setup alerting of high CPU usage

October 1, 2012 Leave a comment

Few days ago I got puzzled with the possible ways of monitoring CPU on SQL Server box, firing an alert if CPU utilization gets over some benchmark for a set period of time. Surprisingly enough, it quickly turned out there is no such thing as any out of the box CPU-related counter(s)  in SQL Server, at least up to SQL 2008 R2 (don’t know what’s in 2012 yet)

Despite this unfortunate fact of lacking any CPU utilization-related counters, SQL Server Alerts have tones of other counters for monitoring with consequent alerting, when the alert event is fired. However, if you are a decision maker within an organization, you should consider implementing one of those enterprise Server / Network Monitoring tools, which have comprehensive set of monitoring and alerting tools. Good examples would be: WhatsUpGold, PA Server Monitor by PA Power Admin, Server & Application Monitor by Solarwinds.

There are some free tools of this kind: —> … here… <—

Now, let’s get back to our sheep. It would probably make little sense to get alerts every time CPU usage just hits the peak or hits it for some very short time, like a few seconds – this could barely hurt anything. This way, it’s a combination of CPU high usage threshold and time-frame it’s going on for. Typically, you wouldn’t want to get alerted before high CPU utilization goes on for a few minutes, five would probably be a good number here. Researching on this topic didn’t give much, but one post I found particularly useful for this situation – you need to get alerted when total CPU consumption on your SQL Server box gets over certain threshold for more than specified time. Here it is: How to Monitor for High CPU utilization in SQL Server by Geoff Albin.  Very good post, which might be quite helpful. As to me I decided not to go that rout as we have WhatsUpGold in place, which does servers CPU monitoring among other things, but I will definitely save this one for any future occasions when there won’t be any server monitoring app in place.

Just beware, if you decide to run the test, it won’t display it like it’s mentioned in the post:

but rather as a bunch of ever popping-up dialog windows, which you’ll have to kill from Task Manager. Here is what’s happens when you run the script from command window:

Then it waits for 4 seconds and the entire loop starts all over again:

Now, to kill it use Task Manager, locate wscript.exe and kick it off.

Besides, considering creating the job yourself from within SSMS rather than running the script from “Step 2” might be a good idea.