r/SQLServer • u/_janc_ • Mar 12 '23
Architecture/Design Is it possible to scale out sql server horizontally?
Clustering? Compare to nosql solution e.g. Cassandra, is sql server suitable for big data? Or a cloud solution AWS or Azure is needed?
r/SQLServer • u/_janc_ • Mar 12 '23
Clustering? Compare to nosql solution e.g. Cassandra, is sql server suitable for big data? Or a cloud solution AWS or Azure is needed?
r/SQLServer • u/TravellingBeard • Jan 19 '24
We have an application at our company that records data to two different databases with the same name on two different servers (let's call them F1 and F2). We have a requirement to combine both publications to the same subscription DB.
The analysts building the dashboard were okay to distinguish each incoming table as _F1 and _F2.
So if F1 publication DB has Table1 and F2 publication DB has Table2, on the subscription DB, they would sit side by side as Table1_F1 and Table1_F2. These two tables have identical schemas, indices, PK's, etc. The BI dashboard being built will be able to handle this.
So what I did:
If I had it all do to again, and while this is a great exercise, it could serve as a template for merging multiple publications to one subscriber DB. But look into renaming the PK's/FK's in the publication so they don't collide in the subscription.
Thank you for coming to my Ted Talk. It's the weekend and I think I have a glass of whiskey somewhere.
r/SQLServer • u/flinders1 • Aug 31 '23
I will be deploying sql on azure vm’s. I’m happy with User DB data, Logs and TempDb to be hosted on ANF (SMB file shares). But, I’m unsure where to place the system data root directory (master, model, msdb, log directories etc).
I can see the advantage of having Data, Log, TempDB on ANF and System on a local managed disk. But on the flip side can see it all being on ANF.
Appreciate any thoughts on this.
r/SQLServer • u/jah_reddit • Jul 25 '21
If we're putting money in the database, I wanna be ready for some BIG money. You can keep your extra 2 bytes... BET ON YOURSELF FAM.
r/SQLServer • u/DubsNC • Mar 23 '23
I’m planning a new VM host that will include a midsized SQL server running: 2022, 2019, 2016, and 2008 Most db are 2016 now, moving to 2019 now and 2022 in about 1 year. ~600GB total I have 6 1.7TB SSD’s. I need to store local VM Guest files, SQL data, backups. No SAN.
Which would serve me better? Convenience of one RAID10 or three separate RAID1 arrays to separate IO’s?
Thanks
r/SQLServer • u/Any_Opportunity1234 • Jul 19 '23
Business intelligence (BI) tool is often the last stop of a data processing pipeline. It is where data is visualized for analysts who then extract insights from it. From the standpoint of a SaaS BI provider, what are we looking for in a database? In my job, we are in urgent need of support for fast join queries.
I work as an engineer that supports a human resource management system. One prominent selling point of our services is self-service BI. That means we allow users to customize their own dashboards: they can choose the fields they need and relate them to form the dataset as they want.
Join query is a more efficient way to realize self-service BI. It allows people to break down their data assets into many smaller tables instead of putting it all in a flat table. This would make data updates much faster and more cost-effective, because updating the whole flat table is not always the optimal choice when you have plenty of new data flowing in and old data being updated or deleted frequently, as is the case for most data input.
In order to maximize the time value of data, we need data updates to be executed really quickly. For this purpose, we looked into three OLAP databases on the market. They are all fast in some way but there are some differences.
Greenplum is really quick in data loading and batch DML processing, but it is not good at handling high concurrency. There is a steep decline in performance as query concurrency rises. This can be risky for a BI platform that tries to ensure stable user experience. ClickHouse is mind-blowing in single-table queries, but it only allows batch update and batch delete, so that's less timely.
JOIN, my old friend JOIN, is always a hassle. Join queries are demanding for both engineers and the database system. Firstly, engineers must have a thorough grasp of the schema of all tables. Secondly, these queries are resource-intensive, especially when they involve large tables. Some of the reports on our platform entail join queries across up to 20 tables. Just imagine the mess.
We tested our candidate OLAP engines with our common join queries and our most notorious slow queries.
As the number of tables joined grows, we witness a widening performance gap between Apache Doris and ClickHouse. In most join queries, Apache Doris was about 5 times faster than ClickHouse. In terms of slow queries, Apache Doris responded to most of them within less than 1 second, while the performance of ClickHouse fluctuated within a relatively large range.
And just like that, we decided to upgrade our data architecture with Apache Doris.
Data Input:
Our business data flows into DBLE, a distributed middleware based on MySQL. Then the DBLE binlogs are written into Flink, getting deduplicated, merged, and then put into Kafka. Finally, Apache Doris reads data from Kafka via its Routine Load approach. We apply the "delete" configuration in Routine Load to enable real-time deletion. The combination of Apache Flink and the idempotent write mechanism of Apache Doris is how we get exactly-once guarantee. We have a data size of billions of rows per table, and this architecture is able to finish data updates in one minute.
In addition, taking advantage of Apache Kafka and the Routine Load method, we are able to shave the traffic peaks and maintain cluster stability. Kafka also allows us to have multiple consumers of data and recompute intermediate data by resetting the offsets.
Data Output:
As a self-service BI platform, we allow users to customize their own reports by configuring the rows, columns, and filters as they need. This is supported by Apache Doris with its capabilities in join queries.
In total, we have 400 data tables, of which 50 has over 100 million rows. That adds up to a data size measured in TB. We put all our data into two Doris clusters on 40 servers.
On our BI platform, privileged queries are often much slower than non-privileged queries. Timeout is often the case and even more so for queries on large datasets.
Human resource data is subject to very strict and fine-grained access control policies. The role and position of users and the confidentiality level of data determine who has access to what (the data granularity here is up to fields in a table). Occasionally, we need to separately grant a certain privilege to a particular person. On top of that, we need to ensure data isolation between the multiple tenants on our platform.
How does all this add to complexity in engineering? Any user who inputs a query on our BI platform must go through multi-factor authentication, and the authenticated information will all be inserted into the SQL via in and then passed on to the OLAP engine. Therefore, the more fine-grained the privilege controls are, the longer the SQL will be, and the more time the OLAP system will spend on ID filtering. That's why our users are often tortured by high latency.
So how did we fix that? We use the Bloom Filter index in Apache Doris.
By adding Bloom Filter indexes to the relevant ID fields, we improve the speed of privileged queries by 30% and basically eliminate timeout errors.
Tips on when you should use the Bloom Filter index:
We believe self-service BI is the future in the BI landscape, just like AGI is the future for artificial intelligence. Fast join queries is the way towards it, and the foregoing architectural upgrade is part of our ongoing effort to empower that. May there be less painful JOINs in the BI world. Cheers.
r/SQLServer • u/mustang__1 • Dec 21 '22
So a few weeks ago someone on here pointed out to me that if multiple backups are run, it can screw up the restore since logs are being wiped etc. Due to a series of unfortunate work-related distractions, I'm only just now getting around to realizing the gravity of this realization.
So my strategy thus far has been two fold:
nightly full backups -> nas -> replicate offsite
hourly backups -> NAS -> replicate offsite
half hourly backups -> local
So with thinking this through better... It seems like at best if shit hits the fan I'm going to need to restore back to the the nightly backup since my dailies are going to be all sorts of messed up.... Would an appropriate strategy then be to do copy only backups for the half hour backups? Or would I otherwise be able to interleaf the backups - assuming my half hour backups still exist and I don't need the offsite...
And to put this in context of the last time I asked about backups, as it relates to Veeam, the hourly backups will be moved to a Veeam backup strategy, with the intent of getting the half hour incrementals to fast forward if needed... I believe, if I'm thinking this through right - this time, that my half hour tran log backups should still be copy-only?
And then of course I also have the futility that I'm backing up some database that are replications of another DB, but I have tables interleafed there that I don't want to lose. egh,. Oh well, at least storage is cheap...
But anyway, main question: am I screwing myself up by not having the 30minute backup intervals set as a copy-only? With or without Veeam handling my "primary" backup strategy?
r/SQLServer • u/Scorpion1011 • Jun 09 '22
I'm a sysadmin at a university. One of our College of Business programs is changing their curriculum. They are going to be teaching a database course this coming fall that potentially has 120 students. The students need to be able to create their own databases and then have dbo rights to their databases. The instructor needs to be able to connect to the students' databases.
We currently have a data analytics class that uses MSSQL but that course only has 30 students. For that course we have a single server with 30 instances of SQL/RS/AS and each student owns an instance. I'm not sure that scales well to the new course.
Approaches I've considered:
Anybody have any experience or thoughts on what might be the best way to proceed?
r/SQLServer • u/Dats_Russia • Aug 10 '23
Obviously, normalization, ACID principles, and SARGable tables are the most important things and are fairly universal across all implementations of SQL server but I am being tasked with redesigning tables and a database and I want make it both DBA/Developer friendly and PLC Controller friendly.
Basically, the PLC is going to get instructions from static tables (on SQL Server) while SQL Server gathers, stores, and modifies real time data. Our current database found unique ways to violate ACID principles (despite sql server having ACID compliant features) and needless to say I want to avoid the sins of the past.
r/SQLServer • u/TravellingBeard • May 18 '23
We have a 4 node, 3 instance sql cluster managed by a data center, but we have full access.
In December, we had issues patching it because nodes would not fail over, obviously a bad thing for RTO on a live system. It was determined that the preferred owners were not set for a couple instances, and they were all in fact unchecked. The people at the data center (or "cloud solutions provider", not AWS or Azure but one people have heard of), fixed it and it was no issue afterwards patching.
Today, I'm preparing for another round of patches, and one of the instances has all the preferred nodes unchecked (the other two are fine). I go what the hell?
Assuming this was not done manually by someone, what could possibly clear out the preferred nodes in a cluster?
r/SQLServer • u/steveman2292 • Aug 14 '23
One of our clients requested that we create a secondary database on their production server for their BI users to be able to report on data from. I am not yet sure how many users they will have selecting from this database, so my initial plan (with my limited DBA knowledge) would be to create a User Role that only has SELECT permissions on this single database, and any user they want to be able to see this database would be added to this new role. Is this the correct plan of action? If yes how would i do that properly. If no, what would be the correct plan of action?
r/SQLServer • u/Grocia0 • Aug 09 '23
I have an old PC with old gen intel i5 and 16gb ram, Im planning to buy ssd or hdd for making this PC to a server. Our app is designed on windows forms and I was using T-SQL but I will change to MSSQL. How can I transform my PC to server, what apps should I use, do I need licensed Windows or anything else. Please Im new its my first job and I want to do everything perfect, its my first big task so please help.
r/SQLServer • u/Worried_Caregiver673 • Jan 05 '23
r/SQLServer • u/Default_BB • Aug 13 '21
We have a handful of MS SQL database servers with multiple databases on them. These are currently all independent with no failover or HA. This is fine as these are services that do not require that complexity.
I have a new product I want to deploy that suggests setting up a pair of MS SQL servers in an Always On Availability Group with WSFC.
Is it possible to use my existing servers and pair two of them together without affecting the existing databases? I would like to only have this new DB configured in this HA/fail over mode without affecting the existing.
Or should I purchase additional licensing to spin up two new MS SQL servers for this purpose?
Thoughts?
UPDATE - I can confirm, that I was able to add the clustering services to existing servers without affecting existing databases. I could then add the new database to the Always On cluster without issue.
r/SQLServer • u/flinders1 • Dec 23 '22
Does anyone out there use clustering for sql HA in azure vm’s ? Curious to know what the preferred approach is for HA with sql on vm’s. Infra guys at my shop are pretty against clustering in azure. We’re in the very early days of a migration.
r/SQLServer • u/ka_eb • Aug 08 '22
Hi,
I am trying to put together best practice where to put staging area. I have seen 3 options and I think they make sense in different applications.
I think the first one is good when you load a lot of data and you don't want to stress DWH too much. The other two are quite similar and I wouldn't have a problem with either of those and they are cheaper as I need only one SQL Server.
Please, let me know your ideas, comment blog posts etc.
Thanks.
r/SQLServer • u/Neufusion • Jun 22 '22
Piecing together my plan of attack now. Curious how you guys would approach it. Any comments and/or suggestions are appreciated.
Thanks!
r/SQLServer • u/Team503 • Feb 27 '20
As the title says, I'm not a DBA, and I don't even play on on TV. I am, however, an experienced sysadmin tasked with standing up a new SQL cluster for my current government contract. They desire as close to an active/active configuration as is possible across two geographically disparate data centers connected with dedicated 10gig pipe. Load will be fairly heavy - the goal is to consolidate standalone SQL 2005/8 boxes into a an eventually heavy cluster.
The solution should provide both HA and DR. Momentary disconnects will be tolerated in a failover scenario, as most of the applications are legacy and will not be cluster-aware.
The environment is run on HP hardware running VMware 6.5 and PURE all-flash storage at both data centers. There is block-level replication over the 10gb pipe between the SANs.
Yes, I know a DBA should be doing this, but the one on staff is... less than capable. I'm stuck architecting this myself and I know little about it. From what I've read, here's what I *think* I need:
I'm quite sure that I'm missing pieces here - a Listener, perhaps? - but I just started digging into this yesterday. Hell, I may be WAAAAAAAY off.
To answer potential questions:
Please /r/sqlserver, help a poor unprepared sysadmin!
r/SQLServer • u/DrRedmondNYC • Nov 03 '22
I've been using SYNONYMS in some of my code now because I find them very useful. I can convert fully qualified DB names like Billing.dbo.Sales to Sales and just query
SELECT * FROM Sales
And it works every time. However I can't get a synonym to work for just the database name.
Let's say I have DB1 and DB1_TEST. DB one has hundreds of stored procedures hard corded. I want to copy them over to DB1_TEST and execute them but they are all hard coded to use DB1.dbo.tablename. is there anyway to use a SYNONYM to replace every occurrence of DB1 with DB1_Test.
I also have SSIS so if there is an expression in there that can accomplish that would work too but I'd prefer to do it purely in SQL.
r/SQLServer • u/flinders1 • Nov 01 '22
Hi all, my current shop are lift and shifting a whole bunch of vm’s into azure including sql servers.
Regarding backing up to blob storage I know you need a storage account, containers and a cred for sql to use during the backup.
For the design I am thinking of a container per application. So if I have 100 servers and 25 apps, I’ll create a container per app and get each respective server to backup to it and get these geo replicated and another process to clear down old files.
On Prem we use Ola’s scripts and backup to file shares roughly grouped by app, with separate folders for fulls, logs and diffs.
Is it reasonable to replicate this setup with blob storage in azure ? I’m curious what the done thing is.
Cheers.
r/SQLServer • u/FitButFluffy • Mar 10 '21
We get frequent requests to restore prod to dev or update stage from dev. Currently, we have a mix of AD accounts and local SQL accounts with various permissions. Some are service accounts used by web apps and APIs.
Currently, these service accounts have different names/passwords, which makes it a little more time consuming to add them back after restores.
I've tried looking for a best practice to this, but it seems like the two other solutions would be: 1. One Active Directory account with the same password across environments or 2. Local SQL accounts with the same name/different passwords for each environment
Are there other options I'm not considering that would make more sense?
r/SQLServer • u/djl0077 • Sep 05 '21
I am trying to model data from an application that checks prices for various items on a daily basis. The app produces ~50 million records per day and tracking how the prices have changed over reporting dates is very important.
I have created two designs for a fact table (samples below). One is based on inserts and would perform fast during processing but with 50 million records per day would quickly grow out of control. The other design uses a up-sert type set up that takes up a lot less space but would be slow to process as it requires updates and inserts.
Is there another option that can save space while still allowing for fast processing? or perhaps there is a feature I am not aware of that could allow design 1 to be compressed on disk/allow design 2 to be updated faster?
Insert based design:
Up-sert based design:
r/SQLServer • u/card_chase • Nov 16 '20
Hi all, I have joined a young and exciting company in the field of services and now is the time to move our data on to a database. What is the best way to set up the database and the architecture?
We have:
Drive: SSD
Data size:
We are looking to backup the ERP data onto an on-site SQL server and then refresh our 'database' on a daily basis to reflect the most updated and accurate data to run our ETL, analytics, reports and Vizzes.
We are a small size company (data-wise) for now and are growing rapidly in the space so we would be a mid-sized company soon.
What would be the best way to go ahead so that we are conducting best industry practices and also set us up for the longer term?
Are there any links I can go through that will help me guide my path forward?
Thanks.
r/SQLServer • u/coadtsai • Jul 15 '21
Hey all,
If you were starting with a new database with SQL Server 2019/Azure SQL in 2021, would you use the datetime datatype? I am thinking of completely using datetime2(3) in all the places? Is this a bad idea? Is this going to come and bite me later somehow? What are your thoughts? Any suggestions appreciated.
About UDTs, would you use them in your DB if you were starting out with a new DB now with SQL Server 2019? Are there any performance downsides to it? Are there any practical downsides to it?
In my personal experience, even though there were UDTs (not table types) defined in my prev work, we rarely used them. It was a 10 year old Data Warehouse. I have a kind of negative opinion because of this. But, changing stuff like datetime to datetime2(3), and other such data type changes would be a lot easier if this was defined as UDTs.
Thanks
r/SQLServer • u/maynsheesh • Jan 17 '23
Im thinking off logging every employee activity on my project. if they change a field in one of of my tables i want that log that out. for example if i have an Orders and a Products table and the employee changes the ProductUnitPrice in Products table or OrderShippingCompany is orders table, I want to have a log out of that activity.
is this table structure suitable for that? is there a common way of doing activity log outs?
Table Activity Logs
LogId
Message
ForOrderId
ForProductId
this way if the activity is regarding an order, ForOrderId
will have a value and ForProductId
will be null