First rule of horizontal scaling of a database is to avoid it. At all cost. You should consider it only when no server you can possibly buy can handle your data. And there are servers which can handle enormous amounts of data today.
Horizontal scaling of a database will give you:
at least an order of magnitude more complicated system:
even in a simplest ...
but I have difficulties to see the added
value compared to Cloud SQL instances with replication and backup. I
am not very familiar in scaling heavy load systems.
Galera-based clusters like Percona XtraDB Cluster support true active/active multi-master, so failover is seamless because you can actually be writing on any node at any time (tho it's ...
First, in general, if you have dedicated servers, I think you are usually better off using them. Through a business that does hosting for a PostgreSQL-backed ERP, I am relatively aware of what the problems are. For us, replication is more or less set up out of the box but tuning the db is problematic. I would expect that 2ndQuadrant's hosting (which I ...
I can't tell you about the cloud disk but when you backup to a network share you can:
Create a network share and assign permissions (I go with full control) to the SQL Server database engine service account (it has to be a domain account)
Backup database to this share like that:
BACKUP DATABASE AdventureWorks TO DISK = '\\MyBackupShare\AdventureWorksFULL....
In my extensive testing of cloud SQL on cloud servers (rackspace cloud, to be specific), I found that splitting the ldf and mdf to distinct block storage volumes made a significant improvement in performance. I achieved really hot performance by putting the ldf on an SSD based block storage volume and the mdf on a standard block storage volume.
Oh goodness, you have a lot of questions in here. I'll try to unpack them all.
Q: What is "SQL Server in the cloud"?
The term "the cloud" is too generic - you might as well be saying "SQL Server on the network." There are lots of different ways to deploy SQL Server in the cloud, some of which function exactly like on-premises servers (it's just that ...
No issues with IDENTITY or SEQUENCE in Azure SQL Database, or SQL Server on Azure VMs.
If you move to a federated design, you should not use the IDENTITY or SEQUENCE value as the only key of any federated table. If your key is something like (TenantId, SequenceVal), then you can have duplicate SequenceVal, but still allow the rows to be globally unique. ...
Heroku have a very simple and quick interface for creating PostgreSQL databases, but you might find that the options are limited. AFAIK you get a single user account, and because the system is shared there are limitations on what you can tweak. For the most part you get to choose the memory allocaton, and this is what they charge by. You are limited in the ...
It would be easier to mod the script so that it writes out either the last value, or a value every n times so that you can restart the script with the last known value in case of failure.
Linux hardware is pretty reliable, I've had servers that have had over 4 year uptime but of course nothing is guaranteed.
You have an XY Problem. You need to use an algorithm that allows you to restart from a specific point.
Dividend.num <=@endnum exists in two places.
Get rid of the 2nd one
Divisor.num between 2 and sqrt(dividend.num) is more restrictive thanDivisor.num <= @endnum`.
get rid of the less restrictive one.
Since the NOT EXISTS ...
You might want to look at EnterpriseDB cloud-database - they offer pre-built AWS and HP Cloud setups which include support.
Rackspace knowledgebase has steps for installing postgresql, although they only provide database support for mysql, SQL server and Oracle.
The OpenStack documentation also includes steps for installing postgresql.
You can research "ashnik" (EnterpriseDB - The Postgres Database Company) - Ashnik enables enterprises adopt open source software solution with confidence
For information, you can view this link:
Depends on the cloud environment you're using but typically it makes sense to put them on the same logical drive. If you need additional IOPS you can stripe across multiple volumes but still present a single drive.
In a cloud environment you are not the sole consumer of the storage you are allocated. You get a small slice of a very large pie and as such all ...
The "best" solution which we found was:
1. migrate the Access tables to an SQL tables
2. Migrate view & stored procedures to the SQL server
3. make the MS-Access run from a network location. This way there are two Ms-Access: one for the development work, the other for everyday use.
This way ms-access can scale.
As the developer who has done this I can ...
Is there a reason to have Regular SQL Failover Clustering when database is in Azure Cloud?
This completely depends on your use cases, knowledgebase, requirements, and cost.
In some ways it would make sense to use S2D and setup an FCI over an AG, most notably the storage costs for high perf disk can be larger. Since each node in an AG has a whole copy of ...
I'm going to preface my answer by indicating I tried this out a few years ago during a demo of Azure's initial IaaS offerings so things have almost certainly changed.
Yes, you can provision multiple disks and create a spanned volume but I'd also check the segment size of the LUNs. If you're looking for high throughput to scan in tables like for building ...
There's no need for a commercial product. You could make use of for example Pentaho Data Integration to do the task.
Back to the main question, here are the steps you could do to feed your data without recreating the tables every time:
Create a capture data change mechanism (it works as a table), to log changes on tables (which table has been inserted with ...
To expand on Dan's answer with some detailed info:
If you have the option setup a small Linux server at home. You might have to modify the sshdconfig to allow for setting up SSH tunnels depending on your release. You might also need to modify the firewall config to allow port 22 connections, though most distributions do this automatically now when you ...
To "improve query" response on reading data, replication will help. You could write away to your primary and read from secondaries to distribute the queries. Also the primary then is relieved of the expensive reads, and can be busy with only writing. Just be aware that there can be some lag on the data. For example a forum, it's not 100% necessary that ...
Questions to ask could include:
What fits well with your current skills? (That might make it 'easier' to use.)
Is your focus on getting something working soon, or on developing new skills?
What is the cost of your choices (money, time, effort, unfamiliarity, etc)?
With your list you do emphasize cost as a factor and the database is not expected to be huge. ...
MySQL has a built-in functionality called MySQL replication that allows the synchronisation between 2 servers, by applying on a "slave" the operations that have been done on the "master". If no writes are done on the slave, that works pretty well and, while it has its issues, it is widely used by most MySQL users (Facebook, Twitter, etc.) for high ...
Replication is always tricky. If you can, avoid it as long as possible.
You have to keep in mind that you have to setup a multimaster cluster which complicates things a lot. We've tried mysql multimaster setup several times but under load the databases got async - and that's the real horror.
Also have a look at the CAP theorem - you have to decide, which ...
Londiste, Bucardo or Slony-I can do this.
See Replication, Clustering and Connection Pooling on the PostgreSQL wiki for details.
Londiste would probably be my first choice for this use case, mainly for simplicity.
You may want to reconsider wal-e from Instagram which does just this,
wal-e Simple Continuous Archiving for Postgres
It supports AWS. And as of November 2013, shortly after you asked the question, it supports table spaces.