Create indexes - own File Group

With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173725223.934126.197140@.30g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?
>

Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.

HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.

Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.

Quote:

Originally Posted by

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||Greg,
Its a data warehouse. I have tools that could rebuild an empty
database pretty quickly (few hours) and I have daily backups. Builds
take about 2 hours, then its read only the rest of the day.
I use enterprise because of our licensing.

Thanks for the info!

Quote:

Originally Posted by

>
Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.
>
HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.
>
Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.
>

Quote:

Originally Posted by

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob

>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

CREATE FULLTEXT CATALOG x ON FILEGROUP 'PRIMARY' issue

Books online recommends creating the FTS catalog in a new filegroup
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:

> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.

CREATE FULLTEXT CATALOG x ON FILEGROUP 'PRIMARY' issue

Books online recommends creating the FTS catalog in a new filegroup
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:
> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.

Create Database with Filegroups restore Database without Filegroup

I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
server name and REPT as the name instance. This is Active\Active Cluster with
Windows 2000 Advanced Server as the OS.
I have a new server and would like to have filegroups for the database. Can
I create filegroups on the new server and restore the backup from
Austin_Sales\REPT in the database with filegroups?
Please help me with this task.
Thanks,
Restore will give you (essentially) a binary image of what is inside the database. I.e., you cannot
change filegroup layout through backup/restore. You'd have to do that before the backup or after the
restore...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
> I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
> server name and REPT as the name instance. This is Active\Active Cluster with
> Windows 2000 Advanced Server as the OS.
> I have a new server and would like to have filegroups for the database. Can
> I create filegroups on the new server and restore the backup from
> Austin_Sales\REPT in the database with filegroups?
> Please help me with this task.
> Thanks,

Create Database with Filegroups restore Database without Filegroup

I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
server name and REPT as the name instance. This is Active\Active Cluster with
Windows 2000 Advanced Server as the OS.
I have a new server and would like to have filegroups for the database. Can
I create filegroups on the new server and restore the backup from
Austin_Sales\REPT in the database with filegroups?
Please help me with this task.
Thanks,Restore will give you (essentially) a binary image of what is inside the database. I.e., you cannot
change filegroup layout through backup/restore. You'd have to do that before the backup or after the
restore...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
> I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
> server name and REPT as the name instance. This is Active\Active Cluster with
> Windows 2000 Advanced Server as the OS.
> I have a new server and would like to have filegroups for the database. Can
> I create filegroups on the new server and restore the backup from
> Austin_Sales\REPT in the database with filegroups?
> Please help me with this task.
> Thanks,

Create Database with Filegroups restore Database without Filegroup

I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
server name and REPT as the name instance. This is Active\Active Cluster wit
h
Windows 2000 Advanced Server as the OS.
I have a new server and would like to have filegroups for the database. Can
I create filegroups on the new server and restore the backup from
Austin_Sales\REPT in the database with filegroups?
Please help me with this task.
Thanks,Restore will give you (essentially) a binary image of what is inside the dat
abase. I.e., you cannot
change filegroup layout through backup/restore. You'd have to do that before
the backup or after the
restore...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
> I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
> server name and REPT as the name instance. This is Active\Active Cluster w
ith
> Windows 2000 Advanced Server as the OS.
> I have a new server and would like to have filegroups for the database. C
an
> I create filegroups on the new server and restore the backup from
> Austin_Sales\REPT in the database with filegroups?
> Please help me with this task.
> Thanks,

Create Database with 2 filegroups and...

I create one table on each filegroup, for example:
Database mywind
file1 = mywind_data_1
filegroup1= new_customers
file2 = mywind_data_2
filegroup2 = sales
Then the t1 table is created on new_customers and the t2 table is created on
sales.
If I delete t1 I can restore only this table ? Using one backup from
filegroups?
I can restore only this table ?
ThanksIt depends on what you want to achieve. You can restore only that table to a new database and copy
the data into the original database. See the PATRTIAL option of the RESTORE command. This is an
advanced feature, make sure you read the information in Books Online, understand it and test.
You cannot restore only that table into the original database, not to an earlier point in time
(before the accidental delete). This is because you need to apply all transaction log backups up to
current point in time, so the delete will be applied.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mônica" <Mnica@.discussions.microsoft.com> wrote in message
news:5CF5BEF7-0E67-4AC9-AFC3-2F37B9512A14@.microsoft.com...
>I create one table on each filegroup, for example:
> Database mywind
> file1 = mywind_data_1
> filegroup1= new_customers
> file2 = mywind_data_2
> filegroup2 = sales
> Then the t1 table is created on new_customers and the t2 table is created on
> sales.
> If I delete t1 I can restore only this table ? Using one backup from
> filegroups?
> I can restore only this table ?
> Thanks

Create Database with 2 filegroups and...

I create one table on each filegroup, for example:
Database mywind
file1 = mywind_data_1
filegroup1= new_customers
file2 = mywind_data_2
filegroup2 = sales
Then the t1 table is created on new_customers and the t2 table is created on
sales.
If I delete t1 I can restore only this table ? Using one backup from
filegroups?
I can restore only this table ?
Thanks
It depends on what you want to achieve. You can restore only that table to a new database and copy
the data into the original database. See the PATRTIAL option of the RESTORE command. This is an
advanced feature, make sure you read the information in Books Online, understand it and test.
You cannot restore only that table into the original database, not to an earlier point in time
(before the accidental delete). This is because you need to apply all transaction log backups up to
current point in time, so the delete will be applied.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"M?nica" <Mnica@.discussions.microsoft.com> wrote in message
news:5CF5BEF7-0E67-4AC9-AFC3-2F37B9512A14@.microsoft.com...
>I create one table on each filegroup, for example:
> Database mywind
> file1 = mywind_data_1
> filegroup1= new_customers
> file2 = mywind_data_2
> filegroup2 = sales
> Then the t1 table is created on new_customers and the t2 table is created on
> sales.
> If I delete t1 I can restore only this table ? Using one backup from
> filegroups?
> I can restore only this table ?
> Thanks

Create Database with 2 filegroups and...

I create one table on each filegroup, for example:
Database mywind
file1 = mywind_data_1
filegroup1= new_customers
file2 = mywind_data_2
filegroup2 = sales
Then the t1 table is created on new_customers and the t2 table is created on
sales.
If I delete t1 I can restore only this table ? Using one backup from
filegroups?
I can restore only this table ?
ThanksIt depends on what you want to achieve. You can restore only that table to a
new database and copy
the data into the original database. See the PATRTIAL option of the RESTORE
command. This is an
advanced feature, make sure you read the information in Books Online, unders
tand it and test.
You cannot restore only that table into the original database, not to an ear
lier point in time
(before the accidental delete). This is because you need to apply all transa
ction log backups up to
current point in time, so the delete will be applied.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"M?nica" <Mnica@.discussions.microsoft.com> wrote in message
news:5CF5BEF7-0E67-4AC9-AFC3-2F37B9512A14@.microsoft.com...
>I create one table on each filegroup, for example:
> Database mywind
> file1 = mywind_data_1
> filegroup1= new_customers
> file2 = mywind_data_2
> filegroup2 = sales
> Then the t1 table is created on new_customers and the t2 table is created
on
> sales.
> If I delete t1 I can restore only this table ? Using one backup from
> filegroups?
> I can restore only this table ?
> Thanks