Post reply

Brand new deployment: 3 part question...

  • March 14, 2011 at 9:45 pm

    #1298654

    Craig Farrell (3/14/2011)Yep, but RAID 0 requires two drives, as does RAID 1.

    Rut-Roh! How did I get this thing to configure as RAID 0 on a single disk then?!? :w00t:

    Also, thanks for the kind words at the beginning of this post, and the company you've included me in. Things like that really make it worth hanging around here. :blush:

    No worries - I take payments via PayPal or cash!

    😉

    J/K

  • March 14, 2011 at 9:55 pm

    #1298657

    Rich Yarger (3/14/2011)

    Craig Farrell (3/14/2011)Yep, but RAID 0 requires two drives, as does RAID 1.

    Rut-Roh! How did I get this thing to configure as RAID 0 on a single disk then?!? :w00t:

    :blink:

    I have no idea... that's just... a drive. Period. What.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 14, 2011 at 10:00 pm

    #1298659

    I know - this is why I am concerned. When I went into Open Manager (Dell's RAID tool) I was able to configure a RAID 0 disk - one disk, on one physical drive. I am not in front of it at the moment. I will have to post the details of the config tomorrow morning.

    Egads! It's not easy being a rookie!

    :w00t:

  • March 14, 2011 at 10:04 pm

    #1298662

    Rich Yarger (3/14/2011)

    I know - this is why I am concerned. When I went into Open Manager (Dell's RAID tool) I was able to configure a RAID 0 disk - one disk, on one physical drive. I am not in front of it at the moment. I will have to post the details of the config tomorrow morning.

    Egads! It's not easy being a rookie!

    :w00t:

    RAID 0... a stripe of RAID... configured... on one drive.

    My guess is it's a self-defense default to allow you to setup single drives in their manager, but still, that's just so not right.

    *mutters to himself while looking up Dell's phone number and wanders off...*

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 14, 2011 at 10:13 pm

    #1298664

    Grrrrrrrrrr! I'm such an....

    Honestly - it's what I still do not know that bothers me the most. Why oh why did I not have this info just 5 days ago? I've already configured some of my system db's and their logs now to settings that might or might not follow Kimberly and Thomas's logic on VLF's.

    Here's another great article I just came across as I was reading Kimberly Tripps writeup.

    http://www.simple-talk.com/sql/database-administration/monitoring-sql-server-virtual-log-file-fragmentation/

    I just hope I can get things in perfect working shape before end of week. I think I am okay, but I am definitely going to have to get this looked at very closely before I finish the server's configuration and complete the documentation.

  • March 14, 2011 at 10:20 pm

    #1298667

    Rich Yarger (3/14/2011)

    Honestly - it's what I still do not know that bothers me the most. Why oh why did I not have this info just 5 days ago? I've already configured some of my system db's and their logs now to settings that might or might not follow Kimberly and Thomas's logic on VLF's.

    Slow down, Rich. Slooow down. You've been making leaps and strides I've rarely seen in most people in an incredibly short time. Don't beat yourself up, there's a ton to learn about all the little bits. Don't keelhaul yourself. 😀

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 14, 2011 at 10:27 pm

    #1298669

    LOL! Thanks for the reminder Craig. Yes - I love SQL so much that I have completely saturated myself as a DBA since last November (worked with it since 2004 but never like this!) It's truly brought my passion back. 🙂

    I'll just have to sleep this concern off until morning. Nothing I can do until then anyway, but I'm more excited now that I read more of Kimberly's blog. It appears I may have been the right judgement on some of what I did with sizing tempdb...I think. Not sure yet.

    More tomorrow, and Craig - you and Gail are the best! Thank you again!

    🙂

  • March 16, 2011 at 2:16 pm

    #1299640

    OK - getting down to the end, and I wanted to run this by you all to get some further input.

    I have a really nice looking SQL Server 2005 64-bit box, thanks to you all! I have my RAID array all nice and fast, just like I like it. I have my Filegroups all nice and divided up (and a much better understanding of things, courtesey of Gail Shaw and Craig Farrell). I was able to make a great plan for very little VLF's, and I even have a backup plan ready to go - BUT I have a couple of questions with regard to it. Here is my backup script, along with what will be used if/when a restore should be needed:

    Backup

    USE master

    GO

    --Backs up system databases

    BACKUP DATABASE [master] TO DISK = N'C:\SQL\Backups\System\master.bak' WITH NOFORMAT, INIT, NAME = N'master-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP DATABASE [model] TO DISK = N'C:\SQL\Backups\System\model.bak' WITH NOFORMAT, INIT, NAME = N'model-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP LOG [model] TO DISK = N'C:\SQL\Backups\System\model.trn' WITH NOFORMAT, INIT, NAME = N'model-Transaction-Log-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP DATABASE [msdb] TO DISK = N'C:\SQL\Backups\System\msdb.bak' WITH NOFORMAT, INIT, NAME = N'msdb-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    --Backs up user databases

    BACKUP DATABASE [Database1] TO DISK = N'C:\SQL\Backups\Database1\Database1.bak' WITH NOFORMAT, INIT, NAME = N'OnDemand-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP LOG [Database1] TO DISK = N'C:\SQL\Backups\Database1\Database1.trn' WITH NOFORMAT, INIT, NAME = N'OnDemand-Transaction-Log-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP DATABASE [Database2] TO DISK = N'C:\SQL\Backups\Database2\Database2.bak' WITH NOFORMAT, INIT, NAME = N'aspnetdb-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP LOG [Database2] TO DISK = N'C:\SQL\Backups\Database2\Database2.trn' WITH NOFORMAT, INIT, NAME = N'aspnetdb-Transaction-Log-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP DATABASE [Database3] TO DISK = N'C:\SQL\Backups\Database3\Database3.bak' WITH NOFORMAT, INIT, NAME = N'NEXUSe2e-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP LOG [Database3] TO DISK = N'C:\SQL\Backups\Database3\Database3.trn' WITH NOFORMAT, INIT, NAME = N'NEXUSe2e-Transaction-Log-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP DATABASE [Database4] TO DISK = N'C:\SQL\Backups\Database4\Database4.bak' WITH NOFORMAT, INIT, NAME = N'Email-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP LOG [Database4] TO DISK = N'C:\SQL\Backups\Database4\Database4.trn' WITH NOFORMAT, INIT, NAME = N'Email-Transaction-Log-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP DATABASE [Database5] TO DISK = N'C:\SQL\Backups\Database5\Database5.bak' WITH NOFORMAT, INIT, NAME = N'JitbitHelpDesk-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP LOG [Database5] TO DISK = N'C:\SQL\Backups\Database5\Database5.trn' WITH NOFORMAT, INIT, NAME = N'JitbitHelpDesk-Transaction-Log-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    Restore

    --DO NOT USE THESE COMMANDS UNLESS YOU ARE NEEDING TO RESTORE THE SYSTEM / USER DATABASES.

    RESTORE DATABASE [master]

    FILE = N'master'

    FROM DISK = N'C:\SQL\Backups\System\master.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    RESTORE DATABASE [model]

    FILE = N'modeldev'

    FROM DISK = N'C:\SQL\Backups\System\model.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    RESTORE DATABASE [msdb]

    FILE = N'MSDBData'

    FROM DISK = N'C:\SQL\Backups\System\msdb.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    RESTORE DATABASE [Database1]

    FILE = N'OnDemand_dat'

    FROM DISK = N'C:\SQL\Backups\Database1\Database1.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    RESTORE DATABASE [Database2]

    FILE = N'aspnetdb'

    FROM DISK = N'C:\SQL\Backups\Database2\Database2.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    RESTORE DATABASE [Database3]

    FILE = N'NEXUSe2e'

    FROM DISK = N'C:\SQL\Backups\Database3\Database3.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    RESTORE DATABASE [Database4]

    FILE = N'Email'

    FROM DISK = N'C:\SQL\Backups\Database4\Database4.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    RESTORE DATABASE [Database5]

    FILE = N'JitbitHelpDesk'

    FROM DISK = N'C:\SQL\Backups\Database5\Database5.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    So - here are my questions, and how I arrived to them...

    I ran the backups you see above in the Backups section. Made both a .bak and .trn file (even though with this routine it appears that the .trn file is not needed). When I did a restore with the code you see from the Restore section, it does what I would expect to see. Went into the properties, and sure enough - all of my Filegroups and settings for those files are in place, but there is one (actually two) problems. 1st - I could not figure out how to bring the database back "online" as when I tried to do a backup on the restored database again - no workie. Said Filegroups could not be backed up at that time because they were not "online". Well - that brought me to problem #2. My files were not actually back in the filesystem (.ndf's at least - stopped there because of all the tears that filled my eyes - couldn't see any longer and thus, didn't notice if the .mdf and .ldf had also not been restored). My guess is that they had to have been, otherwise - dunno how it could have showed back up after Restore into SSMS.

    So with the above stategies - can anyone tell me what I didn't successfully get my .ndf files back into their respective spots in the filesystem, and why (though they showed up) they were not listed in the properties of the files for this database's Filegroups?

    If I may provide you with any further details, I would be more than happy to. Many thanks to you all in advance!

    🙂

  • March 16, 2011 at 2:39 pm

    #1299658

    Rich Yarger (3/16/2011)

    I was able to make a great plan for very little VLF's,

    You don't necessarily want very few VLFs, but that's another discussion...

    RESTORE DATABASE [Database1]

    FILE = N'OnDemand_dat'

    FROM DISK = N'C:\SQL\Backups\Database1\Database1.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    Note you didn't declare MOVEs here, so you're going to overwrite the old files.

    1st - I could not figure out how to bring the database back "online" as when I tried to do a backup on the restored database again - no workie. Said Filegroups could not be backed up at that time because they were not "online".

    Timeout, your secondary filegroups where not online when you made the backup? Do you have the actual errors from that time?

    Well - that brought me to problem #2. My files were not actually back in the filesystem (.ndf's at least - stopped there because of all the tears that filled my eyes - couldn't see any longer and thus, didn't notice if the .mdf and .ldf had also not been restored). My guess is that they had to have been, otherwise - dunno how it could have showed back up after Restore into SSMS.

    If it didn't backup the ndfs, you won't restore the ndfs. The filegroup information is merely in the database header which hides in the primary filegroup.

    We need to deal with the original database and backup before you worry about the restores, if I understood you correctly.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 16, 2011 at 2:50 pm

    #1299669

    Hi Craig!

    Sorry for any confusion on these multiple points. Yes - I went with a design of between 64mb - 1gb for the .ldf's across the board. that will give them a set of 4 VLF's per increase, and with the manner in which this database is going to grow - seemed to be the best way to go about it. I also set the other 4 User db's up this way, as their growth will be even less.

    With regard to the backup I was describing - the data and all Filegroups were on-line when I backed up the database (I will need to do another to give you the error I got on restore and have enough of a window to do this with - if necessary), but it looked like the .bak had both the .mdf and .ndf's in there because of the messages I saw when it was processing (said something about so many pages being backed up for FG1, FG2, etc...). But when I did the restore of it - no workie. Not just that it didn't come online after the restore, but the .ndf's weren't back in the file system (which would explain why it didn't come back online). I could send you a copy of the .bak if you wish. Just let me know!

    Thanks Craig, and I'll do another back of it after my web guy gets done putting all the web pieces in place.

    P.S. Yes - I want the files overwritten each night when the job runs, so when they do their backup to their nearline media device for on-site/off-site storage - it will be the newest.

  • March 16, 2011 at 3:03 pm

    #1299674

    Rich Yarger (3/16/2011)

    P.S. Yes - I want the files overwritten each night when the job runs, so when they do their backup to their nearline media device for on-site/off-site storage - it will be the newest.

    What I meant was that you're restoring over the existing databases Database1, Database2, etc if you restore on the same server you backed up from.

    Definately get back to us with whatever errors you had during the backup.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 16, 2011 at 3:29 pm

    #1299687

    Ah - I understand now. In the test I did for the restore - I had deleted my main User database, and simply ran the restore it that particular one. It ran fine - showed back up in SSMS - all the other details as I have previously described were as they were - but again, no .ndf's in their respective Filegroup directories.

    I just finished up getting some of this to run on my local box. I am going to run it again on my PROD box (which is the only place I can do this test because - we don't have another test server to properly simulate the environment, and the PROD box is still not released yet - so no worries, all is safe).

    I'll be back in a bit with the result...

  • March 17, 2011 at 10:45 am

    #1300069

    OK - I figured out the issue I was having in restoring my Filegroups. I have to take the db off-line to RESTORE the filegroups - hence why they are "off-line". I get it now.

    However - I do have a new error that has cropped up since then. I have 1 Filegroup inparticular which is having difficulties. It's called 'NC_INDEX'. I tried to manually assigned a Non-Clusterd index in a Testing db that I am working with, and it came back with the following error...

    The reason I show you this first, is due to the error I am getting when attempting to backup this Filegroup. I get the following error when attempting to get it backed up...

    Msg 3034, Level 16, State 1, Line 1

    No files were selected to be processed. You may have selected one or more filegroups that have no members.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.

    Now - I have checked, and I do infact have a file (.ndf) associated both in the file system, and viewable from the db properties in SSMS for this Filegroup, but for some reason, it's having some difficulties.

    Any ideas? Many thanks!

    🙂

  • March 17, 2011 at 10:52 am

    #1300075

    The moment I posted the above, I found the problem. I had mistakenly left this Filegroup in the PRIMARY Filegroup as opposed to the one that was created for it. It became obvious when I went into the Filegroups section of the Properties, and infact noticed that there were no files associated to the Filegroup.

    Sorry about that. Now - on to setting up 800 series alerts!

    🙂

  • March 17, 2011 at 1:59 pm

    #1300200

    New issue. Database restores and appears to be "Online". I can open it, expand objects, query from it, etc...But - when I then try to run the very same backup that I did to create the initial set of .FLG files with, it errors out on the Filegroups other than PRIMARY, saying they are "Offline".

    Here is the error I receive when attempting to backup the newly restored db...

    Msg 3007, Level 16, State 2, Line 1

    The backup of the file or filegroup "FG1" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.

    Not sure what is causing this. I've tried the following to remedy the problem, with no success:

    - Stopped and restated the SQL service.

    - Tried taking the db offline, and put it back online.

    Anyone had this happen before, or have any thoughts?

    Many thanks in advance!

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply