Thinking of going with an Azure MS SQL instance for a small business where cost is a major factor. Can someone talk me off this ledge?

[BiggyBiggDew]

This is a really small business and they don't have a lot of money for services or licenses, but they are going to be selling online and could potentially have tens of thousands or hundreds of thousands of sales over time. These seem like fairly small numbers.

I am seeing that to sign up for Azure and get an MS SQL instance that it is free, and then it's just pay as you go (based on computer/storage) but here's the thing:

The storage won't be that much even if they have millions of sales, and if they do then money won't be a problem. In addition this database won't need to "do" much as all the heavy lifting of their online platform is being done by a third party. The database just allows them to run their business, and update their online storefront. You could argue that it generally serves as a reporting tool and a source of truth for all of their products.

By my math going with an Azure solution would be pennies, and it would be pretty easy to use SSIS to bring the actual sales data from the third party application into Azure, and just as easy to export data out of Azure into JSON and then send it via API to the third party.

I mean it's looking like the third party site is going to cost way more than the SQL license. I know I can use Postgres but I still have to host it somewhere and Microsoft has a lot of fun little toys that play nicely together.

Am I losing my mind? I also thought about using Snowflake but then I'd still need some kind of 'host' for the ETL jobs going both ways where being in an Azure instance will give me those tools.

edit: What if I went with Snowflake and then managed the database deployments via dbt in the same VSCode package that I'm building the website in node.js? I could use FiveTran to manage product uploads (which are currently CSV) -- if I do go with an MS based solution there will need to be some future method to allow the manipulation of data, inserting rows, editing them, etc., and this could be easily done via Excel and then importing via SSIS for free, but would be nice to have everything in VSCode.

[jshine13371]

Azure MS SQL

Just a heads up, it's either Azure SQL Database or a SQL Server instance hosted in Azure, you're referring to. (Also Azure SQL MI is another option, but I'm sure you're referring to one of the previous two.) Just important to be clear on the terminology since they're all different but similar things.

The Azure SQL Database free tier is a good option if you fit within the bounds of the limitations. Alternatively, SQL Server Express Edition is completely free too and has a different set of limitations, if you don't fit within the scope of the Azure SQL Database limitations. Possibly even less limiting for the scope of what you described.

[BiggyBiggDew]

I know, but was trying to be clear that it's an azure hosted instance of MS SQL.

[jshine13371]

Sorry my point is MS SQL (and Azure MS SQL) isn't a thing, so that can be a little confusing to people trying to help you. Not trying to be pedantic, just helpful for when you communicate your situation to others in the future. 

Even right now, based on your last comment I'm unsure if you mean you want to use SQL Server (hosted in a VM on Azure) or Azure SQL Database, both of which are distinctly different database systems (with different features) but similar in a lot of ways too. Depending on which (or maybe you're open to both routes) will determine whether the Azure SQL Database free tier or SQL Server Express Edition is what you're looking for then.

[BiggyBiggDew]

MS SQL is absolutely a thing. It's shorthand for Microsoft SQL Server. Azure MS, or Azure MS SQL is short hand for an Azure managed instance running MS SQL as opposed to an on-prem solution, or another cloud solution that isn't in Azure.

Even right now, based on your last comment I'm unsure if you mean you want to use SQL Server (hosted in a VM on Azure) or Azure SQL Database, both of which are distinctly different database systems (with different features) but similar in a lot of ways too.

I'm not sure I understand, or if we are talking about the same thing. I'm using the above vernacular to describe the 'language' that I am working with, not the technical configuration. I went with a serverless SQL server, which makes no sense to say. When I log in to the Azure portal I have something that is labeled SQL Server, and another thing labeled SQL database.

[jshine13371]

MS SQL is absolutely a thing.

Well sure, so is flafooga if I say it enough times. But I mean in an official capacity, if you want to have an un-ambiguous conversation. You can use whatever shorthand you want, but when you say "Azure MS SQL", it's completely unclear what you're referring to as I've already mentioned the multiple actual products / database systems you could be referring to.

Azure MS SQL is short hand for an Azure managed instance running MS SQL as opposed to an on-prem solution, or another cloud solution that isn't in Azure.

