move/upgrade MSDE to SQL 2000

Need some help with moving/upgrading 3 MSDE databases (stored locally on 3 different servers) to a single SQL 2000 server (new instance). Can this be done? If so, how??

Thanks for any responsesStop the MSSQL service. Find the MDF and LDF files (C:\Program Files\Microsoft SQL Server\MSSQL\Data\ ) and copy them to the SQL Server. Then attach the databases on the destination. Watch not to overlay any MDF/LDF files on the destination server.sql

Move stored procs from 2000 to 2005

It's easy to move the tables, but how do you transfer stored procs and triggers?A few ways you can do this.

If you want to move a whole database, you can either backup the database from SQL 2000 and restore to 2005, or detach\reattach.

If you want to be more selective, you can use SQL Enterprise Manager on 2000 to script out the specific stored procedures\triggers to a script file, then execute the script on SQL 2005 using SQL Management Studio.

Jeff.

move objects between mdf files

what is the easiest way to move objects (tables with data and stored procedures) from one sqlexpress database mdf to another mdf?

You can use SELECT...INTO as documented at http://msdn2.microsoft.com/en-us/library/ms190750.aspx. Just identify your table using four part naming so that you can direct it to the table in the other database.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

Move database from SQLS2k5 to SQLS2k5Express

Hello,

1.) Can I move a database complete with tables, diagrams, relationships, stored procedures, etc. from SQL Server 2005 to SQL Server 2005 Express and preserve all the work I did?

2.) Can I do the same from SQL Server 2000 to SQL Server 2005 Express and preserve all the work I did?

Thank you,

dbuchanan

Hi, there should be no problem with either of these scenarios, the only thing to be careful of are the restrictions in SQL Server Express.

Most restrictions are around size and performance, ie 4GB database size, 1 CPU, 1GB Buffer Cache, but there are also some others for instance, SQL Server Express does NOT support SSIS (DTS in SQL Server 2000), so if you are using these features of SQL Server Express, it would not be a simple move.

For more information on SQL Server Express features, see the books online http://msdn2.microsoft.com/en-us/ms165636(SQL.90).aspx

|||As Scott says this is very doable. You can use detach/attach functionality or the Copy Database Wizard for this pretty easily.|||

That isn't actually true, at all.

I've been fighting with SQL express for 3 days now to try and get it to attach a database correctly.

Firstly it will not attach it as a System Database, it creates a new instance for it, which while weird I understand.

Then when any script connects its throws this error :

Could not locate entry in sysdatabases for database 'attacheddatabase'. No entry found with that name. Make sure that the name is entered correctly. (severity 16)

Unless you connect to the system databases then it allows you to select the databases, though your not allowed to copy any tables, or move any databases to that area ........

Oh how I pray for a actual developer focused database from MS, i.e. one that has permissions that allow you do to what you want opposed to hindering you every step of the way ........

I'm fighting tooth and nail to get this whole project moved to a decent database now.

|||

Jemue, what connection string are you using to connect to your databse?

I have used SQL Express extensively, and never had any real problems connecting to database, but the user instance feature can get a bit confusing at times. Maybe if you give me a bit more detail about what you're trying to achieve, what technologies you are using, we may be able to help.

Also keep in mind that you don't have to use User instances, you can just connect to a database in exactly the same way you do in any other SKU of SQL Server.

Cheers

|||

Moving database from SQLS2k5 to SQLS2k5Express may be not easy!

I have obtained customer DB which was detached from SQL2K and attached it to SQL2005 Express.

I found that all tables are presented in form DBA.<table name> and simplest query

SELECT * FROM <table name>

results in error message saying something like "object name <table name> is incorrect" while query

SELECT * FROM DBA.<table name>

goes successfully.

Well, what should be done in this case?

Thank you.

Move database from SQLS2k5 to SQLS2k5Express

Hello,

1.) Can I move a database complete with tables, diagrams, relationships, stored procedures, etc. from SQL Server 2005 to SQL Server 2005 Express and preserve all the work I did?

2.) Can I do the same from SQL Server 2000 to SQL Server 2005 Express and preserve all the work I did?

Thank you,

dbuchanan

Hi, there should be no problem with either of these scenarios, the only thing to be careful of are the restrictions in SQL Server Express.

Most restrictions are around size and performance, ie 4GB database size, 1 CPU, 1GB Buffer Cache, but there are also some others for instance, SQL Server Express does NOT support SSIS (DTS in SQL Server 2000), so if you are using these features of SQL Server Express, it would not be a simple move.

For more information on SQL Server Express features, see the books online http://msdn2.microsoft.com/en-us/ms165636(SQL.90).aspx

|||As Scott says this is very doable. You can use detach/attach functionality or the Copy Database Wizard for this pretty easily.|||

That isn't actually true, at all.

I've been fighting with SQL express for 3 days now to try and get it to attach a database correctly.

Firstly it will not attach it as a System Database, it creates a new instance for it, which while weird I understand.

Then when any script connects its throws this error :

Could not locate entry in sysdatabases for database 'attacheddatabase'. No entry found with that name. Make sure that the name is entered correctly. (severity 16)

Unless you connect to the system databases then it allows you to select the databases, though your not allowed to copy any tables, or move any databases to that area ........

Oh how I pray for a actual developer focused database from MS, i.e. one that has permissions that allow you do to what you want opposed to hindering you every step of the way ........

I'm fighting tooth and nail to get this whole project moved to a decent database now.

|||

Jemue, what connection string are you using to connect to your databse?

I have used SQL Express extensively, and never had any real problems connecting to database, but the user instance feature can get a bit confusing at times. Maybe if you give me a bit more detail about what you're trying to achieve, what technologies you are using, we may be able to help.

Also keep in mind that you don't have to use User instances, you can just connect to a database in exactly the same way you do in any other SKU of SQL Server.

Cheers

|||

Moving database from SQLS2k5 to SQLS2k5Express may be not easy!

I have obtained customer DB which was detached from SQL2K and attached it to SQL2005 Express.

I found that all tables are presented in form DBA.<table name> and simplest query

SELECT * FROM <table name>

results in error message saying something like "object name <table name> is incorrect" while query

SELECT * FROM DBA.<table name>

goes successfully.

Well, what should be done in this case?

Thank you.

Move database from SQLS2k5 to SQLS2k5Express

Hello,

1.) Can I move a database complete with tables, diagrams, relationships, stored procedures, etc. from SQL Server 2005 to SQL Server 2005 Express and preserve all the work I did?

2.) Can I do the same from SQL Server 2000 to SQL Server 2005 Express and preserve all the work I did?

Thank you,

dbuchanan

Hi, there should be no problem with either of these scenarios, the only thing to be careful of are the restrictions in SQL Server Express.

Most restrictions are around size and performance, ie 4GB database size, 1 CPU, 1GB Buffer Cache, but there are also some others for instance, SQL Server Express does NOT support SSIS (DTS in SQL Server 2000), so if you are using these features of SQL Server Express, it would not be a simple move.

For more information on SQL Server Express features, see the books online http://msdn2.microsoft.com/en-us/ms165636(SQL.90).aspx

|||As Scott says this is very doable. You can use detach/attach functionality or the Copy Database Wizard for this pretty easily.|||

That isn't actually true, at all.

I've been fighting with SQL express for 3 days now to try and get it to attach a database correctly.

Firstly it will not attach it as a System Database, it creates a new instance for it, which while weird I understand.

Then when any script connects its throws this error :

Could not locate entry in sysdatabases for database 'attacheddatabase'. No entry found with that name. Make sure that the name is entered correctly. (severity 16)

Unless you connect to the system databases then it allows you to select the databases, though your not allowed to copy any tables, or move any databases to that area ........

Oh how I pray for a actual developer focused database from MS, i.e. one that has permissions that allow you do to what you want opposed to hindering you every step of the way ........

I'm fighting tooth and nail to get this whole project moved to a decent database now.

|||

Jemue, what connection string are you using to connect to your databse?

I have used SQL Express extensively, and never had any real problems connecting to database, but the user instance feature can get a bit confusing at times. Maybe if you give me a bit more detail about what you're trying to achieve, what technologies you are using, we may be able to help.

Also keep in mind that you don't have to use User instances, you can just connect to a database in exactly the same way you do in any other SKU of SQL Server.

Cheers

|||

Moving database from SQLS2k5 to SQLS2k5Express may be not easy!

I have obtained customer DB which was detached from SQL2K and attached it to SQL2005 Express.

I found that all tables are presented in form DBA.<table name> and simplest query

SELECT * FROM <table name>

results in error message saying something like "object name <table name> is incorrect" while query

SELECT * FROM DBA.<table name>

goes successfully.

Well, what should be done in this case?

Thank you.

