SQL14 Migration, Infrastructure, Software Options

IC IC 236 Reputation points
2020-11-14T09:42:21.843+00:00

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

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
533 questions
{count} votes

Accepted answer
  1. 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.

    0 comments No comments

11 additional answers

Sort by: Most helpful
  1. 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,


  2. 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


  3. m 4,271 Reputation points
    2020-11-16T03:17:43.507+00:00

    Hi @IC IC ,

    1. 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.

    1. 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.

    1. 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.

    0 comments No comments

  4. 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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.