| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-02 : 18:54:27
|
| Yes, we use it on all of our servers. The only con is security. Someone sophisticated enough can use DBCC PAGE to read previous data that used to occupy that space on disk. First of all, there are very few people in the world who are sophisticated enough to do this. Second of all, aren't your servers secure?FYI: If your service account has local admin, then it already has this privilege anyway. It's not a security best practice (local admin for service account), but it's still widely common.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-02 : 23:19:25
|
quote:
Originally posted by ferrethouse
I've been reading about this and the author of this article...http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspxSuggests that enabling it is pretty common practice. I'm leery of changing anything away from Microsoft's defaults without due diligence. Does anyone have any experience with this setting and can comment on the pros and cons?
It does have performance benefits in terms of autogrow.But in my enviroment we know our data and we know how much our data is going to grow.So we manually expand our files during off time so we have less concerns on the performance issues due to file expansion.As the author rightly pointed out you should weigh in the performance benefits against the security risk and decides what suits your enviroment the best.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-03 : 00:37:00
|
| I dont work on a 24/7 system .So for me it's better of that I keep my hands away from this stuff than taking chances on security for my system.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-03 : 05:14:48
|
quote:
Originally posted by tkizer
It has performance benefits in terms of autogrow, manual expansions, and creating new databases.
Also, iirc, backups and restores. Restores certainly, not 100% sure about backups.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-03 : 05:43:21
|
| The security risks from this are minimal. For this to be considered a security risk you need:Database sysadmins who are not windows admins and have no rights to the file system (including the drives that the database files are on)Confidential files (that the database admins must not under any circumstances be able to see), saved on the same drives that the database files are on.Said confidential files deleted in a non-secure way (so not overwritten with zeros as typically would be done with highly confidential files)The database files then expanded over the portion of the disk that used to contain the confidential filesThat's a pretty rare combo of requirements. I suspect most companies would keep confidential files separate or securely delete them (or both). It's not a default because there could be companies with that exact setup, and for them having it on by default would be a problem. For the rest of us though, just turn it on, there are no other downsides.--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-03 : 07:42:59
|
quote:
Originally posted by GilaMonster
....Restores certainly, not 100% sure about backups.--Gail ShawSQL Server MVP
I think it will be helpful only in case if we are initializing a new file when doing the restores..After Monday and Tuesday even the calendar says W T F .... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-03 : 07:44:17
|
| Initialising or expanding. So restoring a new database or restoring a DB over an existing one where the DB you're restoring is larger.--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-03 : 07:56:50
|
| Ok so it only happen if my backup is larger than the existing database.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-03 : 08:46:05
|
| No, if the source database of the backup is larger than the existing database.--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-03 : 09:52:49
|
| So if my source database is larger but the data file of the source database is exactly the same size of the target database but log file is much larger so I believe file initialization wont be of any help.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-03 : 10:19:00
|
| I wasn't talking about log files. The backup can be smaller while the data files of the source database are larger. Remember, backups contain only the used portions of the database.--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-03 : 10:34:05
|
| Lets keep backup size out of the picture for the time being.In the link posted above it is mentioned that file initialization wont work on log files.So I was thinking of a scenario where the data file is of the same size but the log file is much bigger as compared to the destination.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-03 : 10:47:52
|
| In that case instant init won't help with the restore.--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-03 : 11:01:40
|
| Ok.Thanks for the clarification.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
|
Instant File Initialization