Move database from SQLS2k5 to SQLS2k5Express

Hello,

1.) Can I move a database complete with tables, diagrams, relationships, stored procedures, etc. from SQL Server 2005 to SQL Server 2005 Express and preserve all the work I did?

2.) Can I do the same from SQL Server 2000 to SQL Server 2005 Express and preserve all the work I did?

Thank you,

dbuchanan

Hi, there should be no problem with either of these scenarios, the only thing to be careful of are the restrictions in SQL Server Express.

Most restrictions are around size and performance, ie 4GB database size, 1 CPU, 1GB Buffer Cache, but there are also some others for instance, SQL Server Express does NOT support SSIS (DTS in SQL Server 2000), so if you are using these features of SQL Server Express, it would not be a simple move.

For more information on SQL Server Express features, see the books online http://msdn2.microsoft.com/en-us/ms165636(SQL.90).aspx

|||As Scott says this is very doable. You can use detach/attach functionality or the Copy Database Wizard for this pretty easily.|||

That isn't actually true, at all.

I've been fighting with SQL express for 3 days now to try and get it to attach a database correctly.

Firstly it will not attach it as a System Database, it creates a new instance for it, which while weird I understand.

Then when any script connects its throws this error :

Could not locate entry in sysdatabases for database 'attacheddatabase'. No entry found with that name. Make sure that the name is entered correctly. (severity 16)

Unless you connect to the system databases then it allows you to select the databases, though your not allowed to copy any tables, or move any databases to that area ........

Oh how I pray for a actual developer focused database from MS, i.e. one that has permissions that allow you do to what you want opposed to hindering you every step of the way ........

I'm fighting tooth and nail to get this whole project moved to a decent database now.

|||

Jemue, what connection string are you using to connect to your databse?

I have used SQL Express extensively, and never had any real problems connecting to database, but the user instance feature can get a bit confusing at times. Maybe if you give me a bit more detail about what you're trying to achieve, what technologies you are using, we may be able to help.

Also keep in mind that you don't have to use User instances, you can just connect to a database in exactly the same way you do in any other SKU of SQL Server.

Cheers

|||

Moving database from SQLS2k5 to SQLS2k5Express may be not easy!

I have obtained customer DB which was detached from SQL2K and attached it to SQL2005 Express.

I found that all tables are presented in form DBA.<table name> and simplest query

SELECT * FROM <table name>

results in error message saying something like "object name <table name> is incorrect" while query

SELECT * FROM DBA.<table name>

goes successfully.

Well, what should be done in this case?

Thank you.

moving indexes to other filegroup

Hello,
I currently have the indexes stored in the primiray filegroup and am
wondering if there is a way to move them to a secondary filegroup and then
put this secondary filegroup on a different drive to improve performance. I
am running sql2k5. Thanks in advance.
Jake
Jake Smythe wrote:
> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance. I
> am running sql2k5. Thanks in advance.
> Jake
>
>
Have you looked at the syntax for CREATE INDEX in Books Online? It's
pretty well documented there how to put an index on a different filegroup.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Jake
You don't say how many drives the current database uses and if you are
moving the indexes to a different drive array or not? You would need to
analyse the index usage by your queries to determine if you would gain
benefit from moving the indexes, it could be that you move the data from some
tables by moving (dropping/creating) the clustered index and leave a
combination of different data/clustered indexes and non-clustered indexes on
both.
It could just be that if you added an extra file to primary filegroup or if
you added the disc(s) into a RAID array the performance increase may be more
substantial. Another thing worth looking at is if moving tempdb to the new
drive and leaving your database alone would help.
If you have not split the spindles that the log files and data files use,
then I would ignore any thought of extra file/filegroups and split them onto
the new disc(s) (possibly with the exception of if your database is read
only!!)
John
"Jake Smythe" wrote:

> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance. I
> am running sql2k5. Thanks in advance.
> Jake
>
>
|||John and Tracy,
Thanks for the replies. We currently have the data on one array with
raid 5 and we have the logs on another array using raid 1. We're going to be
moving to a cluster environment within the next couple of months so I
thought that if we have a third array for the indexes (suggestion on raid?)
we could boost performance even more. We currently have some replication on
some of these databases as well. So I was looking at a way to move the
clustered and non-clustered indexes to another filegroup that would be on
this third array via TSQL if possible. We have sql2k5 std. Is moving the
indexes to another drive array worth it? I did look at the create index on
bol. My assumption is that I would need to drop all of the existing indexes
and recreate them pointing to the new filegroup. If this is the case how can
I get the scripts just to generate clustered and non-clustered indexes from
existing indexes within a database? Any suggestions would be greatly
appreicated
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5E8BB7AA-D27A-4EFB-8654-430D7FF308A8@.microsoft.com...[vbcol=seagreen]
> Hi Jake
> You don't say how many drives the current database uses and if you are
> moving the indexes to a different drive array or not? You would need to
> analyse the index usage by your queries to determine if you would gain
> benefit from moving the indexes, it could be that you move the data from
> some
> tables by moving (dropping/creating) the clustered index and leave a
> combination of different data/clustered indexes and non-clustered indexes
> on
> both.
> It could just be that if you added an extra file to primary filegroup or
> if
> you added the disc(s) into a RAID array the performance increase may be
> more
> substantial. Another thing worth looking at is if moving tempdb to the new
> drive and leaving your database alone would help.
> If you have not split the spindles that the log files and data files use,
> then I would ignore any thought of extra file/filegroups and split them
> onto
> the new disc(s) (possibly with the exception of if your database is read
> only!!)
> John
>
> "Jake Smythe" wrote:
|||Jake Smythe wrote:
> John and Tracy,
> Thanks for the replies. We currently have the data on one array with
> raid 5 and we have the logs on another array using raid 1. We're going to be
> moving to a cluster environment within the next couple of months so I
> thought that if we have a third array for the indexes (suggestion on raid?)
> we could boost performance even more. We currently have some replication on
> some of these databases as well. So I was looking at a way to move the
> clustered and non-clustered indexes to another filegroup that would be on
> this third array via TSQL if possible. We have sql2k5 std. Is moving the
> indexes to another drive array worth it? I did look at the create index on
> bol. My assumption is that I would need to drop all of the existing indexes
> and recreate them pointing to the new filegroup. If this is the case how can
> I get the scripts just to generate clustered and non-clustered indexes from
> existing indexes within a database? Any suggestions would be greatly
> appreicated
>
Jake, I've dumped a script at
http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that
I've used to generate DROP and CREATE statements for indexes. You'll
need to modify it to suit your needs, but it should be pretty close to
working for you.
To be honest, I've never had a performance problem required splitting
indexes off into a seperate filegroup. Usually identifying and tweaking
bad queries and/or missing indexes does more to help performance than
anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4554D0B8.8090503@.realsqlguy.com...
> Jake Smythe wrote:
> Jake, I've dumped a script at
> http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that I've
> used to generate DROP and CREATE statements for indexes. You'll need to
> modify it to suit your needs, but it should be pretty close to working for
> you.
> To be honest, I've never had a performance problem required splitting
> indexes off into a seperate filegroup. Usually identifying and tweaking
> bad queries and/or missing indexes does more to help performance than
> anything.
>
I'll go further. Splitting indexes off to a separate file group is almost
always a waste of time. Especially since you note that you will be moving
to new hardware. You should be moving to 64bit SQL Server on 64bit Windows.
On this platform, the large amounts of memory available for cache will make
your physical disk access patterns mostly unpredictable. If you dedicate a
volume for your indexes, most likely that volume will remain idle because
the indexes on are either completely cached, or completely unused.
Instead, when you add a volume to your server, dedicate it to Logs, TempDB
or just add an additional file to your main file group on the new volume.
SQL Server will automatically spread the IO evenly across all the files in
the file group.
David
|||Guys,
Thanks for the responses. I wasn't sure it was really worth the headache
but figured I'd ask Thanks again for the help/suggestions.
Jake
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%239qSYtQBHHA.4992@.TK2MSFTNGP03.phx.gbl...
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4554D0B8.8090503@.realsqlguy.com...
> I'll go further. Splitting indexes off to a separate file group is almost
> always a waste of time. Especially since you note that you will be moving
> to new hardware. You should be moving to 64bit SQL Server on 64bit
> Windows. On this platform, the large amounts of memory available for cache
> will make your physical disk access patterns mostly unpredictable. If you
> dedicate a volume for your indexes, most likely that volume will remain
> idle because the indexes on are either completely cached, or completely
> unused.
> Instead, when you add a volume to your server, dedicate it to Logs, TempDB
> or just add an additional file to your main file group on the new volume.
> SQL Server will automatically spread the IO evenly across all the files in
> the file group.
> David

moving indexes to other filegroup

Hello,
I currently have the indexes stored in the primiray filegroup and am
wondering if there is a way to move them to a secondary filegroup and then
put this secondary filegroup on a different drive to improve performance. I
am running sql2k5. Thanks in advance.
JakeJake Smythe wrote:
> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance. I
> am running sql2k5. Thanks in advance.
> Jake
>
>
Have you looked at the syntax for CREATE INDEX in Books Online? It's
pretty well documented there how to put an index on a different filegroup.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Jake
You don't say how many drives the current database uses and if you are
moving the indexes to a different drive array or not? You would need to
analyse the index usage by your queries to determine if you would gain
benefit from moving the indexes, it could be that you move the data from some
tables by moving (dropping/creating) the clustered index and leave a
combination of different data/clustered indexes and non-clustered indexes on
both.
It could just be that if you added an extra file to primary filegroup or if
you added the disc(s) into a RAID array the performance increase may be more
substantial. Another thing worth looking at is if moving tempdb to the new
drive and leaving your database alone would help.
If you have not split the spindles that the log files and data files use,
then I would ignore any thought of extra file/filegroups and split them onto
the new disc(s) (possibly with the exception of if your database is read
only!!)
John
"Jake Smythe" wrote:
> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance. I
> am running sql2k5. Thanks in advance.
> Jake
>
>|||John and Tracy,
Thanks for the replies. We currently have the data on one array with
raid 5 and we have the logs on another array using raid 1. We're going to be
moving to a cluster environment within the next couple of months so I
thought that if we have a third array for the indexes (suggestion on raid?)
we could boost performance even more. We currently have some replication on
some of these databases as well. So I was looking at a way to move the
clustered and non-clustered indexes to another filegroup that would be on
this third array via TSQL if possible. We have sql2k5 std. Is moving the
indexes to another drive array worth it? I did look at the create index on
bol. My assumption is that I would need to drop all of the existing indexes
and recreate them pointing to the new filegroup. If this is the case how can
I get the scripts just to generate clustered and non-clustered indexes from
existing indexes within a database? Any suggestions would be greatly
appreicated
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5E8BB7AA-D27A-4EFB-8654-430D7FF308A8@.microsoft.com...
> Hi Jake
> You don't say how many drives the current database uses and if you are
> moving the indexes to a different drive array or not? You would need to
> analyse the index usage by your queries to determine if you would gain
> benefit from moving the indexes, it could be that you move the data from
> some
> tables by moving (dropping/creating) the clustered index and leave a
> combination of different data/clustered indexes and non-clustered indexes
> on
> both.
> It could just be that if you added an extra file to primary filegroup or
> if
> you added the disc(s) into a RAID array the performance increase may be
> more
> substantial. Another thing worth looking at is if moving tempdb to the new
> drive and leaving your database alone would help.
> If you have not split the spindles that the log files and data files use,
> then I would ignore any thought of extra file/filegroups and split them
> onto
> the new disc(s) (possibly with the exception of if your database is read
> only!!)
> John
>
> "Jake Smythe" wrote:
>> Hello,
>> I currently have the indexes stored in the primiray filegroup and am
>> wondering if there is a way to move them to a secondary filegroup and
>> then
>> put this secondary filegroup on a different drive to improve performance.
>> I
>> am running sql2k5. Thanks in advance.
>> Jake
>>
>>|||Jake Smythe wrote:
> John and Tracy,
> Thanks for the replies. We currently have the data on one array with
> raid 5 and we have the logs on another array using raid 1. We're going to be
> moving to a cluster environment within the next couple of months so I
> thought that if we have a third array for the indexes (suggestion on raid?)
> we could boost performance even more. We currently have some replication on
> some of these databases as well. So I was looking at a way to move the
> clustered and non-clustered indexes to another filegroup that would be on
> this third array via TSQL if possible. We have sql2k5 std. Is moving the
> indexes to another drive array worth it? I did look at the create index on
> bol. My assumption is that I would need to drop all of the existing indexes
> and recreate them pointing to the new filegroup. If this is the case how can
> I get the scripts just to generate clustered and non-clustered indexes from
> existing indexes within a database? Any suggestions would be greatly
> appreicated
>
Jake, I've dumped a script at
http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that
I've used to generate DROP and CREATE statements for indexes. You'll
need to modify it to suit your needs, but it should be pretty close to
working for you.
To be honest, I've never had a performance problem required splitting
indexes off into a seperate filegroup. Usually identifying and tweaking
bad queries and/or missing indexes does more to help performance than
anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4554D0B8.8090503@.realsqlguy.com...
> Jake Smythe wrote:
>> John and Tracy,
>> Thanks for the replies. We currently have the data on one array with
>> raid 5 and we have the logs on another array using raid 1. We're going to
>> be moving to a cluster environment within the next couple of months so I
>> thought that if we have a third array for the indexes (suggestion on
>> raid?) we could boost performance even more. We currently have some
>> replication on some of these databases as well. So I was looking at a way
>> to move the clustered and non-clustered indexes to another filegroup that
>> would be on this third array via TSQL if possible. We have sql2k5 std. Is
>> moving the indexes to another drive array worth it? I did look at the
>> create index on bol. My assumption is that I would need to drop all of
>> the existing indexes and recreate them pointing to the new filegroup. If
>> this is the case how can I get the scripts just to generate clustered and
>> non-clustered indexes from existing indexes within a database? Any
>> suggestions would be greatly appreicated
> Jake, I've dumped a script at
> http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that I've
> used to generate DROP and CREATE statements for indexes. You'll need to
> modify it to suit your needs, but it should be pretty close to working for
> you.
> To be honest, I've never had a performance problem required splitting
> indexes off into a seperate filegroup. Usually identifying and tweaking
> bad queries and/or missing indexes does more to help performance than
> anything.
>
I'll go further. Splitting indexes off to a separate file group is almost
always a waste of time. Especially since you note that you will be moving
to new hardware. You should be moving to 64bit SQL Server on 64bit Windows.
On this platform, the large amounts of memory available for cache will make
your physical disk access patterns mostly unpredictable. If you dedicate a
volume for your indexes, most likely that volume will remain idle because
the indexes on are either completely cached, or completely unused.
Instead, when you add a volume to your server, dedicate it to Logs, TempDB
or just add an additional file to your main file group on the new volume.
SQL Server will automatically spread the IO evenly across all the files in
the file group.
David|||Guys,
Thanks for the responses. I wasn't sure it was really worth the headache
but figured I'd ask :) Thanks again for the help/suggestions.
Jake
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%239qSYtQBHHA.4992@.TK2MSFTNGP03.phx.gbl...
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4554D0B8.8090503@.realsqlguy.com...
>> Jake Smythe wrote:
>> John and Tracy,
>> Thanks for the replies. We currently have the data on one array with
>> raid 5 and we have the logs on another array using raid 1. We're going
>> to be moving to a cluster environment within the next couple of months
>> so I thought that if we have a third array for the indexes (suggestion
>> on raid?) we could boost performance even more. We currently have some
>> replication on some of these databases as well. So I was looking at a
>> way to move the clustered and non-clustered indexes to another filegroup
>> that would be on this third array via TSQL if possible. We have sql2k5
>> std. Is moving the indexes to another drive array worth it? I did look
>> at the create index on bol. My assumption is that I would need to drop
>> all of the existing indexes and recreate them pointing to the new
>> filegroup. If this is the case how can I get the scripts just to
>> generate clustered and non-clustered indexes from existing indexes
>> within a database? Any suggestions would be greatly appreicated
>>
>> Jake, I've dumped a script at
>> http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that
>> I've used to generate DROP and CREATE statements for indexes. You'll
>> need to modify it to suit your needs, but it should be pretty close to
>> working for you.
>> To be honest, I've never had a performance problem required splitting
>> indexes off into a seperate filegroup. Usually identifying and tweaking
>> bad queries and/or missing indexes does more to help performance than
>> anything.
> I'll go further. Splitting indexes off to a separate file group is almost
> always a waste of time. Especially since you note that you will be moving
> to new hardware. You should be moving to 64bit SQL Server on 64bit
> Windows. On this platform, the large amounts of memory available for cache
> will make your physical disk access patterns mostly unpredictable. If you
> dedicate a volume for your indexes, most likely that volume will remain
> idle because the indexes on are either completely cached, or completely
> unused.
> Instead, when you add a volume to your server, dedicate it to Logs, TempDB
> or just add an additional file to your main file group on the new volume.
> SQL Server will automatically spread the IO evenly across all the files in
> the file group.
> David

