Hi all,
I'm in the process of designing the architecture of Database Snapshot
feature for Storage Server. I had a research on how each RDBMS servers
support creating database snapshots. Here are my findings.
*H2: *In H2, we can create a snapshot through a JDBC call. *SCRIP TO
'{FILE_NAME}'* query can be used for that.
*MySQL: *We can use *mysqldump* tool for this.
*SQL Server: *This can be done via JDBC. Query is *BACKUP DATABASE
{DATABASE_NAME} TO DISK='**{FILE_PATH}**' *
*PostgreSQL: *This can be done by* pg_dump *tool.
*Important facts in each approach*
In case of H2, the file is created in client side (i.e. comes via network
if we have the database server in a separate node). But it's very unlikely
someone would use a separate node for H2 or use in production. On the other
hand we basically support H2 for the unzip-and-run use case. Hence this
approach shouldn't be a problem for H2.
In both MySQL and PostgreSQL cases, if we run the mentioned tool at client
side (i.e. at SS node), the snapshot file is also created at client side.
This can be a problem when the database is huge, as a huge amount of data
is transferred through network. Therefore we decided to let the tool be at
server side and run it from client side through SSH, programmatically.
(JSch[1] library will be used for that.) When configuring SSH, we have to
generate a private/public key pair for the SS server and use it for SSH
authentication.
In case of SQL Server, the file is created at database server side by
default. So it's not going to be a problem.
After workflow support[2] is added for Storage Server, we will be
implementing the support to create snapshots by invoking a workflow,
instead of doing it by a single click in UI. This will be discussed in
details later.
[1] http://www.jcraft.com/jsch/
[2] Email: "[Storage Server] The design of Workflow support for SS"
Thanks,
--
*Bhathiya Jayasekara*
*Software Engineer,*
*WSO2 inc., http://wso2.com <http://wso2.com>*
*Phone: +94715478185 <%2B94715478185>*
*LinkedIn: http://www.linkedin.com/in/bhathiyaj
<http://www.linkedin.com/in/bhathiyaj>*
*Twitter: https://twitter.com/bhathiyax <https://twitter.com/bhathiyax>*
*Blog: http://movingaheadblog.blogspot.com
<http://movingaheadblog.blogspot.com/>*
_______________________________________________
Architecture mailing list
Architecture@wso2.org
https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
