Blog Archives
SQL Server Migration Assistant Problem Solved
Having used SQL Server Migration Assistant for a major Oracle conversion project over the last year, I quickly grew aggravated with its project limitation involving a single database. In other words, if you setup the project file in SSMA for staging and then you are unable to point it to an acceptance environment to migrate to that environment. That can be very frustrating especially if you have customized many of the project settings. I have discovered two workarounds to resolve this problem.
- Backup (or copy) the project folder (c:\Users\<<Username>>\Documents\SSMAProjects\<<Project Name>>). Close SSMA and then delete the target-metabase.mb file in the project folder. Now open SSMA and now you can connect to a new SQL Server and/or database. You would want to copy the folder if you want to retain the settings for a different environment such as staging, acceptance, production, etc. If you rename the folder for a different environment, make sure to rename the *.o2ssproj file to match the folder name. This is the file that opens inside of SSMA.
- The second option is to create a new project using SSMA and then copying in the object-containers.mappings, project-container.mappings, and finally preferences.prefs. These are the project settings that you have painstakingly setup in your original project.
I hope this helps. Enjoy!
SQL Server Domination Plan: Two Weeks After
A few weeks ago, I let loose a rebel yell and issued a command directive: Death to Oracle! Then I delivered the death knell as we migrated all of the data to SQL Server over Memorial Day weekend. Today, I am proud to announce that after two weeks of the Finance SQL Servers being in production, the users are happy and productive.
Most would say that is how it should be, but the Oracle DBAs told me that the users would complain daily. They were happy to rid themselves of those users and now I know why.
Hopefully I did not jinx myself but it looks like the domination plan is working!
SQL Server Domination Plan Phase One Has Completed
Last week I let loose a rebel yell and issued a command directive: Death to Oracle! Today I am proud to announce that I dealt a death knell to one of our Oracle servers by migrating our finance applications from Oracle to SQL Server. This was a year-long project that culminated in one really, really long weekend of SQL Fun. I survived and Oracle bit the dust. My plan for SQL Server World Domination has completed phase one.
My part of the project included a 27-hour marathon involving migrating the data using SQL Server Migration Assistant and then numerous scripts and comparisons to day-zero databases from the vendor while upgrading the application across five different product upgrades using RedGate SQL Compare (which worked beautifully). It was a day full of Starbucks triple espressos and five-hour energy shots before I crashed and the testers took over. We then rounded out the weekend with a 33-hour ETL process which all completed successfully before the application went live at 7am this morning. Mission accomplished.
This project has great visibility in my organization and its success will open the flood gates to future migrations in showing our world that SQL Server is an enterprise level database. I am not stopping here, I want their 100 terabytes of production data! It will be mine, oh yes it will be mine.
SQL Server Domination Plan Has Begun
Today signals the end and the beginning for a year-long project. My project for SQL Server Domination. Our shop is primarily an Oracle shop with dot net developers. Although I do have almost 300 databases across all levels, the bulk of mission critical applications are written for Oracle.
A little over a year ago we began a plan to migrate our financial applications to SQL Server in order to retire the oldest Oracle servers on site and reduce our massive licensing footprint. This was all part of my plan for SQL Server World Domination (said in a maniacal voice, imagine that). We knew that if this mission critical migration was a success that we could leverage our product against the Oracle servers in-house and begin the destruction of their infrastructure in favor of my glorious SQL Servers! (Hear the trumpets heralding the new king?)
This project, if successful, will be the Oracle death knell. Over the last year I have prepared diligently with one goal in site: this migration weekend. The weekend of my birthday seems apropos to give me the present that I so desire, SQL Server Domination. You may have noticed that the number of blog posts recently have been limited especially after blogging every day last year. For the last few months, I have had one purpose and one purpose only: ensuring the success of this migration!
Let the trumpets herald the coming of a new dawn. Long live SQL Server. I will blog again next week on the other side of this project to declare that the Queen is dead and a new King has ascended the throne! Until then queue the Imperial March, hello Starbucks and massive four day working weekend with little sleep migration project here I come!
Moving SSRS Reports
Yesterday I was tasked with migrating our Citrix EdgeSight database off of a 2005 SQL Server and migrating it to a new 2008R2 Server. No problem, I have done that a million times before. But wait a minute, that database has SSRS reports installed on that server and not our main reporting server. If you follow my blog, you will probably see that I have not had much experience with SSRS, my reporting experience has been with Crystal and other minor packages.
I started researching how to migrate these reports and was not pleased to see Citrix’s response. They recommended migrating the ReportServer and ReportServerTempDB databases to the new server and then reinstalling SSRS on the original server. So what happens then if you have other jobs on the new and old servers? This is not a good recommendation. After an exhaustive search the only option I saw within the tool was to download reports one at a time and upload them into the new server. This was also not an option with a fair amount of reports to migrate.
Enter Reporting Services Scripter from SQLdbatips.com. Run the program on the original server and select your Report folder and scripts are generated and placed into a folder of your choosing. These scripts can then be copied over to the new server and executed by command shell. In this particular instance we had to reopen the data source and enter new credentials. Problem solved, reports migrated successfully.
Enjoy!
Saving Time with SQL Server Migration Assistant for Oracle
After using the SQL Server Migration Assistant for Oracle on a current project to migrate an Oracle database to SQL Server, I have learned a couple of little tricks that I would like to pass on to you. These are mainly time savers so hopefully they will make you more productive in your project.
The first problem I noticed is that there was no easy way to copy projects nor was there a way to save a project file as another name, which would have allowed me to copy the project. Why would I want to copy a project? There are many settings such as column type mappings that will stay the same for my project and are very time-consuming to enter, if you are modifying the defaults. In my migration, I want to do many iterations in my development and acceptance environments with different SQL Server database names such as Round1, Round2, or Round3 and then use Red Gate Compare and Data Compare to verify that all of the schema and data from the migration are consistent in my migration. This gives me a warm and fuzzy feeling before I turn over the data to the testers.
However with each iteration, I must create a new project manually. Why? Because SSMA does not refresh to recognize new databases in the existing projects. That is very annoying. It only shows what is there at the time the project is created. Even if you reconnect to the SQL Server, it does not refresh.
If you know ahead of time that you will do many iterations, you could however create several databases before starting with the SSMA and then copy the project folder from the first project that you create manually. You must then rename the folder to the new project name and the corresponding project file (with an o2ssproj file extension) contained within the project folder. This would allow you to simply switch to the new database. You must also keep in mind that you need to double-click on the Oracle schema and make sure the target schema is selected as your new database. This will not happen automagically as you choose a new database name in the SQL Server metadata explorer.
Another option is to create new projects as you create the databases and then copy in the object-container.mappings and/or project-container.mappings files from the first project folder into the new project folder. This will allow you copy those settings into the new project and save you some time. I hope you find this useful. Enjoy.
Oracle Conversion Update
The Oracle Conversion, day 14, continues and it is not as exciting as I envisioned. Oh, don’t get me wrong, the end result, Global Domination for SQL Server, will be the ends that justify the means. But until then, migrate data, check data, clean data, remigrate date, check data, clean data, time migration, repair migration process, check data, clean data, remigrate, ad nauseam. Rinse, lather, repeat.
Oh, this is not my first rodeo, I’ve converted many projects before but never have I converted Oracle before. Granted, the SQL Server Migration Assistant for Oracle has made life much easier but at the same time it has made life much more difficult.
It is a quirky tool that is very temperamental. We had to restart it before we did a successive migration or it would fail we are guessing because it possibly caches information from the previous migration process and the MSDN blog wasn’t much help. We also had to hit apply on every setting change or it would revert some of them back to the original value.
In addition, we would have to convert schema sometimes four or five times before it would synchronize them properly to SQL Server, saying that the SQL execution failed. That answer gives me a huge amount of troubleshooting possibilities. When it works, it was a great time saver but had we known the quirkiness we probably could have scripted it all out and saved a lot of headaches.
Doesn’t sound like a big deal?
Well when you have some big tables and it takes eight hours to convert the schema and you try synchronizing and it fails and you have to convert the schema again, you watch the progress bar for many hours. Oh yeah, I left out the fact that the vendor is responsible for the migration contractually, so I guess ultimately they should have scripted it out. Enjoy!
SQL Server 2012, Here We Come!
After meeting with Microsoft today to discuss our existing licenses and software assurance level, my organization gave SQL Server 2012 the green light! With our existing software assurance agreement, we will be grandfathered in to the new structure. Thus, when we renew our agreement in 2014, we will convert our existing processor based enterprise licenses to their equivalent core based licenses without additional penalty. That conversion had management worried and there was even discussion of staying on 2008 R2 for as long as feasibly possible, which no DBA wants to hear.
With the grandfathering process, there will be a concerted push to build some new clusters so that they may be grandfathered in as well, especially since we found out that our new acceptance cluster did not count against our licenses if we had the appropriate MSDN licenses for our three SQL DBAs. Personally, I was worried that the new licensing structure may derail some of management’s plans to reduce our Oracle footprint onto SQL Server. If that had been derailed, my plans for global domination would have once again been thwarted by those darn meddling kids. Or was that just old man Smithers channeling through me again?
Enjoy!
Migrating SQL Server Database Logins with sp_help_revlogin
You’ve migrated your database to a new box, with no problems. You created the login that goes with this database, and all is right in the world. However, when your user goes to connect to it they get the dreaded error 18456, login failed. But wait, I created the login, right?
Not so fast there, accidental DBA. You are going to need the help of a nifty stored procedure called sp_help_revlogin. This stored procedure, provided by Microsoft in the link below, will generate a T-SQL script that will copy the logins, their passwords and their corresponding SIDs (or security identification number, which is usually the culprit here). This is also a great utility when the database has numerous users with various security levels and passwords. Really now, who wants to write all of that down and recreate it? Even with screenshots, it can turn into a large, fat-fingering and time consuming event. Mind-numbing, I would say. Why do something manually when you can do it automagically? Just think of all those happy logins that will not be orphaned by the dark side?
Here is also a code snippet to check for orphaned logins, for your enjoyment. Enjoy!
USE <database_name>; GO sp_change_users_login @Action='Report'; GO
SQL Swampland 