moving indexes to other filegroup

Hello,
I currently have the indexes stored in the primiray filegroup and am
wondering if there is a way to move them to a secondary filegroup and then
put this secondary filegroup on a different drive to improve performance. I
am running sql2k5. Thanks in advance.
JakeJake Smythe wrote:
> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance.
I
> am running sql2k5. Thanks in advance.
> Jake
>
>
Have you looked at the syntax for CREATE INDEX in Books Online? It's
pretty well documented there how to put an index on a different filegroup.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Jake
You don't say how many drives the current database uses and if you are
moving the indexes to a different drive array or not? You would need to
analyse the index usage by your queries to determine if you would gain
benefit from moving the indexes, it could be that you move the data from som
e
tables by moving (dropping/creating) the clustered index and leave a
combination of different data/clustered indexes and non-clustered indexes on
both.
It could just be that if you added an extra file to primary filegroup or if
you added the disc(s) into a RAID array the performance increase may be more
substantial. Another thing worth looking at is if moving tempdb to the new
drive and leaving your database alone would help.
If you have not split the spindles that the log files and data files use,
then I would ignore any thought of extra file/filegroups and split them onto
the new disc(s) (possibly with the exception of if your database is read
only!!)
John
"Jake Smythe" wrote:

> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance.
I
> am running sql2k5. Thanks in advance.
> Jake
>
>|||John and Tracy,
Thanks for the replies. We currently have the data on one array with
raid 5 and we have the logs on another array using raid 1. We're going to be
moving to a cluster environment within the next couple of months so I
thought that if we have a third array for the indexes (suggestion on raid?)
we could boost performance even more. We currently have some replication on
some of these databases as well. So I was looking at a way to move the
clustered and non-clustered indexes to another filegroup that would be on
this third array via TSQL if possible. We have sql2k5 std. Is moving the
indexes to another drive array worth it? I did look at the create index on
bol. My assumption is that I would need to drop all of the existing indexes
and recreate them pointing to the new filegroup. If this is the case how can
I get the scripts just to generate clustered and non-clustered indexes from
existing indexes within a database? Any suggestions would be greatly
appreicated
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5E8BB7AA-D27A-4EFB-8654-430D7FF308A8@.microsoft.com...[vbcol=seagreen]
> Hi Jake
> You don't say how many drives the current database uses and if you are
> moving the indexes to a different drive array or not? You would need to
> analyse the index usage by your queries to determine if you would gain
> benefit from moving the indexes, it could be that you move the data from
> some
> tables by moving (dropping/creating) the clustered index and leave a
> combination of different data/clustered indexes and non-clustered indexes
> on
> both.
> It could just be that if you added an extra file to primary filegroup or
> if
> you added the disc(s) into a RAID array the performance increase may be
> more
> substantial. Another thing worth looking at is if moving tempdb to the new
> drive and leaving your database alone would help.
> If you have not split the spindles that the log files and data files use,
> then I would ignore any thought of extra file/filegroups and split them
> onto
> the new disc(s) (possibly with the exception of if your database is read
> only!!)
> John
>
> "Jake Smythe" wrote:
>|||Jake Smythe wrote:
> John and Tracy,
> Thanks for the replies. We currently have the data on one array with
> raid 5 and we have the logs on another array using raid 1. We're going to
be
> moving to a cluster environment within the next couple of months so I
> thought that if we have a third array for the indexes (suggestion on raid?
)
> we could boost performance even more. We currently have some replication o
n
> some of these databases as well. So I was looking at a way to move the
> clustered and non-clustered indexes to another filegroup that would be on
> this third array via TSQL if possible. We have sql2k5 std. Is moving the
> indexes to another drive array worth it? I did look at the create index on
> bol. My assumption is that I would need to drop all of the existing indexe
s
> and recreate them pointing to the new filegroup. If this is the case how c
an
> I get the scripts just to generate clustered and non-clustered indexes fro
m
> existing indexes within a database? Any suggestions would be greatly
> appreicated
>
Jake, I've dumped a script at
http://realsqlguy.com/downloads/01_...rsNonUnique.sql that
I've used to generate DROP and CREATE statements for indexes. You'll
need to modify it to suit your needs, but it should be pretty close to
working for you.
To be honest, I've never had a performance problem required splitting
indexes off into a seperate filegroup. Usually identifying and tweaking
bad queries and/or missing indexes does more to help performance than
anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4554D0B8.8090503@.realsqlguy.com...
> Jake Smythe wrote:
> Jake, I've dumped a script at
> http://realsqlguy.com/downloads/01_...rsNonUnique.sql that I've
> used to generate DROP and CREATE statements for indexes. You'll need to
> modify it to suit your needs, but it should be pretty close to working for
> you.
> To be honest, I've never had a performance problem required splitting
> indexes off into a seperate filegroup. Usually identifying and tweaking
> bad queries and/or missing indexes does more to help performance than
> anything.
>
I'll go further. Splitting indexes off to a separate file group is almost
always a waste of time. Especially since you note that you will be moving
to new hardware. You should be moving to 64bit SQL Server on 64bit Windows.
On this platform, the large amounts of memory available for cache will make
your physical disk access patterns mostly unpredictable. If you dedicate a
volume for your indexes, most likely that volume will remain idle because
the indexes on are either completely cached, or completely unused.
Instead, when you add a volume to your server, dedicate it to Logs, TempDB
or just add an additional file to your main file group on the new volume.
SQL Server will automatically spread the IO evenly across all the files in
the file group.
David|||Guys,
Thanks for the responses. I wasn't sure it was really worth the headache
but figured I'd ask Thanks again for the help/suggestions.
Jake
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%239qSYtQBHHA.4992@.TK2MSFTNGP03.phx.gbl...
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4554D0B8.8090503@.realsqlguy.com...
> I'll go further. Splitting indexes off to a separate file group is almost
> always a waste of time. Especially since you note that you will be moving
> to new hardware. You should be moving to 64bit SQL Server on 64bit
> Windows. On this platform, the large amounts of memory available for cache
> will make your physical disk access patterns mostly unpredictable. If you
> dedicate a volume for your indexes, most likely that volume will remain
> idle because the indexes on are either completely cached, or completely
> unused.
> Instead, when you add a volume to your server, dedicate it to Logs, TempDB
> or just add an additional file to your main file group on the new volume.
> SQL Server will automatically spread the IO evenly across all the files in
> the file group.
> David

