May 13, 2015 at 7:00 am
I apologize for this if it is in the wrong forum, but I have no where else to go. Please send me in the right direction (nicely please).
I am new to SQL and SQL databases. I am an iSeries professional for 30+ years.
Can someone please tell me the difference between the database and the data?
I backup the database thru the Management Studio and a get a 1.2 GB backup file. But, I have 5.7 TB of data in the Storage Array. I know its a lot, but.... It is in-car video required to stored forever. It just keeps growing.
So am I supposed to backup the database, the logs, and the data? Nothing the in Best practices ever refers to the actual data. They just make it seem like its part of the database. It can't be.
So what is the real best practice?
May 13, 2015 at 7:09 am
A database is the definition of the structures and other objects that make up a database (tables, views, procedures, users, roles, etc.). The data is stored within the structures of the database. For instance, a database has a table and that table has a number of rows of data. So the whole thing is the database. Now, there are exceptions to this. If you have a MAX column of varbinary or varchar, only the first 8k of data is stored within the database. The rest is stored to a disk BLOB storage. Another exception would be if you set up FILESTREAM storage. The files are stored outside the database. Now, the FILESTREAM wouldn't show up in a backup, but the BLOB storage would.
Does that help?
On a side note, if you're actually managing this database and you're unsure of the operations, one problem you might be seeing is having the database in FULL recovery mode, but not running log backups. In this case, your log file will just grow and grow until it fills the hard drive. You should either set up log backups or change recovery to simple. But that decision involves working with the business to determine if you need to do point in time recovery of the database.
And yeah, this is a very deep and wide set of functionality you're diving into.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2015 at 7:35 am
Are the videos individual files on the array?
May 13, 2015 at 7:38 am
One point. i said that BLOB storage was outside the database. That's not right. Not sure where my head was. Gail spotted it and corrected me.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2015 at 7:48 am
yes, they are individual files in the storage array.
May 13, 2015 at 9:57 am
Also, I need a new head, FILESTREAM data is stored apart from the database, but is backed up and restored through the backup process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2015 at 10:42 am
It's quite common to organise things so that the database refers to files that are not in the database.
For example a database might contain the information that a particular video is licensed for use in country X from date 1 to date 2 and in country Y from date 3 to date 4 and so on, that the video has English, French and Russian subtitle options in the Spanish soundtrack version and French, Russian, German, Dutch, Spanish, German and Arabic subtitle options with the English soundtrack version, the duration of each version, what file(s) the video is held in, what subscription packages it forms part of, what its price is for viewing outside of a subscription, plus data about the plot, cast, and country-specific classification (suitable for children etc.). The videos themselves are held in files that are not in the database.
Tom
May 13, 2015 at 11:02 am
Assuming they're not FILESTREAM then, they're really not part of the database at all.
Perhaps they should be backed up to tape directly via the array. More in line with how you'd backup e.g. user files.
May 13, 2015 at 11:47 am
Grant Fritchey (5/13/2015)
One point. i said that BLOB storage was outside the database. That's not right. Not sure where my head was. Gail spotted it and corrected me.
Maybe in the clouds?
Thinking Azure blob storage on that one?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 14, 2015 at 12:52 am
SQLRNNR (5/13/2015)
Grant Fritchey (5/13/2015)
One point. i said that BLOB storage was outside the database. That's not right. Not sure where my head was. Gail spotted it and corrected me.Maybe in the clouds?
Thinking Azure blob storage on that one?
Yeah, let's go with that. At least it's a semi-rational explanation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2015 at 12:41 pm
Grant Fritchey (5/14/2015)
SQLRNNR (5/13/2015)
Grant Fritchey (5/13/2015)
One point. i said that BLOB storage was outside the database. That's not right. Not sure where my head was. Gail spotted it and corrected me.Maybe in the clouds?
Thinking Azure blob storage on that one?
Yeah, let's go with that. At least it's a semi-rational explanation.
Better than the "I completely lost my mind" option 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply