jump to navigation

Sybase to SQL Server migration – Part I September 29, 2008

Posted by msrviking in Migration.
add a comment

As promised in my earlier post Sybase to SQL Server 2005 migration here is the first post (of several posts) of the migration that was done.

The requirements were like,

1) Sybase version 12.5.4 running on Sun Solaris OS 5.8 to SQL Server 2005 on Windows 2003
2) Migrate objects like tables, stored procedures, triggers, indexes and migrate data from tables

Well, the moment I saw the requirements I was pretty comfortable assuming that this could be a straight forward migration. But yes, my inner thoughts knew that there are going to be of issues that are going to come up and I will have to resolve them one after the other thru’ help from forums, documentation, experiences from my earlier migrations, and so on…..

With this thoughts I started off doing search in my favorite (everybody’s favorite, I hope nobody will deny this at least Wink) search engine Google. There were good number of links that showed up but now I had the task cut out – to filter one by one to best relevance, and save the experiences of the forum members into a document of little relevant links.

After several rounds of searching, reading, assimilating and understand the contents of each reference link I would like to present bunch of important one’s that you guys wouldn’t want to miss.

Links for documentation on migration from sybase to sql server:

Although the link shows up the migration from Sybase to SQL Server 2000, but this is one of the good documents that Microsoft has under SQL Server 2000 resource kit and this turned out to be “Encyclopedia” for my migration strategy]

This link talks about how to deploy the migration strategy on production system or mock production systems. You could download the copy in word format from here.

Few other links which gave me showed the path of experience apart from above links on theory were

1. Making the Move from Sybase to SQL Server by Sayed Geneidy

2. Migrating from Sybase to SQL Server by Denis Goby

Although the links talk about the migration from different Sybase versions to SQL Server versions, I was having the needed information to kick off my work. And in my next post I am going to talk about what all I had to do with the collected information from these links. Until then, enjoy reading!


Resource Guide to Free Microsoft Software and Online Services September 29, 2008

Posted by msrviking in Technical Documentation.
add a comment

If you are looking for Free Software and Online Services then this resource guide should help point you in the right direction.  Click the link here Resource Guide to Free Microsoft Software and Online Services


SQL DB Backups storage area September 24, 2008

Posted by msrviking in Configuration.
add a comment

I have been into a discussion with one of my clients on where should the backup files be stored for quick backup and restore operations. Here is what I had to talk about,

After some thinking on “Which RAID level should the SQL DB Backups (.BAK, .TRN) be stored in the system?”. I have below thoughts, and recommendations.

Here is the flow of my thoughts.

1)    Does it matter on which RAID levels the backups are taken, be it .BAK or .TRN files or any kind of backups?

Yes, it would matter in systems that have multiple VLDB (Very Large Databases), where the transactions are high, several backup schedules run on multiple databases, taking different types of backups at different timings.

Although the current environment doesn’t have multiple VLDB high transactional databases with backup schedules running during the whole day for different database, I thought we shouldn’t go by the current configurations and should look from a long term perspective which led me to the next question.

2)    How does it matter? Does it matter in terms performance of taking backups, protection of backup files, performance of restore?

Yes, it is important to configure the backups on a drive which has appropriate performance, and fault tolerance. Also it is important to have the backups restored as quickly as possible from the backup location in the system.

In terms of performance of backups of database or restore of backup files, we need to know what are the internals happening so that we could provide an appropriate environment to speed up the process.

A backup or restore operation internally has a basic operation – reading from database, pausing, writing to the media and this happens multiple times until the backup is complete. And the restore operation would be vice-versa of backup internals, reading from backup files, pausing, and writing to [In Recovery] database, until restoration is complete.

The key point in both processes is reading /writing from /to the database /backup file. So essentially we need to help SQL Server in processing this quickly, by configuring the storage devices (system drives in here) appropriately. Also, to enhance the process of backup and restore it’s not the configuration of disks that matters, but it depends on the kind of backups taken, the way the backups are taken on the storage media (drives or tapes).

With all these thoughts, and keeping the configuration of the disk as the primary point I jumped on to the next question.

3)    How do we give performance and protection together?

The SQL DB systems are currently configured to use space allocations from RAID-5 and RAID-10 level of arrays.  Because of my naïve knowledge on RAID’s, I had to refresh my thoughts on what are the different RAID levels, and what exactly it means. Here is what the RAID-5 and RAID-10 are described,

RAID-5: This type of RAID stores parity information but not redundant data and parity information can be used to reconstruct data. And when each logical read occurs, the read performance is increased since reads occur from the configured disks. Whereas when a logical write occurs there are four physical read operations, decreasing the performance of write.

RAID-10: Combining RAID-0 and RAID-1 is RAID-10, which offers higher performance and tolerance because RAID-0 has striping which has high performance output for read /write operations and RAID-1 has mirroring of disks to provide protection.

After knowing how each RAID level works, and what we need my next question was.

4)    What should be the recommendations?

Backup of the databases (any kind) should be on drives which are at RAID-10 levels. The obvious reason being that RAID-5 gives bad performance when it comes to write operations, and RAID-10 provides better read /write performance because of striping, fault tolerance because of disk mirroring.

What do you guys think about my recommendations? Let me know.

Happy reading!

SQL Server 2008 will have DATAllergo techology September 17, 2008

Posted by msrviking in SQL Server 2008.
add a comment

Here is a press release by Microsoft on 9/17 on DATAllergo technology being used in SQL Server 2008. Today there are no bounds for my happiness on the progress that SQL Server has made since the time I had worked on (as far as my memory goes I worked on Sybase 11.x, and SQL Server 6.5).

