Posts Tagged ‘Migration’

Database Consolidation Considerations June 3rd, 2011

Vinod Kumar

Following the post around Multi-tenancy, there were interesting comments that have come my way to write more on such topics. Well, thanks for reading and dropping a line. In this post, thought will write around another interesting yet very common topic which I get a chance to discuss with customers – Consolidation of Servers. Though these can be driven by very specific business reasons, these do need some thought before implementing.

There are a significant number of manageability, availability, performance, scalability, and political considerations when deciding between dedicated (physical/virtual), instance level, database level or schema level consolidation. Fortunately, most of these are covered in the SQL Server 2008 Consolidation Guidance technical article. The article comes complete with excellent explanations and decision trees, and though it’s primarily focused on the decision between virtualization vs. instance level vs. database level – it is noted in the article that “Other possibilities include further optimizations on an existing approach such as schema-level consolidation. The key decision factors are similar to the higher-level consolidation options mentioned previously, so this paper will focus only on those.”

As mentioned before, business might see Consolidation from (not in any specific order):

  1. High-Availability – Instead of giving redundant multiple HA databases, some times consolidating will give an advantage for all the applications on a better server with a common HA option (like clustering).
  2. Centralized Management – Customers look at this as an opportunity to consolidate all the departmental applications and more so consolidate the DBAs inside their organization.
  3. Cost Saving – This is most likely the first thing that business sees as an opportunity. Ultimately, they want to look at maximizing the utilization of the hardware they have bought or more so get one beefy server to manage 10s of applications database away from outdated hardwares.
  4. Risk management – As discussed on above, centralization means it also standardizes the way DB code is developed, managed, deployed and maintained. Also it becomes easier for servicing, implementing processes and automated system administration.

Other Consolidation Considerations

Well, all the above reasons are valid, there are more that are missed between the lines.

  1. Operational Cost – Consolidation on newer hardware means reduced servers and power savings can also be achieved. And moving virtual also means you are saving on hosting costs.
  2. Increased uptime – Server consolidation makes it more economic to provide high-availability configurations and dedicated support staff. Organizations also benefit from implementing better storage management and service continuity solutions.
  3. Predictable Performance – Moving to a more standardized systems means we can assure a more predictable performance and can behind the scenes implement isolation of resources per application and a DBA can go ahead and implement compression like techniques without change in application code.
  4. Integration Benefits – A consolidated platform provides for easier, consistent and cheaper, systems integration, which improves data consistency and reduces the complexity of tasks such as extract, transform, and load (ETL) operations across systems.

Technical Considerations

For code already written, migrating to an alternative solution than was originally designed may require rework by developers – i.e. if they’ve already written their code in database A to access database B objects with 3-part name identifiers, they would have to make accommodating changes. Additionally, any database users with a database-wide role (db_datareader, db_ddladmin, etc) that are consolidation candidates might have to be changed.

Though schema consolidation with a single database is nice for logical groupings of data that benefit from being kept in synch [especially during a recovery] or must maintain referential integrity, the thought of combining unrelated entities raises all kinds of management and political questions.

Unrelated applications might have differing availability, maintenance, and isolation requirements – and combining them at a database level complicates this. And if these are financially sensitive data then the process of auditing or tracking becomes even more harder than ever.

Finally, It is great to collapse multiple databases from applications, but if these applications are in-house applications you are good. If these were third party applications – then application compatibility needs to be checked. Certifying the application to Versions of database, x86 Vs x64 architecture, version of the OS etc are all extra activities to be handled – not to mention on dependency services.

Misc Considerations

Well, Planning is the name of the game. Loved to see this hidden somewhere in documentation – a Checklist. Worth a read and consideration, though this is not complete – you will need to create one for your infrastructure needs.

But there are tons of other things that also come to mind –

Special Security context of databases
Limitations or dependencies that prevent consolidation (Agent jobs, Maintenance Plans, SQLMail, ETL or others)
Third party add-on dependency for application
OLTP VS OLAP features and frequency of use
Dependencies of Server / Instance names (hard coded inside application)
How many databases for a single app and proximity of all the dependent DBs
Data growth rate, Data Retention Policies followed by Archival
Backup windows and Special Backup technologies
Peak usage / Low usage time windows of each server
Replication frequency, duration, and volume
Specific Connectivity requirements (Protocols, SSL and others)
Internet / Public Access vs. Internal only
SLA’s to business units for uptime

In the 60 page whitepaper on Consolidation Using SQL Server 2008, the option to collapse multiple databases into a single database [via multiple schema management] is essentially written off citing challenges with security, object naming conflicts, performance issues, etc. It leaves off with an ominous warning that schema-level consolidation should be “used carefully” – but I’d just avoid it all-together for unrelated applications. And you have more dope above on why I say so.

HomePage for SQL Server Consolidation and Virtualization

If you did read it this far, feel free to drop a comment with suggestions if any. Obviously, your experience are also unique and valuable.

Continue reading...


My Blog New home December 31st, 2010

Vinod Kumar

Welcome back to my new Blog space (Vinod Kumar)

WHY: If you are reading this post, you have surely landed into my new blog space. Yes, I did move away from my old location at SQLXML. Interestingly, the reasons were simple – I was the only person blogging on that space and I had to finally move away because even Bryant had moved away from that space to his very own domain.

With so much happening on the blog space, there is another change in the work I will be doing inside Microsoft from mid-Jan 2011. More about that in the next 15-20 days when things become clear and concrete. I thought with so many changes around, this change will also be a good thing to initiate.

WHERE: Least assured, you can be confident that I will find more time to blog, share and be active on this blog space. My domain of ExtremeExperts has been around for quite sometime and in some forms has been my Identity also since 2003. So I thought of going ahead with a sub-domain for my blogs. Makes it easy for my readers to remember just one Identity to reach me.reverse-migration

WHEN: Yes, there will changes on the blog from skins, plug-ins, format etc in the coming weeks as I get going with the tweaking process before I lock on something. I surely request your kind patience while the mayhem happens on my blog.

There are still some old linking on few posts which I plan to somehow change slowly but surely as and when I get hold of them. So keep sending those broken links to me and I will update them …

Yes, I am on WP hosting now guess quite a lot of you have also used the same. So are there interesting plugins I need to take a look at? Please feel free to recommend and give pointers. Will want to try and lock on things as fast as possible.

TODO: Please update all your bookmarks to point to this new location. Also update all your RSS / Atom feeds to this new location.

Continue reading...


SSIS–Excel Connection Manager Error November 29th, 2010

Vinod Kumar

Yesterday, I was playing with SSIS on my host machine and had bumped into an interesting error:

[Load Promotion Data [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[SSIS.Pipeline] Error: component "Load Promotion Data" (1) failed validation and returned error code 0xC020801C.

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Now this looked interesting because I was on a x64 bit OS, x64 bit on SQL Server but had a x86 (32 bit) Office 2010 installed when I was using my Excel Data Source. These errors were taken as-is from the “Execution Result” tab directly. The errors give away some detail that this combo is the problem. But fundamentally various search yielded me into places which stated I need to run SSIS in a x86 mode. And this is well hidden inside the Project –> Properties tab –> Configuration Properties –> Debugging menu.


Just turn the “Run64BitRuntime” option to “False”. And go ahead with your build !!! This just solved my problem.

Continue reading...


Denali–DTS Support November 26th, 2010

Vinod Kumar

It is high time that we started looking at moving our age old DTS packages back into SSIS with full fledged functionality soon. Just checked the SSMS on the CTP1 to figure out that under the Legacy system, this has gone missing.


Moving to the new features is always advised and make sure to keep an eye into the support for way old features that you are likely to still have around as you plan for your migration or upgrade. All the best !!! The documentation will surely have a note, but as we start playing around I am getting to know of these one by one …

Continue reading...


Denali–Extended Events November 24th, 2010

Vinod Kumar

As I installed Denali (next version of SQL Server) on my machine, I have been trying to figure out what all have been added as part of this CTP1. On playing around with the SQL Server Management Studio I found something very interesting –


Now Extended Events in completely integrated inside of SQL Server and this is such a cool thing. Though there is very less we can do with this in this CTP1 release. I just tried to query from the database to figure out that the there are tons of events and actions already there inside the database.


I am sure the subsequent CTP’s we are sure to see more of this xEvents infrastructure. As shown in the figure 1, there is already an Event on System_Health check which has been configured out-of-box. Interesting to play around, you can script it out and check how it has been configured. Remove the grouping and the wealth of events and actions come out …

More on this later, but you get the point. Extended Events are now part of UI and lots of fun things are coming round the corner !!!

Continue reading...