SQL14 Migration, Infrastructure, Software Options

Question

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

Answer 1

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.

Answer 2

Thank You MIA,
This is for the upgrade to take me the following 5 - 7 years and want close to MPP / PDW

As the infrastructure will be done this year and software next year.

• Can i go higher (Processors/CPU. Ram, space etc), what are higher specs because Paralism is a huge issue, what is higher?
• Are their performance gains with Windows19?

Benefits of SQL21 and release, hoping it offers more options as we only doing this next year Feb/Mar?

So i dont really need VS, as VS Community for SSDT is free?

Please & Thanks

Answer 3

Does the 120 processors means 120 CPU processing Cores?

Answer 4

Hi @IC IC ,

Can i go higher (Processors/CPU. Ram, space etc), what are higher specs because Paralism is a huge issue, what is higher?

No specific. For configuration CPU :16 is ok.

Are their performance gains with Windows19?

Yes. More powerful configuration, more helpful.

So i dont really need VS, as VS Community for SSDT is free?

You can use SSDT for free.

Does the 120 processors means 120 CPU processing Cores?

Actually,you don't need too much more CPUs, though more cpus are better. However, 16 CPU is enough.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

ok so i shud go for 32/64 cpu, is 120 not better or an overdo?