Data transformation services: migrating

Showing posts with label migrating. Show all posts

Thursday, March 29, 2012

Data storage question

I am migrating Sybase databases to SQL 2000. For the most part I am using a
Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
problem that I'm running into is that the size of the databases are 3, 4 or
more times bigger in SQL then they where in Sybase. The database that I
moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
Some mentioned to me to how SQL is setup in regards to number of rows per
page. I've let to find anything that will help me to look this information
up. I've been through sp_configure but I didn't see anything that jump out
at me. Any help would be nice.
TIA All.
JoeIt's probably due to lots of page splits. Did you add the indexes before or
after you imported the data? Make sure each table has a clustered index and
try reindexing the table. That should free up a lot of space and then you
can do a one time shrink to a reasonable size. You will want to reindex
again after the shrink though since the shrink moves data around again.
--
Andrew J. Kelly SQL MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
>I am migrating Sybase databases to SQL 2000. For the most part I am using
>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
>problem that I'm running into is that the size of the databases are 3, 4 or
>more times bigger in SQL then they where in Sybase. The database that I
>moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
>Some mentioned to me to how SQL is setup in regards to number of rows per
>page. I've let to find anything that will help me to look this information
>up. I've been through sp_configure but I didn't see anything that jump out
>at me. Any help would be nice.
> TIA All.
> Joe
>|||Thanks Andrew, I'll take a look and see about the indexes.
JD
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ue2C9vlrFHA.3440@.TK2MSFTNGP10.phx.gbl...
> It's probably due to lots of page splits. Did you add the indexes before
> or after you imported the data? Make sure each table has a clustered
> index and try reindexing the table. That should free up a lot of space
> and then you can do a one time shrink to a reasonable size. You will want
> to reindex again after the shrink though since the shrink moves data
> around again.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am using
>>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
>>The problem that I'm running into is that the size of the databases are 3,
>>4 or more times bigger in SQL then they where in Sybase. The database that
>>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
>>why' Some mentioned to me to how SQL is setup in regards to number of
>>rows per page. I've let to find anything that will help me to look this
>>information up. I've been through sp_configure but I didn't see anything
>>that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>|||reindexing your database will olso increase your performance as well as it
will organize the rows splited into pages
remember to make a backup before and after of this process and that the
database will not be abailable during this time
use dbcc dbreindex instead of dbcc indexdefrag
"Joe D" wrote:
> Thanks Andrew, I'll take a look and see about the indexes.
> JD
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ue2C9vlrFHA.3440@.TK2MSFTNGP10.phx.gbl...
> > It's probably due to lots of page splits. Did you add the indexes before
> > or after you imported the data? Make sure each table has a clustered
> > index and try reindexing the table. That should free up a lot of space
> > and then you can do a one time shrink to a reasonable size. You will want
> > to reindex again after the shrink though since the shrink moves data
> > around again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Joe D" <jkdriscoll@.qg.com> wrote in message
> > news:df4s9i$j23$1@.sxnews1.qg.com...
> >>I am migrating Sybase databases to SQL 2000. For the most part I am using
> >>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
> >>The problem that I'm running into is that the size of the databases are 3,
> >>4 or more times bigger in SQL then they where in Sybase. The database that
> >>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
> >>why' Some mentioned to me to how SQL is setup in regards to number of
> >>rows per page. I've let to find anything that will help me to look this
> >>information up. I've been through sp_configure but I didn't see anything
> >>that jump out at me. Any help would be nice.
> >>
> >> TIA All.
> >>
> >> Joe
> >>
> >
> >
>
>|||Hi Joe
Another reason for ballooning of db size could be if you have a lot of fixed
length columns that have a lot NULL values. I believe Sybase still works
like older versions of SQL Server, and any column that is NULL takes 0
bytes. In SQL Server 7 and 2000, a fixed width column takes the full number
of bytes, whether it is NULL or not..
HTH
Kalen Delaney
www.solidqualitylearning.com
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
> I am migrating Sybase databases to SQL 2000. For the most part I am using
> a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
> The problem that I'm running into is that the size of the databases are 3,
> 4 or more times bigger in SQL then they where in Sybase. The database that
> I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
> why' Some mentioned to me to how SQL is setup in regards to number of
> rows per page. I've let to find anything that will help me to look this
> information up. I've been through sp_configure but I didn't see anything
> that jump out at me. Any help would be nice.
> TIA All.
> Joe
>|||Very good point Kalen.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi Joe
> Another reason for ballooning of db size could be if you have a lot of
> fixed length columns that have a lot NULL values. I believe Sybase still
> works like older versions of SQL Server, and any column that is NULL takes
> 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the full
> number of bytes, whether it is NULL or not..
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the databases
>> are 3, 4 or more times bigger in SQL then they where in Sybase. The
>> database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>> like to know why' Some mentioned to me to how SQL is setup in regards to
>> number of rows per page. I've let to find anything that will help me to
>> look this information up. I've been through sp_configure but I didn't see
>> anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>
>|||Another explanation for the increased space requirements is that clustered
index keys are also stored in non-clustered index leaf pages. These keys
are used as 'pointers' to the data. This can add up if you have wide
clustered index keys and a lot of non-clustered indexes
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
>I am migrating Sybase databases to SQL 2000. For the most part I am using
>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
>problem that I'm running into is that the size of the databases are 3, 4 or
>more times bigger in SQL then they where in Sybase. The database that I
>moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
>Some mentioned to me to how SQL is setup in regards to number of rows per
>page. I've let to find anything that will help me to look this information
>up. I've been through sp_configure but I didn't see anything that jump out
>at me. Any help would be nice.
> TIA All.
> Joe
>|||Morning Kalen,
This was my thought but I seemed unable to find any supporting
documentation. I appreciate the feed back.
Also, Andrew's suggestion of re-indexing has proved to be of help. Now I
need to go back through the databases that I have already migrated and
re-index those.
Thanks much,
Joe
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi Joe
> Another reason for ballooning of db size could be if you have a lot of
> fixed length columns that have a lot NULL values. I believe Sybase still
> works like older versions of SQL Server, and any column that is NULL takes
> 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the full
> number of bytes, whether it is NULL or not..
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the databases
>> are 3, 4 or more times bigger in SQL then they where in Sybase. The
>> database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>> like to know why' Some mentioned to me to how SQL is setup in regards to
>> number of rows per page. I've let to find anything that will help me to
>> look this information up. I've been through sp_configure but I didn't see
>> anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>
>|||Are there any work around for this Dan?
Thanks for you input.
Joe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
> Another explanation for the increased space requirements is that clustered
> index keys are also stored in non-clustered index leaf pages. These keys
> are used as 'pointers' to the data. This can add up if you have wide
> clustered index keys and a lot of non-clustered indexes
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am using
>>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
>>The problem that I'm running into is that the size of the databases are 3,
>>4 or more times bigger in SQL then they where in Sybase. The database that
>>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
>>why' Some mentioned to me to how SQL is setup in regards to number of
>>rows per page. I've let to find anything that will help me to look this
>>information up. I've been through sp_configure but I didn't see anything
>>that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>|||Regarding index space requirements, I can only suggest that you re-evaluate
your indexing strategy. Keep in mind that although storing the clustered
index key in non-clustered indexes may increase disk space requirements, it
also reduces non-clustered index maintenance because the non-clustered
indexes won't need to change when page splits occur. Also, non-clustered
indexes can better cover certain queries.
As with any DBMS migration, you need to consider architectural differences
in your migration. A simple migration tool may get you part of the way
there but additional work is often needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df7052$vdu$1@.sxnews1.qg.com...
> Are there any work around for this Dan?
> Thanks for you input.
> Joe
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
>> Another explanation for the increased space requirements is that
>> clustered index keys are also stored in non-clustered index leaf pages.
>> These keys are used as 'pointers' to the data. This can add up if you
>> have wide clustered index keys and a lot of non-clustered indexes
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am
>>using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>>tool. The problem that I'm running into is that the size of the databases
>>are 3, 4 or more times bigger in SQL then they where in Sybase. The
>>database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>>like to know why' Some mentioned to me to how SQL is setup in regards to
>>number of rows per page. I've let to find anything that will help me to
>>look this information up. I've been through sp_configure but I didn't see
>>anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>|||Thanks Dan for your advise and it does help.
Joe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OiG%23X3vrFHA.2588@.tk2msftngp13.phx.gbl...
> Regarding index space requirements, I can only suggest that you
> re-evaluate your indexing strategy. Keep in mind that although storing
> the clustered index key in non-clustered indexes may increase disk space
> requirements, it also reduces non-clustered index maintenance because the
> non-clustered indexes won't need to change when page splits occur. Also,
> non-clustered indexes can better cover certain queries.
> As with any DBMS migration, you need to consider architectural differences
> in your migration. A simple migration tool may get you part of the way
> there but additional work is often needed.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df7052$vdu$1@.sxnews1.qg.com...
>> Are there any work around for this Dan?
>> Thanks for you input.
>> Joe
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
>> Another explanation for the increased space requirements is that
>> clustered index keys are also stored in non-clustered index leaf pages.
>> These keys are used as 'pointers' to the data. This can add up if you
>> have wide clustered index keys and a lot of non-clustered indexes
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am
>>using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>>tool. The problem that I'm running into is that the size of the
>>databases are 3, 4 or more times bigger in SQL then they where in
>>Sybase. The database that I moved today went from 13GB in Sybase to 81GB
>>in SQL. I'd like to know why' Some mentioned to me to how SQL is setup
>>in regards to number of rows per page. I've let to find anything that
>>will help me to look this information up. I've been through sp_configure
>>but I didn't see anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>>
>|||Supporting documentation?
I guess you haven't read "Inside SQL Server". :-)
It shows you down to the byte level how rows are organized on your pages for
each datatype, and even has examples for null columns of large width. It
also provides the tools for you to examine the page layouts for yourself so
you don't have to take anybody's word for it. You can answer you own
questions.
The book also talks a lot about indexing strategies. (And yes, I am the
author.)
HTH
Kalen Delaney
www.solidqualitylearning.com
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df7001$vdq$1@.sxnews1.qg.com...
> Morning Kalen,
> This was my thought but I seemed unable to find any supporting
> documentation. I appreciate the feed back.
> Also, Andrew's suggestion of re-indexing has proved to be of help. Now
> I need to go back through the databases that I have already migrated and
> re-index those.
> Thanks much,
> Joe
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
>> Hi Joe
>> Another reason for ballooning of db size could be if you have a lot of
>> fixed length columns that have a lot NULL values. I believe Sybase still
>> works like older versions of SQL Server, and any column that is NULL
>> takes 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the
>> full number of bytes, whether it is NULL or not..
>> HTH
>> Kalen Delaney
>> www.solidqualitylearning.com
>>
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the
>> databases are 3, 4 or more times bigger in SQL then they where in
>> Sybase. The database that I moved today went from 13GB in Sybase to 81GB
>> in SQL. I'd like to know why' Some mentioned to me to how SQL is setup
>> in regards to number of rows per page. I've let to find anything that
>> will help me to look this information up. I've been through sp_configure
>> but I didn't see anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>>
>
>

