33

There's really no one 'best way' to store time series data, and it honestly depends on a number of factors. However, I'm going to focus on two factors primarily, with them being: (1) How serious is this project that it deserves your effort to optimize the schema? (2) What are your query access patterns really going to be like? With those questions in ...


17

You have pretty much 4 choices: NoSQL - definition Every record is stored as a set of Key/Value pairs. It is very flexible and fast. Not all of the report writers out there support this style of storage. There are many example database implementations of NoSQL. The one that seems to be most popular right now, is MongoDB. EAV - definition This is where you ...


13

It depends on what's at the other end of the mount point. If the mounts are all LUNS spread across the same physical drives, then no gains. If the LUNS are all over a shared, slow, iSCSI channel, maybe no gains. If the LUNS are all under 1 controller...you see how many variables are at play. There's no one answer. To determine the configuration of the mount ...


11

Why are you not getting minimal logging? I have found the Data Loading Performance Guide, which you reference, to be an extremely valuable resource. However, it is also not 100% comprehensive, and I suspect that the grid is complex enough already that the author did not add a column Table Partitioning to break out differences in behavior depending on ...


11

is there any performance benefits to keep the data files split across different logical drives? If all the volumes map to the same set of physical disks on the SAN there's normally no difference. However, if each Volume maps to a different SAN LUN, it's possible for the SAN to allocate storage resources differently to the volumes. For instance, they can be ...


10

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave. This allows Pgpool to load ...


9

FILETABLE data is built upon the FILESTREAM feature of SQL Server, and uses the Windows Server filesystem to store VARBINARY(MAX) data in discrete NTFS files on the filesystem of the server itself. This data is not stored in the database, therefore the database file size will not include these 10GB+ files. Database backups will, by default, include the ...


9

Ah, what is the problem? a table with 54 million records A small table. Nice. I have one here with 8.5 billion rows. we have !300 inserts and !100 updates per minute Yeah. Small. I know. I have one here with around 500 million inserts per day. That is 347222.2222222222 per minute. All that running on stock hardware. Seriously. THough not totally as ...


9

You touch several subjects, therefore it is important to establish their scopes and the links that exist among them. Firstly, if the specific business domain you are working with implies (a) multiple one-to-many associations along with just one associative entity type —or many-to-many [M:N] association—, and (b) you are representing said characteristics with ...


8

Based on the results of numerous internet searches I can't find any (post-SQL Server 2000) reason to not use mount points. The main reason is someone had a bad experience with them (or, conversely, no experience with them) and has completely ridden them off... forever. This is otherwise known as personal preference. Now, there are some reasons that you ...


8

In addition to CM_Dayton's answer and Sean Gallardy's answer, one issue not yet identified with Mount Points is related to security. To quote Guidelines for Setting SQL Permissions on Mount Point Folders: Although the mount-point root folders may look like regular folders and are accessed in the same way folders are accessed, they are not regular folders....


7

At this point, I guess SQL Server with an AlwaysOn cluster will satisfy HA, but what about read/write? I think this SQL Server 2012 whitepaper will be a good read for you at this point to get a feel for what's possible with the two different features (Availability Groups and Fail-over Clustered Instances) that come under the AlwaysOn brand name. Deploy ...


6

I thought that Jeffrey Yao did a fantastic job answering this question here. I've edited it a little bit and sometimes include my own mission statement on my resume, but here's my "slightly" modified version of Jeffrey Yao. This is not specific to mariadb, but I don't think it has to be. "Mission: I am committed to providing a robust and secure database ...


5

So let's look at the "roles" here represented in the referenced architecture. Keep in mind, this is the "2 cent tour" of what the architecture is all about. Mongos This process is the "router" in that it acts as the interface for a "client" to connect to a sharded cluster. You can have many mongos instances, so you are not limited to "one" only. As pointed ...


5

With respect to your question about using Vertica during the ETL, it's very rarely necessary (although not unknown) to use a different type of database for the ETL. I would not do that unless you perceive a specific need to do so. The only times I've ever heard of this being done due to interactions with legacy data sources. Although @Kermit works with ...


5

