January 10, 2020 at 3:37 pm
That was precisely my thought. A fellow DBA in another company recently went through a bout with a ransom-ware. The ransom-ware was able to track down where the normal backups were and nuked them. The only thing that saved him was that he shipped his backups to a remote site in a separate process not on any of the database servers or the backup system. Not having any reference on the attacked machine to the backup location(s) is a huge layer of prevention. Of course, that would also mean that I'd have to nuke the backup history and wouldn't do a thing in that area if the attack occurred during backups... and we do a shedload of log file backups not to mention that the FULL backups take hours to complete. If the attacker has access to MSDB, they could still track things so an interim "hop" for where backups are stored will probably be in order. That may be a whole lot easier to do than doing "pulls" that involve actually issuing a backup command to the remote system.
Air gaps are wonderful things
January 10, 2020 at 3:38 pm
MVDBA (Mike Vessey) wrote:jasona.work wrote:Steve Jones - SSC Editor wrote:Good luck with that. Not sure third party tools make this better, but you're welcome to eval SQL Backup from Redgate if it might help
Yeah, I've trialed it a couple times here on my homelab (MAN I wish I could have JUST ONE test server that wasn't locked down 6 ways to Sunday at work!) and I like the interface and such.
Plus, I know it'd be easier if I had to give someone the basics to cover for me!
Maybe, someday, I'll get a REAL backup person to help out...
I heard Jeff was looking for work 🙂
BWAAAA-HAAAA!!!! Just to be sure... NOT! Like I tell the recruiters that email me, I'm currently working my dream job. I'm doing interesting work with good people for good money and a commute so short that I could ride a unicycle to work without getting chaff marks. Besides, I'm so busy that if I get any busier, I'd need to be twins. 😀
But imagine all the fun toys you could look at here!
Or find out what happens to your toes when a 70-ton vehicle drives over them, that could be interesting too. Once...
January 10, 2020 at 3:40 pm
Eirikur Eiriksson wrote:Jeff Moden wrote:Jeff Moden wrote:x wrote:Phil Parkin wrote:Do you have any favorite links on the subject of pulling rather than pushing?
Hmmmm ...
Nah thought I had one but no luck retrieving it.
I get better luck when just dropping sql server from the search, ie., googling with the phrase: backup pull vs push.
The bottom line for me is that if your server that needs backup has privileges to "push" the backup to the remote backup server, then in the event the server needing backup gets owned, the writeable remote backup location is also at risk, whereas if the backup server "pulls" from a read only share on the backup client (like the database server containing the .bak you want saved), its less succeptible to security breeches on the database server, and since it can pull from a read only share, the client (database) server is also protected somewhat from the backup server getting taken down.
That was precisely my thought. A fellow DBA in another company recently went through a bout with a ransom-ware. The ransom-ware was able to track down where the normal backups were and nuked them. The only thing that saved him was that he shipped his backups to a remote site in a separate process not on any of the database servers or the backup system. Not having any reference on the attacked machine to the backup location(s) is a huge layer of prevention. Of course, that would also mean that I'd have to nuke the backup history and wouldn't do a thing in that area if the attack occurred during backups... and we do a shedload of log file backups not to mention that the FULL backups take hours to complete. If the attacker has access to MSDB, they could still track things so an interim "hop" for where backups are stored will probably be in order. That may be a whole lot easier to do than doing "pulls" that involve actually issuing a backup command to the remote system.
There's also the subject of people that have made the mistake of the service login having Domain-Admin privs, especially on legacy systems that they may have not been the ones to do the installation.
The worst one I've seen was the global image server having God privileges in order to push an system/disk image onto any system on the network. Goes without saying, that server got compromised by a ransomware and funny enough, the only option was a global rebuild of all the estate (thousands of systems). Me thinks that is a strong argument for doing "pulls" 😉
😎
I have to deal with Amazon RDS (for one of our most famous clients - every single user on this site will have been in one of their stores) - let me be clear - I hate RDS and it is underpowered and under featured. - no sql mail, almost impossible to create linked servers etc etc… everything you try to do RDS won't let you
I've had to create linked servers from our on-premise sql boxes to the RDS instance and pull from a logging table about timings of certain procedures and then have our on premise server email our support team before the customer starts ringing us... I'm a 90% pull man
I'm not the greatest fan of RDS either, think of it as less than the equivalent of SQL Server Express in terms of usability.
😎
In few recent migration projects I ended up using EC2 instances as RDS just did not cut it.
January 10, 2020 at 3:43 pm
MVDBA (Mike Vessey) wrote:jasona.work wrote:Steve Jones - SSC Editor wrote:Good luck with that. Not sure third party tools make this better, but you're welcome to eval SQL Backup from Redgate if it might help
Yeah, I've trialed it a couple times here on my homelab (MAN I wish I could have JUST ONE test server that wasn't locked down 6 ways to Sunday at work!) and I like the interface and such.
Plus, I know it'd be easier if I had to give someone the basics to cover for me!
Maybe, someday, I'll get a REAL backup person to help out...
I heard Jeff was looking for work 🙂
BWAAAA-HAAAA!!!! Just to be sure... NOT! Like I tell the recruiters that email me, I'm currently working my dream job. I'm doing interesting work with good people for good money and a commute so short that I could ride a unicycle to work without getting chaff marks. Besides, I'm so busy that if I get any busier, I'd need to be twins. 😀
Having a twin does help 😛
😎
January 10, 2020 at 3:52 pm
Damn! I've never had a collapsed lung but know other people that have. It's definitely NOT a cake walk. Glad it turned out ok in the end. Were they able to do a full restoration of the collapsed lung?
they could have pickled it and put it in a jar for all i care - it was 4 weeks of sitting in the corner of the shower (because i couldn't stand up) and a nurse hosing me down.
christmas day i had a turkey sandwich for lunch and slightly delirious dreams that my company was switching to oracle
MVDBA
January 10, 2020 at 4:02 pm
x wrote:Phil Parkin wrote:Do you have any favorite links on the subject of pulling rather than pushing?
Hmmmm ...
Nah thought I had one but no luck retrieving it.
I get better luck when just dropping sql server from the search, ie., googling with the phrase: backup pull vs push.
The bottom line for me is that if your server that needs backup has privileges to "push" the backup to the remote backup server, then in the event the server needing backup gets owned, the writeable remote backup location is also at risk, whereas if the backup server "pulls" from a read only share on the backup client (like the database server containing the .bak you want saved), its less succeptible to security breeches on the database server, and since it can pull from a read only share, the client (database) server is also protected somewhat from the backup server getting taken down.
We can "pull" the backup from a read only share, yes. Why would you not be able to do that?
I was just curious on how it worked, how the backup software knows when its time to do the copy from the source server etc
January 10, 2020 at 4:04 pm
Jeff Moden wrote:Ooooo... interesting and very timely subject for me, Patrick. Do you have any favorite links on the subject of pulling rather than pushing?
I'll have to look. It's more a a Windows issue. If you log into a host (RDP) and start copying, the SMB copy starts to take up memory on the remote host, so there is some contention for SQL Server. Better to log into the destination host and "pull" the copy from the SQL instance host, which doesn't use the same memory. Not sure why. I know Brent Ozar presented on it and showed the memory dip, and someone at MS talked about this at one of the MVP Summits.
I'll ping Brent.
I would be interested in all that just getting set up and scheduled. Besides not having xp_cmdshell any more, I also don't rdp to servers either.
January 10, 2020 at 4:07 pm
Jeff Moden wrote:Damn! I've never had a collapsed lung but know other people that have. It's definitely NOT a cake walk. Glad it turned out ok in the end. Were they able to do a full restoration of the collapsed lung?
they could have pickled it and put it in a jar for all i care - it was 4 weeks of sitting in the corner of the shower (because i couldn't stand up) and a nurse hosing me down.
christmas day i had a turkey sandwich for lunch and slightly delirious dreams that my company was switching to oracle
"delirious dreams that my company was switching to oracle" ... so when did the nightmares stop?
😎
January 10, 2020 at 4:49 pm
Grrr
All set to use the dbatools.io Powershell to generate scripts to migrate my SQL logins, only to find out that because of the bloody way our systems get locked down, it can't be imported (import-module, after DL'ing the Zip file)
Now I have to plow through the list sp_help_revlogin generates to sort out the "not needed / disabled" stuff
January 10, 2020 at 5:03 pm
OK, a couple things.
In here: https://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/, #3
Also, more here on file cache bloat - https://www.red-gate.com/simple-talk/sql/database-administration/six-scary-sql-surprises/
January 12, 2020 at 5:17 am
OK, a couple things.
In here: https://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/, #3
Also, more here on file cache bloat - https://www.red-gate.com/simple-talk/sql/database-administration/six-scary-sql-surprises/
It's funny, the most obvious option of not using lousy Windows interface on servers is not included into final recommendations.
And the index fill-factor part is completely f-ked up. Including final recommendations.
_____________
Code for TallyGenerator
January 12, 2020 at 3:12 pm
Jeff Moden wrote:That was precisely my thought. A fellow DBA in another company recently went through a bout with a ransom-ware. The ransom-ware was able to track down where the normal backups were and nuked them. The only thing that saved him was that he shipped his backups to a remote site in a separate process not on any of the database servers or the backup system. Not having any reference on the attacked machine to the backup location(s) is a huge layer of prevention. Of course, that would also mean that I'd have to nuke the backup history and wouldn't do a thing in that area if the attack occurred during backups... and we do a shedload of log file backups not to mention that the FULL backups take hours to complete. If the attacker has access to MSDB, they could still track things so an interim "hop" for where backups are stored will probably be in order. That may be a whole lot easier to do than doing "pulls" that involve actually issuing a backup command to the remote system.
Air gaps are wonderful things
Perfect name for this type of thing. I believe you've coined a new use of the term "Air Gap". Thanks, Steve.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2020 at 7:42 pm
Jeff Moden wrote:MVDBA (Mike Vessey) wrote:jasona.work wrote:Steve Jones - SSC Editor wrote:Good luck with that. Not sure third party tools make this better, but you're welcome to eval SQL Backup from Redgate if it might help
Yeah, I've trialed it a couple times here on my homelab (MAN I wish I could have JUST ONE test server that wasn't locked down 6 ways to Sunday at work!) and I like the interface and such.
Plus, I know it'd be easier if I had to give someone the basics to cover for me!
Maybe, someday, I'll get a REAL backup person to help out...
I heard Jeff was looking for work 🙂
BWAAAA-HAAAA!!!! Just to be sure... NOT! Like I tell the recruiters that email me, I'm currently working my dream job. I'm doing interesting work with good people for good money and a commute so short that I could ride a unicycle to work without getting chaff marks. Besides, I'm so busy that if I get any busier, I'd need to be twins. 😀
But imagine all the fun toys you could look at here!
Or find out what happens to your toes when a 70-ton vehicle drives over them, that could be interesting too. Once...
Dude! 70 ton "toys"? What company do you work for? Caterpillar or a cement/gravel company?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2020 at 1:30 pm
jasona.work wrote:Jeff Moden wrote:MVDBA (Mike Vessey) wrote:jasona.work wrote:Steve Jones - SSC Editor wrote:Good luck with that. Not sure third party tools make this better, but you're welcome to eval SQL Backup from Redgate if it might help
Yeah, I've trialed it a couple times here on my homelab (MAN I wish I could have JUST ONE test server that wasn't locked down 6 ways to Sunday at work!) and I like the interface and such.
Plus, I know it'd be easier if I had to give someone the basics to cover for me!
Maybe, someday, I'll get a REAL backup person to help out...
I heard Jeff was looking for work 🙂
BWAAAA-HAAAA!!!! Just to be sure... NOT! Like I tell the recruiters that email me, I'm currently working my dream job. I'm doing interesting work with good people for good money and a commute so short that I could ride a unicycle to work without getting chaff marks. Besides, I'm so busy that if I get any busier, I'd need to be twins. 😀
But imagine all the fun toys you could look at here!
Or find out what happens to your toes when a 70-ton vehicle drives over them, that could be interesting too. Once...
Dude! 70 ton "toys"? What company do you work for? Caterpillar or a cement/gravel company?
Well, we do have one of these: https://en.wikipedia.org/wiki/M1_Abrams parked out front of the building I'm in, so that might be a hint for you...
🙂
January 13, 2020 at 3:27 pm
Jeff Moden wrote:jasona.work wrote:Jeff Moden wrote:MVDBA (Mike Vessey) wrote:jasona.work wrote:Steve Jones - SSC Editor wrote:Good luck with that. Not sure third party tools make this better, but you're welcome to eval SQL Backup from Redgate if it might help
Yeah, I've trialed it a couple times here on my homelab (MAN I wish I could have JUST ONE test server that wasn't locked down 6 ways to Sunday at work!) and I like the interface and such.
Plus, I know it'd be easier if I had to give someone the basics to cover for me!
Maybe, someday, I'll get a REAL backup person to help out...
I heard Jeff was looking for work 🙂
BWAAAA-HAAAA!!!! Just to be sure... NOT! Like I tell the recruiters that email me, I'm currently working my dream job. I'm doing interesting work with good people for good money and a commute so short that I could ride a unicycle to work without getting chaff marks. Besides, I'm so busy that if I get any busier, I'd need to be twins. 😀
But imagine all the fun toys you could look at here!
Or find out what happens to your toes when a 70-ton vehicle drives over them, that could be interesting too. Once...
Dude! 70 ton "toys"? What company do you work for? Caterpillar or a cement/gravel company?
Well, we do have one of these: https://en.wikipedia.org/wiki/M1_Abrams parked out front of the building I'm in, so that might be a hint for you...
🙂
BWAAAA-HAAAA!!!! I know the place! They won't let employees take them out for a test drive, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 64,351 through 64,365 (of 66,549 total)
You must be logged in to reply to this topic. Login to reply