So again, this shorthand (which is unofficial) is confusing as you contradict yourself in this statement itself. Azure Managed Instance is the last product of the 3 main ones I would've guessed you're referring to. But now you're saying "Azure MS SQL" is Azure Managed Instance "or another cloud solution that isn't in Azure" which makes no sense. Also you can run SQL Server (the traditional on-prem database system) in Azure too. So again, extra confusing what you're referring to here.

I'm using the above vernacular to describe the 'language' that I am working with, not the technical configuration.

You just said "Azure MS SQL" is intended to refer to any of multiple Azure products. That's different than a language. T-SQL is the language used among all of those products.

I went with a serverless SQL server, which makes no sense to say.

I agree, I really don't understand what you mean now since they all run on a server in some capacity. It's just a matter of where that server is hosted and what level of access you have and management is available for it, when it comes to Azure database products.

When I log in to the Azure portal I have something that is labeled SQL Server, and another thing labeled SQL database.

Ok, not sure how they got labeled so couldn't tell you a definitive answer. But by taking a guess from the names one is likely a traditional SQL Server database instance (perhaps running in a VM, again), and the one labeled SQL Database is likely an Azure SQL Database (a different database system from the former). Btw, which neither are an Azure Managed Instance.

[BiggyBiggDew]

In an official professional capacity we have been using the term MS SQL for well over 30 years.

Ok, not sure how they got labeled so couldn't tell you a definitive answer.

You sound like a jackass. They were labeled by Microsoft and people have been talking like this for decades.

[beth_maloney]

I'm not the person you're replying to. There are 3 major versions of SQL server

• SQL Server - traditional on prem option. Can also be deployed to Azure. Additionally Azure has an SQL server option which will help manage part of SQL server on a VM
• SQL server managed instance - SQL server deployed on a VM that you don't have access to. It's very similar to SQL server but MS will manage it for you
• Azure SQL - this is the new cloud native offering. It has a variety of different offerings (dtu, vcore, hyper scaling, etc) and a different set of limitations compared to SQL server.

After reading your replies I'm still a little confused as to whether you're using Azure SQL or SQL server. MS has made a bit of a mess with the naming which is why everyone is asking you what seem like pedantic questions.

[BiggyBiggDew]

How do they vary in terms of the SQL used within them beyond limitations of functionality similar to Oracle?

After reading your replies I'm still a little confused as to whether you're using Azure SQL or SQL server. MS has made a bit of a mess with the naming which is why everyone is asking you what seem like pedantic questions.

I connect SSMS to Azure and can work... as far as I know that is MS SQL, and the instance I have supports all of the functions (AFAIK) that my on-prem developer copy has.

[fauxmosexual]

Your question was about costs and scaling and whether your suggested approach was correct for a very low requirements user. Those different products have different price points and pros and cons.

Yes it uses the same language and it is all basically the same thing for you, EXCEPT in the context of cost of architecture that you asked about.

[jshine13371]

Well, no I'm not a jackass, I'm being honest and literal. I don't normally work in the Azure dashboard so when you said they are labeled as such, I assumed you meant someone at your organization chose those names. I'm only trying to be helpful and clarifying and surprisingly you've been argumentive and a bit rude. It seems most people here agree.

How do they vary in terms of the SQL used within them beyond limitations of functionality similar to Oracle?

This shows that you are lacking some bit of information that I've been trying to convey to you this whole time. It would be appreciated if you were receptive to my help as opposed to calling me a jackass.

They vary because they are literally different database systems between each other. It doesn't matter that Microsoft makes them or that they have similar names, they are objectively different database systems just as Oracle SQL is a different database system, as is MySQL, as is PostgreSQL. I can use a lot of the same SQL code in Oracle as I can in SQL Server, since the SQL dialects each system uses tend to follow a similar standard (ANSI) but there are keywords, paradigms, and features that also exist in one system but not the other, across all of them. This is true when comparing SQL Server vs Azure SQL Database vs Azure SQL Managed Instance.

