-1

We currently have a Microsoft SQL Server instance (oltp) we use as our transactional and reporting database. We want to pull out and create a separate database for reporting.

We are currently vetting Redshift and Snowflake. We came up with a question today which is why can't we create a new SQL Server instance for reporting which has the star schema and just use that (instead of redshift or snowflake)? We don't have many tables over a million rows. So maybe using a columnar data warehouse is over kill for us.

Does any know the pros and cons of using Microsoft SQL Server as a reporting database (data warehouse) with a star schema?

We also have a requirement to handle real time or near real time updates.

johnsontroye
  • 281
  • 1
  • 3
  • 20
  • 1
    A big pro of using sql server for the warehouse is that you get SSAS and SSRS included in the same license and they all integrate nicely with the Active Directory (which you presumably already have). A big pro of Snowflake is the cost model where you only pay when you use it. Enabling you to scale up and down on demand. – Peter Henell Apr 19 '18 at 20:51
  • 1
    Why not consider a free open source dbms for the warehouse? In general, these kinds of questions are to broad to be answered here. – Peter Henell Apr 19 '18 at 20:53
  • Thank you Peter. I'm glad you mentioned the SSAS aspect of it. We do use Cognos for reporting but that has no bearing really on this decision. I'm curious how SQL Server used as a realtime or near real time data warehouse will perform. That is a requirement also. – johnsontroye Apr 19 '18 at 21:08

1 Answers1

1

You can use SQL Server as a data warehouse repository. As long as you have a well designed star schema there is no reason not to use it for that purpose.

demircioglu
  • 3,069
  • 1
  • 15
  • 22
  • Then where does the advantage of using redshift or snowflake come in? Is it in the volume of data being stored only or are there other advantages? Is it in the concurrency? – johnsontroye Apr 19 '18 at 19:55
  • Snowflake (or Netezza etc) are purpose built for data warehouses. Redshift is Amazon's offering if you like to host your DW in the cloud. If you already have the followings in place I would say use SQL server : SQL Server licenses bought already, in house know how, data not in 10 TB+ – demircioglu Apr 19 '18 at 19:57