Wednesday, March 7, 2012

Data mining formation for SQL Server 2005

Hi,

I already have data mining experience with different software, but my company is now migrating to SQL Server 2005 and since it include a data mining module, I would like to learn how to use it properly (Analysis Services, DMX language DMX, …).

Is there companies that offers formation in data mining for SQL Server 2005 ?

Thanks!

Tony

You mean training? Solid Quality Learning offers some training in SQL 2005 DM.|||

Yes, I meant training. Thanks !

Data mining formation for SQL Server 2005

Hi,

Is there companies that offers formation in data mining for SQL Server 2005 ?

Thanks!

Tony

You mean training? Solid Quality Learning offers some training in SQL 2005 DM.|||

Yes, I meant training. Thanks !

Data migration overview help

Hello,

This is my first project with SSIS and I'm having some problems migrating our current data import to it. Generally: right now we're pulling hourly data (which has a timestamp on it) in from a customer's db(which can't be modified, and is big and growing quickly) into our db. To do this we have been using a field to keep track of the last data download time (in our db) and then using that then when querying the customer's db to reduce the number of rows returned.

Will a similar system work in SSIS? I'm having trouble pulling the last download date out of the "target" database and using it in the query on the customer's db. Any advice on what I should be looking into to do this and am I headed in the right direction or just not thinking about this in a SSIS way?