Move database

Hello,

What is the easiest way to move a database from one server to another with the all table structures, view and stored procedures?

Thanks,

For just the structure (not the data) you can create a create script for the database (in enterprise Manager, right click any object in the database and Generate SQL Script should be an option).

For everything, including data, you can detatch the database, move the physical database file, and then attach the database file in the new location.

DTS will also allow you to move data and tables.

|||Would Backup?Restore work for this?|||to restore structure and data, yes.

Moving indexes from a filegroup to another

Hi,
I have a 65 Gb database stored on a single filegroup and I would like
to move non-clustered indexes to another filegroup.
I'm thinking of the following process:
_ Backup initial database
_ Create new empty database with two filegroups (one for data, one
for indexes) (the database is created by a software that defines all
the objects on a single filegroup)
_ Moving indexes (definition) from a filegroup to another while the
database is empty.
_ Restore data and indexes from the previous backup so that data go
the data filegroup and indexes go to the indexes filegroup.
Another solution would be to drop the indexes and recreate them on the
new filegroup but moving 65 Gb will probably take a very long time.
Any ideas ?
Thanks !> I have a 65 Gb database stored on a single filegroup and I would like
> to move non-clustered indexes to another filegroup.
The only index you can move is the clustered index (which effectively takes
the table and all the non-clustered indexes with it). You can do this by
dropping the clustered index and re-creating it on the new filegroup, by
using ON <filegroup>.|||If you want to separate the data (Heaps & Clustered indexes) from the
non-clustered indexes you will need to drop the non-clustered indexes and
recreate them on the new filegroup. YOu won't move all 65GB since the table
itself should be the majority of the data. There is no need to restore
anything.
Andrew J. Kelly SQL MVP
<florent.jeannot@.gmail.com> wrote in message
news:1139511741.514031.76310@.o13g2000cwo.googlegroups.com...
> Hi,
> I have a 65 Gb database stored on a single filegroup and I would like
> to move non-clustered indexes to another filegroup.
> I'm thinking of the following process:
> _ Backup initial database
> _ Create new empty database with two filegroups (one for data, one
> for indexes) (the database is created by a software that defines all
> the objects on a single filegroup)
> _ Moving indexes (definition) from a filegroup to another while the
> database is empty.
> _ Restore data and indexes from the previous backup so that data go
> the data filegroup and indexes go to the indexes filegroup.
> Another solution would be to drop the indexes and recreate them on the
> new filegroup but moving 65 Gb will probably take a very long time.
> Any ideas ?
> Thanks !
>|||Thanks Andrew. That was my first thought, but actually I was wondering
whether there would be a faster way to do it.