Pau Lo
asked on
Data Locations / Backup/Archive operations
Can anyone with experience of backing up databases let me know what I am after in the below which is about where data for a database driven application “could reside” dependant on setup and business operations/policy.
I basically want to identify every where data for a specific application “goes”. It is sensitive data so I really want control or at least some insight into everywhere this data essentially goes during backup/retention/archive/t
Also, what kind of documentation would typically include this data “path/flow”? I.e. everywhere the data held within the database that drives this application does/could the data end up. Is this called a certain type of document by any chance that I could ask for? What infrastructure/architectur
In my non expert backup mind, the data lives on the hard disc on the database server, i.e. the same server where the MSSQL service is running. But I assume for larger applications the data may well be spread across numerous servers/storage devices.
Any pointers welcome. As simple terminology as possible be greatly appreciated to show a non backup/DBA person the flow/process of where data from a database driven app goes/resides.
I basically want to identify every where data for a specific application “goes”. It is sensitive data so I really want control or at least some insight into everywhere this data essentially goes during backup/retention/archive/t
Also, what kind of documentation would typically include this data “path/flow”? I.e. everywhere the data held within the database that drives this application does/could the data end up. Is this called a certain type of document by any chance that I could ask for? What infrastructure/architectur
In my non expert backup mind, the data lives on the hard disc on the database server, i.e. the same server where the MSSQL service is running. But I assume for larger applications the data may well be spread across numerous servers/storage devices.
Any pointers welcome. As simple terminology as possible be greatly appreciated to show a non backup/DBA person the flow/process of where data from a database driven app goes/resides.
ASKER
Thanks for the reply
Is this workflow called anything in particular? I.e. the x workflow?
Also backups on tapes, are these typically encrypted? What kind of device would be required if someone got physical access to the tapes to restore them? And what sort of cost is it for such a restore device?
So in summary typically data goes from:
db server disc > backup server disc > tape
Is this workflow called anything in particular? I.e. the x workflow?
Also backups on tapes, are these typically encrypted? What kind of device would be required if someone got physical access to the tapes to restore them? And what sort of cost is it for such a restore device?
So in summary typically data goes from:
db server disc > backup server disc > tape
ASKER
And for a retention policy.
Is it typical for huge databases that data will roll onto different disks, or will it just be backed up to tape and restored if ever required?
I wasnt sure how larger databases work as i assume the disks on a database/data server can fill up quite quickly?
Is it typical for huge databases that data will roll onto different disks, or will it just be backed up to tape and restored if ever required?
I wasnt sure how larger databases work as i assume the disks on a database/data server can fill up quite quickly?
Encryption is starting to come around, but is not really mainstream yet unless you have newer tapes drives. Most likely if you haven't asked for encryption it's not encrypted.
Again, it depends on the backup software writing the tape on how easy it is to read the data. Some tapes can easily be imported, some are harder.
The log files on database servers get backed up frequently. These allow the disks to not fill up easily.
The data doesn't really sit on the backup server, it's moved from the client machine to the tape directly. Some products use disk for staging, as again there's many options.
Again, it depends on the backup software writing the tape on how easy it is to read the data. Some tapes can easily be imported, some are harder.
The log files on database servers get backed up frequently. These allow the disks to not fill up easily.
The data doesn't really sit on the backup server, it's moved from the client machine to the tape directly. Some products use disk for staging, as again there's many options.
ASKER
Thanks for all the advice.
A couple of final things. Why do folk use a SAN to store the applications database, as opposed to using the local disc on the database server? Whats the pros'cons of sticking with the local disc of the db server and not using SAN to store the apps DB?
And.... how does a SAN "appear"? For example ( I am not a network admin) to see other "servers" and their various discs/shares in our domain we just use explorer, share enum and port scans, and its relatively easy to see which are DB servers running MS-SQL, email servers running Exchange, File Servers, Domain Controllers etc). Would a SAN device just look like a domain member server running server 2008 or something, or would it be in a totally differernt domain and be spread across numerous servers as opposed to one? The concept of a domain full of servers and the various local or viritual discs on the servers/workstations and a SAN I appreciate may be totally different?
A couple of final things. Why do folk use a SAN to store the applications database, as opposed to using the local disc on the database server? Whats the pros'cons of sticking with the local disc of the db server and not using SAN to store the apps DB?
And.... how does a SAN "appear"? For example ( I am not a network admin) to see other "servers" and their various discs/shares in our domain we just use explorer, share enum and port scans, and its relatively easy to see which are DB servers running MS-SQL, email servers running Exchange, File Servers, Domain Controllers etc). Would a SAN device just look like a domain member server running server 2008 or something, or would it be in a totally differernt domain and be spread across numerous servers as opposed to one? The concept of a domain full of servers and the various local or viritual discs on the servers/workstations and a SAN I appreciate may be totally different?
Performance, reliability, scalability, and cost savings.
SAN itself is not seen on the network. It's attached via fiber channel and uses SCSI commands. There's another centralized storage option called NAS, which does use the network. It's shared much like a share on a server, such as \\server\share. SANs are not something you scan for. Your SAN admin creates maps from the storage array to the server needing the storage. He then presents the storage to the server, which sees it just like it would internal disk. The server doesn't know it's not internal, nor does it care.
SAN itself is not seen on the network. It's attached via fiber channel and uses SCSI commands. There's another centralized storage option called NAS, which does use the network. It's shared much like a share on a server, such as \\server\share. SANs are not something you scan for. Your SAN admin creates maps from the storage array to the server needing the storage. He then presents the storage to the server, which sees it just like it would internal disk. The server doesn't know it's not internal, nor does it care.
ASKER
How so with cost savings? And I guess the other 3 Performance, reliability, scalability...
And say an admin had to pick a file off the SAN, how do they essentially "logon" to the SAN, and what prevents a malicious insider also logging on to the SAN as well, i.e. what type of remote access tools / authentication is used to obtain access?
And say an admin had to pick a file off the SAN, how do they essentially "logon" to the SAN, and what prevents a malicious insider also logging on to the SAN as well, i.e. what type of remote access tools / authentication is used to obtain access?
ASKER
>>Your SAN admin creates maps from the storage array to the server needing the storage
Are you saying in say \\databaseserver\ needed to write files to SAN drive X, in explorer or my computer on \\databaseserver\ you'd see a mapped drive X as if it was a local drive? I.e. if you got access to \\databaseserver you could potentially also gain access to SAN drive X through the mapped drive? Can a normal user given the right credentials / know how map a SAN drive to their machine as if it was a local/network drive to save files to/from the SAN?
Are you saying in say \\databaseserver\ needed to write files to SAN drive X, in explorer or my computer on \\databaseserver\ you'd see a mapped drive X as if it was a local drive? I.e. if you got access to \\databaseserver you could potentially also gain access to SAN drive X through the mapped drive? Can a normal user given the right credentials / know how map a SAN drive to their machine as if it was a local/network drive to save files to/from the SAN?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Each environment is unique. Without knowing the size/layout/etc of your environment, I will assume a normal decent sized IT shop.
You're correct, in that the data will reside on the disk on the database server. This disk can be local disk, meaning internal to the box, or SAN disk, which appears local but actually sits on a storage array that is centralized.
When using typical backup softwares, the database files, logs, or whatever backup methodology is being used is moved via Ethernet through the backup server to tape. The data is not typically kept on the backup server itself. There is meta data, which is just a record of the data, on the backup server. The file with the real data on it will sit on a tape.
Now, there are 100's of caveats to this. The data can sit on the backup server, the data can go to multiple places (Virtual Tapes + replication), etc etc. There are so many scenarios you can mention. But, in general, you'll have your copy on your database server, and copies on tapes.
If you want a report of this, you'll most likely have to get with your backup administrator at which point they would provide you with a report or catalog dump of what files are on which tapes.
Please let me know if you have any specific questions, or if you can provide any details on your backup environment.