How powerful/common are variables in SSIS and should I be trying to use them here?

Thanks,
Jeff

Variables are the way to go with this. Use an ExecuteSQL task to grab the timestamp and store it in a variable. Then either use the variable as a parameter on your data source SELECT statements in the data flow, or build the SQL dynamically, using expression based variables. Generally, if your data source is not SQL Server, it seems like you are better off using the expression based variables.

Saturday, February 25, 2012

Data migration into SQL server 2005 from xml files

Friends

Any one of you please provide step by step info for migrating sql server 2005 from xml files.

cheers :)

satish

Try to provide as much detail as you can so that someone can help you.

Data Migration

Hi
We are in the process of migrating Data from SQL SERVER 2000 to
Oracle 9i.

Can anyone let me know what are the steps that needs to be followed
in achieving the same.

If there's any specific documentation also available , please let me know
Any pointers would be of great help.

Thanks
Shankar GokuleI think you'd get a more sympathetic ear over here:

http://www.dbforums.com/f4/

Besides...migration is migrattion...

Copy structure (in ERWin is nice), forward engineer (change the code to match the platform)...dump data, load to stage, audit data, load new structure...

data mapping and migration

Has anyone used DTS packages for migrating old data to a new schema?

If so are there any tutorials on this?

I'd prefer not to do this by hand. ;-)Just use DTS graphic wizard.
BOL is the best tutorial|||Try the wizard in Enterprise Manager. That's a nice easy intro into it.|||I have a simple package where I want to dump data from one table to another. Its erroring out on this line.

DTSDestination("TaxExempt") =1

The destination field is int.

I don't receive any errors when i have the following:

DTSDestination("ShippingWeight") = DTSSource("weight")

for some reason it doesnt like the hardcoded value i supplied it.

Any ideas why?

Thanks for the help.

Thursday, March 29, 2012

Data storage question

Data storage question

Data storage question

Wednesday, March 7, 2012

Data mining formation for SQL Server 2005

Data mining formation for SQL Server 2005

Data migration overview help

Saturday, February 25, 2012

Data migration into SQL server 2005 from xml files

Data Migration

data mapping and migration

Data transformation services

Blog Archive

About Me