MSSQL Mistake: drive

Showing posts with label drive. Show all posts

Monday, March 26, 2012

Multi data files, same drive/FG perf question

Hello. Say you have a SQL 7 database with 1 MDF and 3 NDF
files... all in the same filegroup and drive (and
folder). Beyond the question of WHY it's like that, other
then that's the way thigns were done in that area back in
the 6.5 device days... question...
Since SQL Server will split the data and indexes across
these 4 files evenly, I'd think things would perform
slower then if these were one file. Wouldn't this be a
type of fragmentation since the data/index is not in a
contigous disk area? THanks, BruceThe MDF is the Primary data file and the NDFs are Secondary files. There are
numerous reasons for this type of design. Performance, Securtiy.
1 example might be that you have some lookup tables that are all static, to
ensure nobody is able to change the data you can create a Read-Only data
file. All the lookup tables are then placed on the Read-Only data file
--
HTH
Ryan Waight, MCDBA, MCSE
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:5e2701c37d19$5668d0b0$a601280a@.phx.gbl...
> Hello. Say you have a SQL 7 database with 1 MDF and 3 NDF
> files... all in the same filegroup and drive (and
> folder). Beyond the question of WHY it's like that, other
> then that's the way thigns were done in that area back in
> the 6.5 device days... question...
> Since SQL Server will split the data and indexes across
> these 4 files evenly, I'd think things would perform
> slower then if these were one file. Wouldn't this be a
> type of fragmentation since the data/index is not in a
> contigous disk area? THanks, Bruce|||If the IO subsystem can handle the extra IO, it is possible for things to
run faster... SQL will automatically fire of separate, parallel IO threads
to handle queries, one for each of the data files... Even if you do not get
any performance improvement now, setting up multiple files in a filegroup
will allow you ( at a later time) install a new raid array, backup and
restore ( or sp+_attach) the database into the new raid array and get IO
parallelism...
So this setup could be the result of an *old way* thinker doing things the
6.5 way, OR a forward thinker planning for the future..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:5e2701c37d19$5668d0b0$a601280a@.phx.gbl...
> Hello. Say you have a SQL 7 database with 1 MDF and 3 NDF
> files... all in the same filegroup and drive (and
> folder). Beyond the question of WHY it's like that, other
> then that's the way thigns were done in that area back in
> the 6.5 device days... question...
> Since SQL Server will split the data and indexes across
> these 4 files evenly, I'd think things would perform
> slower then if these were one file. Wouldn't this be a
> type of fragmentation since the data/index is not in a
> contigous disk area? THanks, Bruce

Friday, March 23, 2012

Mulit MDF Files Seperately Restore On SQL Server 2000

Hi there,
I have a question about backup restore. I have setup a database with 5 MDF
file located in different drive.
testdb_data1.MDF
testdb_data2.MDF (in USB drive)
testdb_data3.MDF
testdb_data4.MDF
testdb_data5.MDF
If the MDF file in USB drive fail (say I unplug it and replace with a new
USB drive), is that possible to restore this MDF file only in order to
recover the whole database? I have tried to backup all the MDF files but
fail to restore in this case.
Could anyone give me some hints on this question? Thanks in advance.
KennethAre you backup up the database files from Windows or using SQL Servers BACKUP command?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Black" <hoodworld@.hotmail.com> wrote in message news:uTvjY90yHHA.484@.TK2MSFTNGP06.phx.gbl...
> Hi there,
> I have a question about backup restore. I have setup a database with 5 MDF
> file located in different drive.
> testdb_data1.MDF
> testdb_data2.MDF (in USB drive)
> testdb_data3.MDF
> testdb_data4.MDF
> testdb_data5.MDF
> If the MDF file in USB drive fail (say I unplug it and replace with a new
> USB drive), is that possible to restore this MDF file only in order to
> recover the whole database? I have tried to backup all the MDF files but
> fail to restore in this case.
> Could anyone give me some hints on this question? Thanks in advance.
> Kenneth
>|||And are they in same file group? On which version of sql?
"Tibor Karaszi" wrote:
> Are you backup up the database files from Windows or using SQL Servers BACKUP command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Black" <hoodworld@.hotmail.com> wrote in message news:uTvjY90yHHA.484@.TK2MSFTNGP06.phx.gbl...
> > Hi there,
> >
> > I have a question about backup restore. I have setup a database with 5 MDF
> > file located in different drive.
> >
> > testdb_data1.MDF
> > testdb_data2.MDF (in USB drive)
> > testdb_data3.MDF
> > testdb_data4.MDF
> > testdb_data5.MDF
> >
> > If the MDF file in USB drive fail (say I unplug it and replace with a new
> > USB drive), is that possible to restore this MDF file only in order to
> > recover the whole database? I have tried to backup all the MDF files but
> > fail to restore in this case.
> >
> > Could anyone give me some hints on this question? Thanks in advance.
> >
> > Kenneth
> >
> >|||In sql2k, you can restore single file with sql backup/restore. But need to do
log restores to keep data integrity.
"Tibor Karaszi" wrote:
> Are you backup up the database files from Windows or using SQL Servers BACKUP command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Black" <hoodworld@.hotmail.com> wrote in message news:uTvjY90yHHA.484@.TK2MSFTNGP06.phx.gbl...
> > Hi there,
> >
> > I have a question about backup restore. I have setup a database with 5 MDF
> > file located in different drive.
> >
> > testdb_data1.MDF
> > testdb_data2.MDF (in USB drive)
> > testdb_data3.MDF
> > testdb_data4.MDF
> > testdb_data5.MDF
> >
> > If the MDF file in USB drive fail (say I unplug it and replace with a new
> > USB drive), is that possible to restore this MDF file only in order to
> > recover the whole database? I have tried to backup all the MDF files but
> > fail to restore in this case.
> >
> > Could anyone give me some hints on this question? Thanks in advance.
> >
> > Kenneth
> >
> >