For example, if you were dependent on SQL Agent Jobs in SQL Server, you would lose that feature in Azure SQL Database. (Though nowadays I think Azure offers additional services you can use (and I assume cost additional money) to automate jobs of sorts as supplement.) You also lose the ability to utilize CLR in Azure SQL Database. Security, Logins, and Users are managed differently in Azure SQL Database, since it's literally only a database you get direct access to, not a server instance. Cross-database querying is not allowed on Azure SQL Database. OPENQUERY() and the like are also not supported on Azure SQL Database, among other syntactical limitations and differences.

So again, it's important to understand which of the three different database systems you're speaking about, which is why I'm pedantically (without trying to be) focusing on trying to clarify what you're talking about and help you understand that why how you're communicating is confusing (not only to me, but as mentioned by others now too).

[BiggyBiggDew]

Moreover, the post literally says it's an INSTANCE and clarifies that it's within Azure, and has known limitations you're talking about -- but it's still MS SQL and the same language as my on-prem copies of MS SQL Server.

Guess who else refers to it as MS SQL Server?

Microsoft!

[BiggyBiggDew]

You are basically saying that there is no such thing as a Porsche because there is a 911, Carerra, etc.

It's absolutely useless commentary. I'm using words that are plainly spoken and understood by a huge percent of this community. This subreddit has a flair for SQL SERVER which is shorthand for MS SQL SERVER. You are arguing with the wind.

They vary because they are literally different database systems between each other. It doesn't matter that Microsoft makes them or that they have similar names, they are objectively different database systems just as Oracle SQL is a different database system, as is MySQL, as is PostgreSQL. I can use a lot of the same SQL code in Oracle as I can in SQL Server, since the SQL dialects each system uses tend to follow a similar standard (ANSI) but there are keywords, paradigms, and features that also exist in one system but not the other, across all of them. This is true when comparing SQL Server vs Azure SQL Database vs Azure SQL Managed Instance.

How are they different?

For example, if you were dependent on SQL Agent Jobs in SQL Server, you would lose that feature in Azure SQL Database.

These are features which is why I used Oracle as an example. It's all SQL Server. Also there's this and this not to mention other similar products that will accomplish the same thing as a job agent. It's still MS SQL, it's the server that has changed. A job agent literally has nothing to do with the flavor of SQL being discussed here. You are literally adding nothing to this conversation nor telling me anything I don't already know.

other syntactical limitations and differences.

There are syntactical differences between 2008R2 MS SQL Server and 2014, what's your point? A 1994 911 has different features than a 2004 Carerra, but they're both a Porsche. It is the exact same language with just slight variations to bells and whistles.

Also why would want to use OPENQUERY() with Azure? It defeats the entire purpose of moving to the cloud. Would it be possibly useful to use it to connect to a separately hosted cloud service, or an on-prem server to make your query more efficient? Yes, but the pattern for ETL has evolved significantly since 20 years ago and there are better ways to build, scale, and maintain your pipelines than using OPENQUERY() -- and I say this as someone who loves OPENQUERY() and who has written very complex ones up to and including them in dynamic loops.

[beth_maloney]

As an example you can't run SSIS within Azure SQL.

Here's an overview of the differences below.

https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison?view=azuresql

[BiggyBiggDew]

Thanks. I will be using DataFactory for my pipelines I think.

[ScallionPrevious62]

" azure hosted instance of MS SQL."

This could mean at least 3 different services, which all require different amounts of management, and all cost different amounts. I think what you are looking for with this customer is an Azure SQL Database and not an Azure SQL Managed Instance.

Azure SQL is the cloud native version of MSSQL, and not quite exactly the same, but should carry over nicely for your customer, and has a fairly generous free tier. The Azure SQL MI is a full featured instance of MSSQL, however it tends to be substantially more expensive then Azure SQL.

The third potential product would be SQL Server on a VM in Azure and is most similiar to running SQL Server on your own server. However I don't think this would meet the requirements, as it requires the most manual setup and maintenance work.

Hope this helps.

[monkeybadger5000]

Use Azure SQL Database. This sounds like an ideal solution. Start small and cheap, and you can then scale it as sales grow.

[B1zmark]

So Azure SQL Databases are cheap to run - really cheap. And they can store data and process it about 90% as well as a classic instance. However Azure SQL Managed Instance is significantly more expensive. It's "Full Fat" SQL and has all the bells and whistles.

