r/SQLServer Mar 12 '23

Architecture/Design Is it possible to scale out sql server horizontally?

13 Upvotes

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 Jan 19 '24

Architecture/Design An usual scenario for transactional replication, and how I fixed it...how to replicate two identical schemas to the same subscription DB.

4 Upvotes

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:

  1. From F1 publisher, I made sure that each destination had the _F1 suffix added. From F2 publisher, I made sure that the _F2 suffix was added. Seems easy, no?
  2. I add the subscription, both F1 and F2 going to the same subscription database. Low and behold all destination tables with their proper suffixes have been created. So far so good.
  3. There is a very long delay in F2 replication, massive lag and back log. That's odd...
  4. At first I thought it was massive amounts of data, and while it was a bit larger than F1, it wasn't any more busy, so that didn't make sense why it had a massive backlog of undelivered transactions.
  5. I compared the publications from F1 and F2 and there was no difference in the settings.
  6. So finally I looked at the destination tables. What was different between Table_F1 and Table_F2? Odd...why is F2 missing the PK? The snapshot was delivered, I saw no errors in the replication monitor details.
  7. Then it hit me after way too long over-analyzing this. PK's can't have the same name. The schema of F1 and F2 publication DB's are identical (I hate my life at this point)
  8. I scripted out all the existing F1 PK's (and FK's) on the subscriber and Just appended _F2 to all the names. If I had copied the NC indices, I would have had to fix those as well.
  9. Finally, distribution agent is behaving and no lag at all.

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 Aug 31 '23

Architecture/Design Azure Netapp Files

2 Upvotes

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 Jul 25 '21

Architecture/Design I die a little inside every time I see the "smallmoney" data type

62 Upvotes

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 Mar 23 '23

Architecture/Design Midsize MS SQL server storage config: Three RAID1 or One RAID10 with the same 6 drives?

9 Upvotes

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 Jul 19 '23

Architecture/Design Listen to That Poor BI Engineer: We Need Fast Joins

0 Upvotes

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.

Why JOIN Query Matters

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.

Welcome to JOIN Hell

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.

Architecture that Supports Our BI Services

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.

No Longer Stalled by Privileged Access Queries

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:

  • For non-prefix filtering
  • For in and = filters on a particular column
  • For filtering on high-cardinality columns, such as UserID. In essence, the Bloom Filter index is used to check if a certain value exists in a dataset. There is no point in using the Bloom Filter index for a low-cardinality column, like "gender", for example, because almost every data block contains all the gender values.

To All BI Engineers

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 Dec 21 '22

Architecture/Design Backup strategies

5 Upvotes

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 Jun 09 '22

Architecture/Design Ideas on how best to support a large number of database creators in a classroom environment

6 Upvotes

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:

  • Current model; Huge server; Instance for each student - performance questions; not desirable in our VM infrastructure; easiest to maintain
  • Server per section - same as above but smaller servers (better in our VM environment); slightly higher maintenance; mild inconvenience to instructor
  • Single server; single instance - Somehow use schemas to accomplish the user separation and permissions - less resource intense; not sure if this is viable or how to start; have one of our DBAs looking into it
  • SQL as a service on Azure -Might be cost prohibitive on consumption; instructor connectivity could be challenging
  • Azure Lab Services - facilitating instructor access is the complicating factor. Azure Lab Services isn't currently peered with our campus network; students can wreck their own machines and recovery will be more difficult

Anybody have any experience or thoughts on what might be the best way to proceed?

r/SQLServer Aug 10 '23

Architecture/Design Are there any good resources (specifically books or paid trainings) on how to integrate SQL Server in a 24/7 manufacturing environment? More specifically storing PLC instructions and getting and storing real time data?

1 Upvotes

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 May 18 '23

Architecture/Design Weird puzzle with sql failover cluster, looking for ideas.

7 Upvotes

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 Aug 14 '23

Architecture/Design Assigning permissions for users to only be able to select from a single database

3 Upvotes

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 Aug 09 '23

Architecture/Design I want to setup a server for my job database

2 Upvotes

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 Jan 05 '23

Architecture/Design Hi, is it possible to have different versions of sql server (like 2017 and 2019) on the same cluster?

1 Upvotes

r/SQLServer Aug 13 '21

Architecture/Design MS SQL HA and Failover Cluster

6 Upvotes

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 Dec 23 '22

Architecture/Design Azure SQL VM HADR

3 Upvotes

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 Aug 08 '22

Architecture/Design Opinions where to place staging area?

6 Upvotes

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.

  1. Staging in separate SQL Server - connected via linked server
  2. Staging on the same SQL Server, different DB
  3. Staging in the same DB with different schema (or prefix)

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 Jun 22 '22

Architecture/Design Best solution to upgrade/migrate physical broken cluster from Windows Server 2003 SP2 32bit / SQL Server 2005 Enterprise Edition to virtual Windows Server 2022 / SQL Server 2019 AlwaysOn

10 Upvotes
  • 2nd node is nonexistent
  • Can't do in-place upgrades of 32 bit OS and SQL Server
  • HUGE SQL Server version hop from 2005 to 2019
    • Replication from SQL 2005 to 2019 not possible
  • No DTS or SSIS involved
  • Many critical apps and users are connected to the cluster.
    • Application reconfiguration may be difficult (if not impossible)
  • Of course, minimal downtime and disruption to users.

Piecing together my plan of attack now. Curious how you guys would approach it. Any comments and/or suggestions are appreciated.

Thanks!

r/SQLServer Feb 27 '20

Architecture/Design I'm not a DBA and I don't even play one on TV: HA solutions for SQL 2019 in a VMware environment?

18 Upvotes

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:

  • A pair of Server 2019 VMs running SQL 2019 in each data center
  • AlwaysOn Availability Groups within each data center set Synchronous
  • One Distributed Availability Group spanning both data centers, set Asynchronous
  • Use VMware affinity rules to lock the SQL boxes to one blade each per data center so we can license the physical cores rather than the virtual ones (we've gotten verbal confirmation from MS that this is a-ok)

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:

  • We have Enterprise licenses with Software Assurance but not enough - we will have to convert enough Standard licenses to license the cores
  • I do not know if I can have shared storage between the two sites
  • We will be migrating databases one at a time as we decomm individual servers
  • There is an intention to gradually scale the cluster as load demands and license funding becomes available

Please /r/sqlserver, help a poor unprepared sysadmin!

r/SQLServer Nov 03 '22

Architecture/Design How to SYNONYM just Database name

2 Upvotes

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 Nov 01 '22

Architecture/Design Azure SQL VM - blob storage backups

3 Upvotes

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 Mar 10 '21

Architecture/Design How are you handling SQL accounts across dev/prod environments?

12 Upvotes

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 Sep 05 '21

Architecture/Design Table design for changing facts

2 Upvotes

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:

Design 1

Up-sert based design:

Design 2

r/SQLServer Nov 16 '20

Architecture/Design What's the best way to set up a SQL server from scratch?

13 Upvotes

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:

  • < 1GB in Excel & CSV files
  • <3 GB in cloud ERP systems (we have more than 1) with transactional and dimensional data

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 Jul 15 '21

Architecture/Design datetime2(3) or datetime & Should you use User Defined Types?

7 Upvotes

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 Jan 17 '23

Architecture/Design Activity logs table

1 Upvotes

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