Tag Info

Hot answers tagged

7

My question is, do you think we should look into upgrading to SQL Server 2014 Enterprise so that we can partition our time sheet tables? No. Absolutely not. On a pocket change server of 8 cores you would be spending ~$50k and be unlikely to see any benefit. I would suggest trying, in this order: Increase the memory allocation to SQL Server. You ...


6

Let's break this question up into a few parts. Q: I need to insert 1mm rows a day. Is that a lot? Not really. 1mm divided by 24 hours divided by 60 minutes divided by 60 seconds gives you about 12 inserts per second. For a rough frame of perspective, it's not unusual to see 1,000 inserts per second in typical commodity servers with no tuning. Granted, ...


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

What you are seeing on net is mostly a copied advise where people actually want to say that "please don't make shrinking data file or log file a daily routine operation". Had it been so bad Microsoft would have removed it but it is still there and even most experienced DBA's and developer use it but they are aware about the after affects so they know what to ...


4

It's a good pick to use it? If I was hired on a new company, it's a good pick to use maintenance plan ( for me, looks like too "simple", don't want to look a bad DBA). Maintenance plans are not bad, but when your environment grows, the limited flexiblity and functionality that maintenance plans provide wont be sufficient. For e.g Maintenance plans ...


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 major differences are related to duplicate entries in both data and metadata, and the existence of NULL values. SQL implementations deviate from the relational model for practical reasons, for the most part. Read up on the topic in SQL and Relational Theory by C.J. Date or (Kudos to Bill Karwin for this title by the same author) read pages 2 and 3 in ...


3

If it's a data warehouse system you're looking at, you may wish to consider InfiniDB. It's an Open Source columnar storage engine designed for DW type loads - it falls into the NewSQL paradigm as defined by Michael Stonebraker. There's also InfoBright, which is a similar offering. Of course, you may not require any special software at all - vanilla MySQL ...


3

You haven't really given us much info about what this data is going to be used for. I mean, you have said what data is going to be stored, but what are you going to do with it? If your purpose is storing the data then reporting on it, then I think you're looking in the wrong place. A simple MySQL or SQL Database would do just fine and the reporting tools ...


3

Shrinking database log file is necessary if log has grown out of control whether after a one-time data delete or where recovery model of the database is FULL and no log backups have been carried out. To reverse the effect of a one-time operation that caused unexpected log file growth are convincing reasons for shrinking a database\ database file. ...


3

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 ...


2

I can speak for Postgres. PostgreSQL is really fast to copy to if you are a super user. Postgresql has a copy to command. Merely save the file as a csv (which can also be opened in excel) and use: COPY table FROM 'fpath' CSV HEADER DELIMITER ';' You can copy anything with COPY (SELECT STATEMENT) TO 'fpath' as well. If you must use excel formats like ...


2

circle_share1 does not require much in-depth design thought. circle_share2 requires some actual brain cycles to determine if the primary key will have the desired performance. Rows in circle_share1 will be written to the disk in the exact order they are inserted, thereby making inserts quicker. Rows in circle_share2 may be inserted anywhere in the ...


2

If you are using SQL Logins, then basically, no. You'll want to have sqlcmd use the person's domain login to connect to the SQL Instance (the -E parameter instead of -U and -P). You can use a login trigger to ensure that they are only able to connect via sqlcmd if you want to prevent them from logging in via SQL Server management studio (it isn't a perfect ...


2

0- CASCADE means to 'replicate' the changes that happens on the 'master' table to the 'details' tables. For simplicity, assume the relation (country, city). Each city[detail] belongs to a country[master], or each city is a child of a country. With cascade, if you delete a country, all cities belong to that country will be deleted. In your case, ...


2

InnoDB log files are stored on disk and so consume disk space - http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html. However the innodb_buffer_pool_size does impact memory usage. I find this a good reference for memory sizing - http://www.mysqlcalculator.com/


2

If all you want to do is keep track of which authors contributed to which papers, then all you need is a simple intersecting entity (see more) like this: Here the columns of the SUBMISSION table are just the two primary key columns from PAPER and AUTHOR. These two columns will jointly form the primary key of the intersecting table and will also each ...


2

The actual costs are difficult to quantify as they are often diffused. Often they appear as slower transactions. For a system used rarely, this may not be significant. For a system used for an hour a day, that is taking twice as long as is required the cost would be about 100 hrs a year per person using the system. Slower transactions may also have ...


2

PostgreSQL comes to my mind. You have pgcrypto library for encrpyting data and there are drivers for .Net


2

My largest obstacle right now with Cassandra are the no-join, no order by (since everything is ordered by key for you), and mass update. If your current data structure depends on joins (or aggregation) to pull off the queries you need you won't find a nosql option that includes these. You do have some options: Consider reviewing your current data ...


2

For your use case of key-value data, where the value is simple, MongoDB is more than you need. It is a document-oriented data store for complex value types. A specific key-value store would suit your needs. RIAK is the one I've looked at though several others exist. Since your retrieval will be by time range i.e. key range, Elastic's full-text ...


1

Having had a look at your rethink with just three tables (www.graphicsdesigned.co.uk/MuseumSchema4.gif) it still needs more tweaking. What if a museum is linked to more than one category? The way you have it set up at the moment you'll be adding another row to the museums table for each category that the museum is in. What you need is a "link table" which ...


1

Teaching yourself database design is indeed very tricky. You might benefit from finding a good tutorial that would help you master the basics. If you want suggestions concerning a good learning vehicle, ask. Here is a very oversimplified summary of some of the basics. Before you can design a database, you have to analyze the subject matter in some ...


1

There are several methods you can use such as mysqldump folder copy enterprise backup for mysqldump use following command: mysqldump -uuser -ppassword --database your_db_name --routines --triggers --events --lock-all-tables >outputfile.sql and use following command at destination: mysql -uroot -ppassword<outputfile.sql. You can also use ...


1

If you foresee that moving to Cassandra is definitely in your future, it will be easier to do while your dataset is still small and manageable. Also, as you learn and get a feel for Cassandra, a small dataset is a better one to make mistakes on (and thus, easier to correct them). That way your data model is solid by the time your dataset gets big, and ...


1

These are general recommendations, as you do not show the full extent of your queries to be performed (which kind of analytics you plan to do). Assuming you do not need real time results, you should just denormalize your data at the end of the period, precalculate once your aggregated results on all necessary timeframes -by day, by week, by month-, and work ...


1

If you don't have a DBA chances are the server hasn't been configured according to simple but critical best practices. You can't really make a definitive diagnosis until you've done that. Engage and expert to assess what needs getting done. Then either make the changes or have them do it.


1

At 14 million rows in your largest table and 16GB of RAM devoted to SQL Server it sounds like your entire DB almost fits in memory. If that is true the problem is likely to be quadratic (or worse) behaviour in your queries, which can only be truly fixed by rewriting the worst offenders. That said, I would try one or both of: Adding some SSD RAM to the ...


1

That is a tough one, if only there was a way to find things like that online. http://tinyurl.com/q7zxplh Make a list of the high level objects that need, such as planes, airports, routes, people, roles, etc. Then figure out how they are related, how you identify each object and what attributes they would have.


1

If Users can only be members of one group, you need only store a GroupId in the Users table. If not, the pattern you are looking for is commonly known as a "Join" table, containing only foreign keys and perhaps a generated primary key. If you have primary key UserId (uniquely identifies a user record) for the Users table and GroupId for the Groups table, ...



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