A quick information on what MS had to say about this,

"Microsoft Corp. continues to rapidly expand the capabilities of its
data platform and is already revealing product road map details
following the acquisition of DATAllegro Inc., a provider of
large-volume, high-performance data warehouse appliances. As part of
its focus on helping organizations manage and access even the largest
quantities of data, Microsoft will offer a new solution based on
DATAllegro’s technology that extends Microsoft SQL Server to scale into
hundreds of terabytes of data. The company will begin giving customers
and partners early access to the combined solution through community
technology previews (CTPs) within the next 12 months, with full product
availability scheduled for the first half of calendar year 2010."

A quick information on what could DATAllergo could do for SQL Server 2008,

"Some info from their website: DATAllegro v3 performs table scans between 0.5TB/minute and 10.5TB/minute. DATAllegro v3 offers new capabilities to handle complex workloads that are a mixture of near real-time loads, long analytical queries and short quick-hit queries. Improved workload management gives queries with low workloads higher priority, reducing the overall workload on the server. DATAllegro also provides a comprehensive architecture with a high-speed loader performing loads at rate of over 1TB/hour. The loading server or landing zone within the high-speed appliance network is used to provide a loading services with a minimal impact to queries being run."

All the best SQL Server!

Six-Core Xeon Processors September 17, 2008

Posted by msrviking in Processors.
add a comment

For quite sometime I had been interested
on how the CPU’s handle the workload in a SQL Server system. This
curiosity always leads me into reading several articles on processors
(how they work, what was the history, what is the new technology, what
is in market, what are the trends, whats coming up in future).

Today, I have happened to read this article and had delighted me
(although I am an amateur in the world of processors). Here is the link
for good reading  processor named Dunnington officially released by
Six-Core Xeon Processors, Boasts 50% Performance Boost.

Happy reading!

Sybase to SQL Server 2005 migration September 15, 2008

Posted by msrviking in Migration.
add a comment

Hello Guys,

As per my promise given in my earlier blog post Back Again!, I shall be sharing my experiences when I had to do Sybase to SQL Server 2005 migration. This would be multi-series posts, and I am intending to cover lot of technical details along with experiences.

Keep watching the blog from tomorrow.

Happy reading!

Technical Rollup September 12, 2008

Posted by msrviking in Technical Documentation.
add a comment

One of my favorite blogs for technical reading is by SQL Master at SSQA.Net. And as I was going thro’  the blog entries I happen to read a nicely trimmed entry on Technical Rollup email pertaining to SQL Server, documents & guidelines.

Please check the article.

Happy reading!

Run a SQL Server job in intervals of seconds September 12, 2008

Posted by msrviking in Scripts.
add a comment

One the projects I work required a script to monitor sys.sysprocesses table to capture activity in the database.

At this point my thoughts were running around and I was looking for some scripts on the web (to avoid reinvention of the wheel). At last, I found a script written by Chad Boyd –  check the article here sp_who for Sql 2005 (sp_who2k5).
Now what? I had to tweak the above script so that it could be joined up with sys.sysprocesses table instead of sys.dm_exec_sessions. The reason why I did this was to ensure I get information for all the spid’s listed in the sys.sysprocesses table, unlike the sys.dm_exec_sessions which gives only information of running spid. Now I was little perplexed on how to poll the using the above tweak script in seconds.

Well, I was telling myself "Don’t worry, dude!, you will have the breakthrough". And yes, I did have the break when I learnt that we could schedule a SQL Agent Job to run in seconds by setting the value of the parameter @freq_subday_type to 2. If you set this value the Agent job runs every few seconds and by setting the  @freq_subday_interval.

I have the job running for past few weeks and gathering all the information I need from the system table, DMV and DMF’s. I hope this article helps you all!

Happy reading!

Performance tuning methodology September 11, 2008

Posted by msrviking in Performance tuning.
add a comment

Hello Guys,

I had a request from one of the project teams to help them in DB performance tuning. I was delighted to help and came up with a meeting plan. I had bunch of questions before I could jump for sharing the methodology. Here are the questions I fired back at the team.

  1. Which database technology would we look at, for performance
    tuning? I am assuming SQL Server.

  2. If SQL Server, which version of SQL Server would be looked at
    during performance tuning?

  3. What kind of performance tuning does the team want to do?
    (Queries, Index, Database, Instance, OS or Hardware)

  4. Is the performance tuning, for an application in production or
    application which is being developed?

  5. Is there any information you could provide that would be good
    for understanding of what has been done, what needs to be done?

To my surprise, even though I had mentioned that my thoughts on performance tuning would revolve around SQL Server, I was given a reply back by team saying that tuning would be on Sybase 15. Okkk, now what? I started googling around for some information on how to help in tuning Sybase databases. Well, after little searching and finding interesting articles, I stepped back a little while and thought "What am I doing?". And quickly it struck me, that we are looking only methodology and not at technology based tuning. Wouldn’t methodology be common across any db technology for performance tuning? The only set of differences would be from monitoring, analyzing tools during performance tuning. I have strong belief that if we know the methodology we could get into tuning of different db technology.

What do you guys think about it? Also if you have different set of questions than what I had, do let me know.

Happy reading!

SQL Server 2008 Webchat on Sept 23, 2008 September 11, 2008

Posted by msrviking in SQL Server 2008.
add a comment

There is a webchat on 8/23. Visit the below link for more details at Dan’s blogging site – SQL Server 2008 Webchat on Sept 23, 2008