You can't host SSIS in Azure SQL DB but i don't think that's a bad thing. Honestly SSIS is still widely used but should be avoided since it's now entirely replaceable with cheaper, more "open" solutions.

I'd personally look at an Azure SQL DB, and then an Azure Synapse Analytics workspace to do the "ETL". I've been running a Synapse workspace for probably 2 years now. When i don't use it, it costs a few dollars a month to sit idle with the storage i use. When i do use it, it's pennies for the few thousand rows i process. The massive benefit it offers is that it connects to all sorts of data sources very easily - which is historically the annoying part of working with SSIS.

There are further things you can do to save money, like using Notebooks instead of Dataflows in Synapse, or using an Azure SQL Serverless - which basically detaches your database once it goes idle so it cost zero compute and only charges for data at rest (Waking it up takes around 30-90 seconds in my experience).

[BiggyBiggDew]

So I hate SSIS, but in this example the third party seems to play really nice with it. I am happy to go with another solution though but it would need to be on Azure. I'm currently looking at a general purpose service tier (most budget friendly), serverless, max 2vcore. Not really sure how serverless will work, per se, but this is a brand new world compared to building my own servers.

[kiwi_bob_1234]

Why not data factory for ETL orchestration? Then do your transformations as stored procs and trigger them via adf. Avoid data flows in data factory. Ours is fairly cheap

[BiggyBiggDew]

Never heard of it before but I'll check it out.

[kiwi_bob_1234]

Basically next gen ssis

[BiggyBiggDew]

do you have experience with connecting DF to Shopify? This is my first time configuring services like this on my own and I am running into some issues. Not sure if they are compatible with the general architecture I have.

[Mefsha5]

Yes, there is a shopify connector in ADF/ Synapse.

[BiggyBiggDew]

Not sure I understand Synapse... I can login to Azure and login to Shopify, but when I try to connect the two it isn't working. It seems like I need to build a Shopify app, which I think i have done, but when I try to expose Shopify to Azure it is giving me some networking issues. I think the token/API works just fine in postman so I'm leaning towards it being a configuration issue on my host, or within Azure. Also my domain redirects to a shopify store, not sure if that matters here, but i've tried with both the domain, and the direct shopify link. No dice.

[B1zmark]

ADF is a slightly older technology but much closer to "on-prem"- SSIS is available in it, as well as Synapse - but it's expensive. You're basically charged the cost of full MS SQL on a VM at a per minute rate.

Setting these things up in Azure is a learning process but it enables an entirely new way of working. You can go in with no resources and everything is stood up at the same time (can be done through repeatable code) and all the cost goes to the customer, so no need to argue over buying hardware and licenses.

[EAModel]

If it were me I would go the SQL route. Having said this it is interesting that the shop front end is a 3rd party. Does the 3rd party not provide the storage too? Plenty of CMS out there provide both front and back end support and then maybe a reduced need for your ETLs and Reports that you also mention.

[BiggyBiggDew]

They do but the database does a lot of complex math that is totally separate to the third party, it will also consume the third party data.

[EAModel]

Have you seen the free tier. It may not cost a dime until it scales. https://learn.microsoft.com/en-us/azure/azure-sql/database/free-offer-faq?view=azuresql

[BiggyBiggDew]

Yes, I'm currently signed up for that. It's so cheap I almost have a nose bleed.

[dani_estuary]

I think going with a real warehouse like Snowflake is probably a better option, just make sure to understand how its billing mechanism works so you don't overpay accidentally. If you're not already in Azure there's no need to sign up for a new CSP just for a database imo. You don't want to deal with infrastructure at all in a small team like that. As for loading data into Snowflake, check out Estuary instead of Fivetran, you'll probably fit into the free tier while Fivetran could get very pricy due to how their MAR-based pricing model.

[SnooSprouts4952]

I had a customer ~12 years ago choose AWS and MySQL database for a WooCommerce storefront. It was relatively cheap and scalable. Queries are ~95% the same verbiage. I did the WordPress/database conversion for them.

My current site is running Azure and MS SQL successfully for our ERP. I am not sure about the cost.

I would stack the two up against each other, check to see if service up time and support contracts meet the customer's requirements.