Mulit MDF Files Seperately Restore On SQL Server 2000

Monday, February 20, 2012

MsSql Server 2000 Backup to tape

i have 3 schedule job for backing up different databases (4 database) to a
tape drive.
first job is to do database backup.
2nd job is to backup different database.
3rd job is to backup Transaction log database.
each job consist of 4 step, which each step is for each database
when schedule sometimes i get the message The file on device '\\.\Tape0' is
not a valid Microsoft Tape Format backup st.[Sqlstate 42000])error 3242)
Backup database is terminating abnormally. [ Sqlstate 42000](error 3013)
. the
step fail
but sometime it can go thru the job.
Please Help. Thanks
EHTanHi
Have you thought of changing to backing up to disc and then to tape?
John
"EngHim" wrote:

> i have 3 schedule job for backing up different databases (4 database) to a
> tape drive.
> first job is to do database backup.
> 2nd job is to backup different database.
> 3rd job is to backup Transaction log database.
> each job consist of 4 step, which each step is for each database
> when schedule sometimes i get the message The file on device '\\.\Tape0' i
s
> not a valid Microsoft Tape Format backup st.[Sqlstate 42000])error 324
2)
> Backup database is terminating abnormally. [ Sqlstate 42000](error 301
3). the
> step fail
> but sometime it can go thru the job.
> Please Help. Thanks
> --
> EHTan

MsSql Server 2000 Backup to tape

i have 3 schedule job for backing up different databases (4 database) to a
tape drive.
first job is to do database backup.
2nd job is to backup different database.
3rd job is to backup Transaction log database.
each job consist of 4 step, which each step is for each database
when schedule sometimes i get the message The file on device '\\.\Tape0' is
not a valid Microsoft Tape Format backup st.[Sqlstate 42000])error 3242)
Backup database is terminating abnormally. [ Sqlstate 42000](error 3013). the
step fail
but sometime it can go thru the job.
Please Help. Thanks
--
EHTanHi
Have you thought of changing to backing up to disc and then to tape?
John
"EngHim" wrote:
> i have 3 schedule job for backing up different databases (4 database) to a
> tape drive.
> first job is to do database backup.
> 2nd job is to backup different database.
> 3rd job is to backup Transaction log database.
> each job consist of 4 step, which each step is for each database
> when schedule sometimes i get the message The file on device '\\.\Tape0' is
> not a valid Microsoft Tape Format backup st.[Sqlstate 42000])error 3242)
> Backup database is terminating abnormally. [ Sqlstate 42000](error 3013). the
> step fail
> but sometime it can go thru the job.
> Please Help. Thanks
> --
> EHTan

Monday, March 26, 2012

Multi data files, same drive/FG perf question

Friday, March 23, 2012

Mulit MDF Files Seperately Restore On SQL Server 2000

Mulit MDF Files Seperately Restore On SQL Server 2000

Mulit MDF Files Seperately Restore On SQL Server 2000

Monday, February 20, 2012

MsSql Server 2000 Backup to tape

MsSql Server 2000 Backup to tape

MSSQL Mistake

Blog Archive

About Me