in what circumstances would it be justified to use a primary key that is a combination of the type of the record(let's say, CH for Chinese food, In for Indian and MX for Mexican) and a sequence number ? Under no circumstances would it be justified. A column containing two different data elements as one value is a violation of first normal form. 1NF ...


5

Its 2019 and this question deserves an updated answer. Whether the approach is the best or not is something I'll leave you to benchmark and test but here is an approach. Use a database extension called timescaledb This is an extension installed on standard PostgreSQL and handles several problems encountered while storing time series reasonably well ...


5

Here is a structure that is at least very close. There is no programmatic way to get the parameters (unfortunately). You need to format them into XML to pass in. The Login that initiates a SQL Agent job seems to only be logged in the message column of msdb.dbo.sysjobhistory, for step_id = 0. This value can be extracted, but not during the execution of the ...


5

I would use a server level trigger on CREATE DATABASE. Something like this: CREATE TRIGGER [CreateDatabase_OwnerMapper] ON ALL SERVER WITH EXECUTE AS 'sa' FOR CREATE_DATABASE AS BEGIN SET NOCOUNT ON; DECLARE @DatabaseName NVARCHAR(128) = (SELECT CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') as NVARCHAR(...


5

But a single row can contain more than 1000 criteria No, you are predicating your data design on a flawed relational model. Putting the cart before the horse. The tail is wagging the dog. I think you mean that a single entity can have 1000 attributes. In such a scenario, and particularly when most are null, the best solution is usually an Entity-Attribute-...


4

Having done this type of project before, here's some of the practical gotchas to look out for: "2. This master then does one-way slave propagation to up to 200 read-only servers via VPN." Think carefully about the network traffic required here. In the simplest terms, if the insert/update/delete load on the database takes up 1/200th of a network cable's ...


4

Answer originally left as a question comment. For zero downtime during tables refactoring, we create new tables along with old ones, and switch in small steps. From Developing Low-Maintenance Databases by Alex Kuznetsov on Simple Talk. Instead of making all the changes at once, we make multiple incremental changes, as follows: Make sure that ...


4

I'm going to assume that you already have the budget to implement some data warehouse solution. Just briefly talking about Vertica; it is a load and read optimized platform, and certainly not designed for OLTP. The piece on staging and processing data would need some more thought. Vertica isn't really designed to have data staged, cleansed, and moved into ...


4

The raw data for that table is 1.4GB (54m * 28 bytes), the indexes will add some data but nowhere near a huge amount, let's say 5GB for the index. So whatever you do: why only 4GB ram? Make it 32GB for room for growth. MySQL on an SSD will be nicely fast. NoSQL is a buzzword that means nothing, so unless you have a specific product in mind, I will ignore ...


4

There is no particular policy to faster insertion (loading), which can direct you to your target. Instead, there are some configuration and prerequisites which can help you to improve write (insert) performance. The performance of any application, installed on OS lies on the hardware of the server. So, first, you should identify how much IOps disk you ...


4

The script that SSMS generates is not the best one. You should be able to use simple ALTER TABLE: ALTER TABLE TableName ALTER COLUMN ColumnName VARCHAR(255) NOT NULL Obviously, make sure in advance that the text that you store in this column would fit into 255 characters limit. I'd strongly recommend to try it in test environment first to get an idea how ...


4

What you're describing sounds like Microsoft Sql Server HADR commonly called AlwaysOn Availability Groups. It combines traditional Windows clustering with Database Mirroring to allow you a primary replica and up to 4 (Sql Server 2012) secondary replicas. Once set up correctly it will provide you with a single IP address and port # to use as a connection ...


4

Oh god no, do not use the SQL Server Destination. At this point, the negligible performance gains are not worth the random error it may throw while loading data. OLE DB Destination is rock solid, provides excellent performance and doesn't suffer from the infernal errors you can run into with the SQL Server Destination. Both components can store data into ...


3

Obviously when you check in a changeset, you know who the customer is (or was) according to what you describe about your process. Even TFS requires something to be the same, so that it can maintain history. In any case, in multiple versions of the same object, what is constant? Is the "Name" constant? (Probably not.) If the Customers table were that ...


3

Welcome to DBA Stack Exchange! My problem is that I don't know the 'speed' of SQL when it deals with stats and maths, compared to other language. I know that basic functions (correlation, R^2, ...) are already implemented in SQL, but I am using far more 'advanced' (I mean 'complex' here..) functions (even if I have not represented it here). As a ...


Only top voted, non community-wiki answers of a minimum length are eligible