Good day, i need help please! I have tried reaching out to vendors but they more keen on having their solutions within the businnes as i require infrastructure, software specifications for the next 5-7 years with improvong the analytics & speed & reliability of reporting I'm on the spotlight as my BI Space is struggling and have received some harsh words :( The SMP setup does Paralism Poorly, with the hige demand for faster im forced to run sequentially. I am tasked with word class and will be backed, however limitaions are we cant go Aure and needs to be VM server Please i dont know, and only listing thoughts: 1. I need to know what is the best infrastructure sepcifications to boost performance possibly 200% MPP PDW, is that the latest can it be setup on VMWare OS, windows 19 2. Software Recommendations, i'm hoping for SQL21 (sql16) but service packs VS which version from VS 2013 for SSDT 3. Analytics / Reporting Should i setup sepearte VM for Tabular & OLAP cubes, is it needed if i setup better infrastructure We use a little cloud but i need to bridge gap from Excel presentations to Powerbi, Finance love excel but other Bus units says we not evolving. Is their excel on steriods or olap in Web, something more to assist 4. Innovation What more than excel cubes can BI provide without Azure, users dont like pbi mobile andpbi is used sparingly within the organization. MDS is one aspet i will intriduce next year but i need bigger as other thans speed they want to see more. 5. Current Infrastructure Whats a quick fix to the below to get me thru to the next 3 months of upgrade, more CPU/Ram etc I urgently need to do step1 "Infrastructure" first as the area is not performing and then want upgrade tools therafter Operating System Windows 12 64 BIT Data Storage 2TB + Log Drive (200gb) + Backup Drive (500gb) Cores & Processors 2 Physical Cores/Proc with 4 Virtual Processors Each (8 PROCESSORS) Disk Space Available 50GB RAM 64GB Data Warehouse Microsoft SQL 2014 Enterprise SMP Data Manipulation SQL DB Engine SQL Integration Services / PowerBI Gateway Data Presentation SQL Analyses Services / SQL Analyses Services Tabular / Excel / O365 Data Visualization PowerBI Pro Development Visual Studio 2013 SSDT Version Control Team Foundation Services Hosted SharePoint, SQL SSIS Packages >100, SQL Jobs >70, SSAS Cubes >25, SSAS Cube Users >450, PBI Dashboards>10 Sorry i know its a lot, really would i appreicate clear guidance becuase if i get this wong :( The expectations are very high, want innovation, new cubes presentation, faster data feeds (realtime), finance jobs to run from 1h ours to 15min Thank You

Hi @IC IC , Data Processing occurs on Presentation, even though it reads the Storage VM? Yes. It can. Which OS, Windows 19? I use Windows 16 standard, they are works well, you can also use Windows 19, no problem. Specific powerfull VM? Your configruation is excellent:120 processor, 5T disk , RAM 1T. Can i use VS2019 Communitry with SSDT only or do i need VS19 Developer? You can use community version. SQL21 released?, do i rather wait until a service pack is available No. You do not need, you can use the current latest sp and not need to wait. what are the latest software for VS & SQL? SQL: latest-updates-for-microsoft-sql-server VS: downloads BR, Mia If the answer is helpful, please click "Accept Answer" and upvote it.

HI, Thank You so much for Assisting. SSMS, SSIS, SSAS, SSAS TAB, PowerBI is on 1 VM server (windows 12) SQL 14 Enterprise No Sahrepoint & SSRS has seperate servers, i meant to say the cubes are published and shared on a Sharepoint Site I have tried so many options on Max degree of paralism to 8 with queries 50 Only 1 SQL server engine Point1, is of great urgency because my dept head wants to have a plan for hardware on Monday. I'm not sure of MPP/PDW on VM with speifications as i would like to do AI and better reporting going forward management wants a quick fix, but that might not be great for the big upgrade i want to do whch will be perforncance and reporting by 200% with new tools, then hardware might not be good enough. They willing to back me and want world class now, so i want to present the best out their that will cover me for anything going forward Please help,

Data Drive: 2TB with 50GB available, 1 main DB of 1.9tb Log Drive 200GB with 100GB available Backup 300gb with 40 GB available 3 Sql DB and 30 OLAP Databases My infrastructure manager keeps on telling me i cant throw resources at the issue, i have requested an increase but kept saying no its not my problem and wants a vendor to give more proffessional instructions SQL19 with Windows19 is part of my upgrade as i cant upgrade right now hence temp win on the resources, but looking at the SQL 21 (16) which VS SSDT is required? How many RAM & CPU can i request for the temp fix? Is 512 or 1TB RAM too much or more? Thank You

Hi @IC IC , I need to know what is the best infrastructure sepcifications to boost performance possibly 200% Firstly, if you can, set SQL Engine in one server and set SSAS SSRS and PowerBI in another server, one is for Data Store, one is for data process and presentation. However, I am not sure whether it can improve 200% preformance. Software Recommendations, Why not use the latest SP and VS and SSDT? 3.Should i setup sepearte VM for Tabular & OLAP cubes, is it needed if i setup better infrastructure You should not. However, it's better for one server for data processing. Different departments love different presentatin tools, this is not conflication, you can only suppy the data source, and then they can use their loved tools to present data and doing analysis. Innovation If they don't like PBI, you can try other tools, such as SSRS , QLIK ... 5.Current Infrastructure If you don't familiar with that, recommend you consult one IT constant team to help you doing upgrade all your IT insfratructures and softwares. Maybe you can ask professional engineers for help, and they will deal with your problem separately and confidentially: https://support.microsoft.com/en-us/assistedsupportproducts BR, Mia If the answer is helpful, please click "Accept Answer" and upvote it.

Thank You DirkHondong & Mia, My biggest tables (20) ranges from 200m to 400m My list for upgrade below, Please advise on each point 1. Infrastructure, Need to simulate as close to a MPP/PDW using VMware 2 VM: 1 for DB Engine (Storage) & SSIS (ETL) 1 for SSAS, Tab, PBI (Presentation) > Data Processing occurs on Presentation, even though it reads the Storage VM? Which OS, Windows 19? Processors / CPU / Core for each VM, 32/64/120 Processors ? CPU Architecture / Cache? Disk Space from 2TB to 5TB RAM 1TB Specific powerfull VM? 2. Software Recommendations Can i use VS2019 Communitry with SSDT only or do i need VS19 Developer? SQL21 released?, do i rather wait until a service pack is available What are the latest software for VS & SQL? 3. Innovation Looking to Bridge gap between Excel and PowerBI Going to be using MDS in the future but need a bit more, like a Forensic Tool where users can list a rule and output is generated based on any source plugged in Regards

SQL14 Migration, Infrastructure, Software Options

IC IC 236

Good day, i need help please!

I have tried reaching out to vendors but they more keen on having their solutions within the businnes as i require infrastructure, software specifications for the next 5-7 years with improvong the analytics & speed & reliability of reporting

I'm on the spotlight as my BI Space is struggling and have received some harsh words :(
The SMP setup does Paralism Poorly, with the hige demand for faster im forced to run sequentially.
I am tasked with word class and will be backed, however limitaions are we cant go Aure and needs to be VM server

Please i dont know, and only listing thoughts:

1. I need to know what is the best infrastructure sepcifications to boost performance possibly 200%

MPP PDW, is that the latest
can it be setup on VMWare
OS, windows 19

2. Software Recommendations,

i'm hoping for SQL21 (sql16) but service packs
VS which version from VS 2013 for SSDT

3. Analytics / Reporting

Should i setup sepearte VM for Tabular & OLAP cubes, is it needed if i setup better infrastructure
We use a little cloud but i need to bridge gap from Excel presentations to Powerbi, Finance love excel but other Bus units says we not evolving. Is their excel on steriods or olap in Web, something more to assist

4. Innovation

What more than excel cubes can BI provide without Azure, users dont like pbi mobile andpbi is used sparingly within the organization. MDS is one aspet i will intriduce next year but i need bigger as other thans speed they want to see more.

5. Current Infrastructure

Whats a quick fix to the below to get me thru to the next 3 months of upgrade, more CPU/Ram etc

I urgently need to do step1 "Infrastructure" first as the area is not performing and then want upgrade tools therafter

Operating System Windows 12 64 BIT
Data Storage 2TB + Log Drive (200gb) + Backup Drive (500gb)
Cores & Processors 2 Physical Cores/Proc with 4 Virtual Processors Each (8 PROCESSORS)
Disk Space Available 50GB
RAM 64GB
Data Warehouse Microsoft SQL 2014 Enterprise SMP
Data Manipulation SQL DB Engine SQL Integration Services / PowerBI Gateway
Data Presentation SQL Analyses Services / SQL Analyses Services Tabular / Excel / O365
Data Visualization PowerBI Pro
Development Visual Studio 2013 SSDT
Version Control Team Foundation Services
Hosted SharePoint, SQL SSIS Packages >100, SQL Jobs >70, SSAS Cubes >25, SSAS Cube Users >450, PBI Dashboards>10

Sorry i know its a lot, really would i appreicate clear guidance becuase if i get this wong :(
The expectations are very high, want innovation, new cubes presentation, faster data feeds (realtime), finance jobs to run from 1h ours to 15min

Thank You

Dirk Hondong 871 Reputation points

2020-11-14T10:59:46.517+00:00

that's quite a lot of stuff.
regarding your current infrastructure: is that all on one box?
Eg. you have only 64GB of RAM for this SQL Server with just 8 CPUs and you host Sharepoint DBs there as well as all your Cubes and Reports?

1st of all: Sharepoint has a completely different demand or expectation of how an SQL Server instance should be configured, e.g. MAXDOP = 1
If that is set for your one box wonder, then you'll never get parallelism at all except you put MAXDOP options in your queries.

So, is it only one SQL Srv engine or do you run multiple instances as well?

Accepted answer

m 4,271 Reputation points

2020-11-16T07:58:37.257+00:00

Hi @IC IC ,

Data Processing occurs on Presentation, even though it reads the Storage VM?

Yes. It can.

Which OS, Windows 19?

I use Windows 16 standard, they are works well, you can also use Windows 19, no problem.

Specific powerfull VM?

Your configruation is excellent:120 processor, 5T disk , RAM 1T.

Can i use VS2019 Communitry with SSDT only or do i need VS19 Developer?

You can use community version.

SQL21 released?, do i rather wait until a service pack is available

No. You do not need, you can use the current latest sp and not need to wait.

what are the latest software for VS & SQL?

SQL: latest-updates-for-microsoft-sql-server
VS:downloads

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

11 additional answers

IC IC 236 Reputation points

2020-11-14T11:53:13.287+00:00

HI, Thank You so much for Assisting.

SSMS, SSIS, SSAS, SSAS TAB, PowerBI is on 1 VM server (windows 12) SQL 14 Enterprise

No Sahrepoint & SSRS has seperate servers, i meant to say the cubes are published and shared on a Sharepoint Site

I have tried so many options on Max degree of paralism to 8 with queries 50

Only 1 SQL server engine

Point1, is of great urgency because my dept head wants to have a plan for hardware on Monday.
I'm not sure of MPP/PDW on VM with speifications as i would like to do AI and better reporting going forward
management wants a quick fix, but that might not be great for the big upgrade i want to do whch will be perforncance and reporting by 200% with new tools, then hardware might not be good enough.
They willing to back me and want world class now, so i want to present the best out their that will cover me for anything going forward

Please help,
Please sign in to rate this answer.
Dirk Hondong 871 Reputation points

2020-11-14T13:04:43.253+00:00

The thing is:
I cannot tell you if a PDW can or will solve all your problems.
A PDW is a setup where you have a control node and multiple compute nodes, so you will have multiple SQL Servers:
https://learn.microsoft.com/de-de/sql/analytics-platform-system/home-analytics-platform-system-aps-pdw?view=aps-pdw-2016-au7
Also: with SQL Server 2019 Big Data Cluster where introduced. So more options available for future proof/reade environments.

But one tip:
You spent a lot of money for SQL srv license and then have only 64GB in that box? Don't limit yourself there.
Put as much RAM into the SQL Server as possible. Also, jump on SQL Server 2019, get advantage of columnstore indices and so on

Since I have no idea how big your Datawarehouse DB(s) are, what queries are running etc
Just saying everyting needs to be 200% faster is difficult wihtout any metrics, baselines...

Dirk Hondong 871 Reputation points

2020-11-14T13:04:56.097+00:00

But with the few information available, eg the size of your data volume, log vol, backup it is really hard to give you a proper recommendation

But I would not go for a one box wonder any more, except you put enoguh ressources into that one VM.
Keep in mind: every service wants to have its ressources ond it is always best practise not to mix the different workloads.

But let me recap
you have just one SQL Server Installation
This is a Windows Server 2012 with 8vCPUs and 64GB RAM
On this machine you are running 1 SQL Srv egine, one SSAS engine, one SSAS TAB engine and also PowerBI?

Since you mentioned the size of your data and log volumes:
are only the DB file(s) in there or also all the olap stuff?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
IC IC 236 Reputation points

2020-11-14T13:43:03.88+00:00

Data Drive: 2TB with 50GB available, 1 main DB of 1.9tb
Log Drive 200GB with 100GB available
Backup 300gb with 40 GB available

3 Sql DB and 30 OLAP Databases
My infrastructure manager keeps on telling me i cant throw resources at the issue, i have requested an increase but kept saying no its not my problem and wants a vendor to give more proffessional instructions

SQL19 with Windows19 is part of my upgrade as i cant upgrade right now hence temp win on the resources, but looking at the SQL 21 (16)
which VS SSDT is required?
How many RAM & CPU can i request for the temp fix?
Is 512 or 1TB RAM too much or more?

Thank You
Please sign in to rate this answer.
Dirk Hondong 871 Reputation points

2020-11-15T11:22:34.923+00:00

Ok, question here is: how big is your biggest table in the 1.9TB database?
It is all just rough math, but if your main tabel for example gets queried every time to build up your OLAP cubes and this table has a size of, let's say 300GB, then SQL Server has to go all the way back to the storage to pull data since SQL Server is not able to cache the data in the buffer because of onyl 64GB RAM.

But again: I've no idea what kind of storage you have.
And you cannot through resources every time to a system if performance issues occur. That woulf not help on a long term.

Also: If you raise the number of CPUs then you will have additional licensing costs.

Also, just one example from my last work week to give you an idea that sometimes ressources aren't the solution for everything:

a colleague hat a problem with the runtime of a report. The queried tables where only 2 GB in size all together.

Dirk Hondong 871 Reputation points

2020-11-15T11:26:49.887+00:00

But report took ages, e.g. we cancelled it after 2 hours of runtime without any results returned.

Turned out it was weird programming.
Report got its information from a table valued function which relied on a table valued function and so on.

After I've switched the logic a bit and used a real temporary table instead of a TVF the whole report was processed in 20 seconds.

So, no additonal ressources needed or whatever.
It is the combination of so many factors. I could have add CPUs to the system or even assign more RAM to the VM
In that case it wouldn't get better.

So again: without having a closer look or further analysis throwing hardware at the problem can help, but may also help not. Don't expect too much from more CPUs and mroe RAM.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
m 4,271 Reputation points

2020-11-16T03:17:43.507+00:00
Hi @IC IC ,

I need to know what is the best infrastructure sepcifications to boost performance possibly 200%

Firstly, if you can, set SQL Engine in one server and set SSAS SSRS and PowerBI in another server, one is for Data Store, one is for data process and presentation. However, I am not sure whether it can improve 200% preformance.

Software Recommendations,

Why not use the latest SP and VS and SSDT?

3.Should i setup sepearte VM for Tabular & OLAP cubes, is it needed if i setup better infrastructure

You should not. However, it's better for one server for data processing.
Different departments love different presentatin tools, this is not conflication, you can only suppy the data source, and then they can use their loved tools to present data and doing analysis.

Innovation

If they don't like PBI, you can try other tools, such as SSRS, QLIK...

5.Current Infrastructure

If you don't familiar with that, recommend you consult one IT constant team to help you doing upgrade all your IT insfratructures and softwares.

Maybe you can ask professional engineers for help, and they will deal with your problem separately and confidentially:
https://support.microsoft.com/en-us/assistedsupportproducts

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
IC IC 236 Reputation points

2020-11-16T06:44:55.77+00:00
Thank You DirkHondong & Mia,
My biggest tables (20) ranges from 200m to 400m

My list for upgrade below, Please advise on each point

1. Infrastructure, Need to simulate as close to a MPP/PDW using VMware
2 VM:

1 for DB Engine (Storage) & SSIS (ETL)

1 for SSAS, Tab, PBI (Presentation) > Data Processing occurs on Presentation, even though it reads the Storage VM?

Which OS, Windows 19?

Processors / CPU / Core for each VM, 32/64/120 Processors ?

CPU Architecture / Cache?

Disk Space from 2TB to 5TB

RAM 1TB

Specific powerfull VM?

2. Software Recommendations

Can i use VS2019 Communitry with SSDT only or do i need VS19 Developer?

SQL21 released?, do i rather wait until a service pack is available

What are the latest software for VS & SQL?

3. Innovation

Looking to Bridge gap between Excel and PowerBI

Going to be using MDS in the future but need a bit more, like a Forensic Tool where users can list a rule and output is generated based on any source plugged in

Regards
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL14 Migration, Infrastructure, Software Options

11 additional answers

Your answer