Hi.
A have a database on MS-SQL Server 2008 that is growing a lot, year by year.
What I want to do is: take some data and store in a XML file, and store this file in a table, at the same database.
Is this a good idea? Do you have any idea?
Thanks.
+2
A:
The standard approach is to setup a new database (probably on a different machine) which will be your archive database and run a stored procedure on schedule to move your records to the archive database (only records older that 2 years for example or what is suitable for your business problem). You will have to copy your table's structure and keep both dbs synchronized. You can include in your sp only the biggest tables, usually only few of them are causing problems.
This way you can still access you archived data in proper manner and run queries against it. Storing the data in xml in your database doesn't look like a good idea to me, since you will have hard times if you need your old data. Also it will give you a huge overhead on the disk space.
anthares
2010-03-10 13:03:00
+1, key points: `there is a better way, using a different DB`, `storing in XML makes getting your data hard` and `XML will use more disk space`
KM
2010-03-10 13:51:20
What if I zip XML file before store in database? Is that more efficient?
rpf
2010-03-10 14:44:08
It will be better for the data storage, but it will be nightmare if you need the data back. I don't see any reason to do this.
anthares
2010-03-10 14:47:37
I thought it was just me who thought that.My DBA and software architect think that keeping XML files with history is the better approach.... :/
rpf
2010-03-10 14:55:38
I personally wouldn't go this way! It's interesting what are the DBA and SA's arguments for doing this. Probably saving money for the SQL license and hardware - but this solution would be pretty ugly and the history info will be really not accessible.
anthares
2010-03-10 15:09:23
A:
XML is by its very nature verbose and a far less efficient way of storing data than natively within a table column/row structure.
For example, imagine just storing a simple table of users...
<Users>
<User>
<UserID>1</UserID>
<Name>Joe Smith</Name>
<DOB>24.02.1973</DOB>
</User>
<User>
<UserID>2</UserID>
<Name>Joleen Smith</Name>
<DOB>13.10.1962</DOB>
</User>
...
...
You would be better off making a copy of your schema and exporting archive data out to this second copy. That way you'll still be able to query it which you wouldn't be able to do (easily) if it was XML
CResults
2010-03-10 13:04:37
Although I agree that XML takes more space than just storing the data in regular rows/columns, the DB is not just storing all the verbose repetitive XML tags. if you were to use a hex editor on the database file and look at some XML data, you would see that it is using some encoding scheme to save some space.
KM
2010-03-10 13:58:01
Interesting point, I was forgetting about the SQL2008 SQL datatype and also hadn't realised using it reduced the data footprint by as much as it does. Good shout. However, my suggestion is still the same, don't use XML, use a separate schema/copy of the DB for archive data.
CResults
2010-03-10 14:39:28