Creating Sample VMs on my laptop

I have a laptop with two internal disks, SSD and HDD and also three external HDDs. As a database professional, I have to experiment my performance tuning scripts on every SQL Server versions after 2000. Moreover, I need a playground to assess all new features which installing many of them needs more than one machine, usually on Windows Server and rarely on Linux. It’s clear that I have to use virtual machines. I always love vmware workstation. This software offers more than other vendors. I do not want to advertise for Microsoft, just because I love SQL Server. BTW, I always use vmware workstation. On my laptop I have installed Windows 10 pro as a host OS which is great to run vmware VMs on top of it. I usually have a minimal software installed on my laptop OS which helps me a lot in running more VMs simultaneously. The reason is the RAM, I have 16GB RAM on my laptop and I have to use it minimally on my laptop OS. Nevertheless, I had some issues with windows 10 automatic updates which suddenly restart my opened Vms. So, I disabled the windows update service. Now, I can update my laptop OS periodically.

On the other side, using VMs helps me a lot when changing the laptop. I can save a lot of time, I just need to install a minimal software and copy my VMs to its fast SSD and go back to productivity!

Since for performance tuning I usually use a custom database that I load 200 GB of raw data into it, alongside with stackoverflow sample database which is about 130 GB, I cannot put such massive data on each version of SQL Server on separate VMs on my SSD! So, I have to use HDDs to backup from VMs and put it on HDD, and whenever I need it, I can copy the specific VM from HDD to SSD. For copying a VM in vmware all I have to do is using “clone vm” feature.

I have a VM bank folder I named it Model, something like the Model database in SQL Server. I have two phases to update this VM bank:

  1. New Operating System
    1. Install a new OS on new VM on SSD
    2. Perform sysprep on the new VM
    3. create a backup from new VM to Model folder (HDD)
  2. Create a Sample VM
    1. create a copy from existing OS within Model folder (HDD) to target SSD
    2. install all needed software on the new VM
    3. create a backup from new VM to Model folder (HDD)

If you want to see one of my sample vmware lab setup (with very detailed information on vmware setup), please visit SQL Server 2014: Step by Step Guide to Setup a Failover Cluster Virtual Lab

Advertisements

PERSIST_SAMPLE_PERCENT

ON

The statistics will retain the set sampling percentage for subsequent updates that do not explicitly specify a sampling percentage.

OFF

The statistics sampling percentage will get reset to default sampling in subsequent updates that do not explicitly specify a sampling percentage.

Source: UPDATE STATISTICS (Transact-SQL) | Microsoft Docs

This keyword is added to T-SQL from SQL Server 2016 SP1 CU4.

Perdro Lopez (Microsoft Team) explained it deeply here .

All important commands you need to configure a Windows Server Core and install SQL Server on it

If you have to perform a core mode SQL Server installation, don’t be afraid, just know these commands:

  • create share folder :
net share "share folder"=c:\share /grant:everyone,full
  • restart server :
shutdown -r -t 0
  • configure server 2012+ :
sconfig.cmd
  • rename computer :
netdom renamecomputer %computername% /newname:Svr-1
  • set static ip address :
netsh interface ipv4 set address "Local Area Connection" static 10.10.10.10
  • join to domain :
netdom join Svr-1 /domain:YourDomain.com
  • configure time zone :
Control timedate.cpl
  • activate windows :
slmgr –ato
  • set the password to never expire (lab only) :
wmic useraccount where "Name='Administrator'" set PasswordExpires=false
  • system preparation tool :
%windir%\system32\sysprep\sysprep.exe /oobe /generalize /shutdown
  • enable remote desktop (%windir%\System32>) :
cscript scregedit.wsf /ar 0
  • disable remote desktop (%windir%\System32>) :
cscript scregedit.wsf /ar 1
  • enable the firewall to allow the remote desktop (%windir%\System32>) :
netsh advfirewall firewall set rule group="remote desktop" new enable=Yes
  • enable .net 3.5 on server 2012+ (powershell) :
Install-WindowsFeature NET-Framework-Core –Source D:\Sources\SxS\
  • install SQL Server Database Engine only :
Setup.exe /qs /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS

Configure Windows Server Core with Sconfig.cmd

If you are going to install SQL Server on Windows Server 2012+ Core edition, you can use sconfig.cmd to configure Windows server core easily.

Starting with Windows Server 2012, you can use the Server Configuration tool (Sconfig.cmd) to configure and manage several common aspects of Server Core installations. You must be a member of the Administrators group to use the tool.Sconfig.cmd is available in the Minimal Server Interface and in Server with a GUI mode.

To start the Server Configuration ToolChange to the system drive.

Type Sconfig.cmd, and then press ENTER.

The Server Configuration tool interface opens:

Source: Configure a Server Core Server with Sconfig.cmd

Adaptive query processing in SQL Server 2017

The batch mode adaptive joins feature enables the choice of a hash join or nested loop join method to be deferred until after the first input has been scanned. The adaptive join operator defines a threshold that is used to decide when to switch to a nested loop plan. Your plan can therefore dynamically switch to a better join strategy during execution. Here’s how it works:

If the row count of the build join input is small enough that a nested loop join would be more optimal than a hash join, your plan switches to a nested loop algorithm.

If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a hash join.

Source: Adaptive query processing in Microsoft SQL databases | Microsoft Docs

What is the SSRS web portal  

The web portal is a web application that you open by typing the web portal URL in the address bar of the browser window. When you start the web portal, the pages, links and options that you see will vary based on the permissions you have on the report server. To perform a task, you must be assigned to a role that includes the task. A user who is assigned to a role that has full permissions has access to the complete set of application menus and pages available for managing a report server. A user assigned to a role that has permissions to view and run reports sees only the menus and pages that support those activities. Each user can have different role assignments for different report servers, or even for the various reports and folders that are stored on a single report server.For more information about roles, see Granting Permissions on a Native Mode Report Server.

Source: Web portal (SSRS Native Mode) | Microsoft Docs