What's new in SQL Server 2022 (16.x)

SQL Server 2022 (16.x) builds on previous releases to grow SQL Server as a platform that gives you choices of development languages, data types, on-premises or cloud environments, and operating systems.

https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16

Feature highlights

Administrative:

Azure Synapse Link for SQL:  near real-time analytics With integration between SQL Server 2022 (16.x) and Azure Synapse Analytics dedicated SQL pools, Azure Synapse Link for SQL enables you to run analytics scenarios on your operational data with minimum impact on source databases.

Object storage integration:  introduces new object storage integration with S3-compatible object storage, in addition to Azure Storage. The first is backup to URL and the second is Data Lake Virtualization.

Link to Azure SQL Managed Instance: Connect your SQL Server instance to Azure SQL Managed Instance.

Contained availability group: Create an Always On availability group that:

        - Manages its own metadata objects (users, logins, permissions, SQL Agent jobs etc.) at the availability                     group level in addition to the instance level.
        - Includes specialized contained system databases within the availability group.

Distributed availability group: - Now using multiple TCP connections for better network bandwidth utilization across a remote link with long tcp latencies.

Improved backup metadata: system table returns last valid restore time.

Azure Active Directory authentication: Use Azure Active Directory (Azure AD) authentication to connect to SQL Server.

Always encrypted with secure enclaves: Support for JOIN, GROUP BY, and ORDER BY, and for text columns using UTF-8 collations in confidential queries using enclaves.

Query Store on secondary replicas: Query Store on secondary replicas enables the same Query Store functionality on secondary replica workloads that is available for primary replicas. 

Manage Azure extension for SQL Server:Use SQL Server Configuration Manager to manage Azure extension for SQL Server service. 

Shrink database WAIT_AT_LOW_PRIORITY:In previous versions, shrinking databases and database files to reclaim space often leads to concurrency issues. SQL Server 2022 adds WAIT_AT_LOW_PRIORITY as an additional option for shrink operations (DBCC SHRINKDATABASE and DBCC SHRINKFILE). 

When you specify WAIT_AT_LOW_PRIORITY, new queries requiring Sch-S or Sch-M locks aren't blocked by the waiting shrink operation, until the shrink operation stops waiting and begins executing

Developers:

CREATE INDEX

WAIT_AT_LOW_PRIORITY with online index operations clause added.

SELECT ... WINDOW clause

Determines the partitioning and ordering of a rowset before the window function, which uses the window in OVER clause is applied. See SELECT - WINDOW.

IS [NOT] DISTINCT FROM	Determines whether two expressions when compared with each other evaluate to NULL, and guarantees a true or false value as the result. For more information, see IS [NOT] DISTINCT FROM (Transact-SQL).
Time series functions	You can store and analyze data that changes over time, using time-windowing, aggregation, and filtering capabilities. - DATE_BUCKET () - GENERATE_SERIES () The following adds support to IGNORE NULLS and RESPECT NULLS: - FIRST_VALUE () - LAST_VALUE ()
JSON functions	- ISJSON () - JSON_PATH_EXISTS () - JSON_OBJECT () - JSON_ARRAY ()
Aggregate functions	- APPROX_PERCENTILE_CONT () - APPROX_PERCENTILE_DISC ()
T-SQL functions	- GREATEST () - LEAST () - STRING_SPLIT () - DATETRUNC () - LTRIM () - RTRIM () - TRIM ()
Bit manipulation functions	- LEFT_SHIFT () - RIGHT_SHIFT () - BIT_COUNT () - GET_BIT () - SET_BIT ()

MS Ignite 2018 wrap up

Microsoft has started rolling updates to all platforms after wrapping up Ignite in Orlando last week. Updates from Microsoft Ignite 2018 and links to all the session videos and slide decks. There were announcements across all the Microsoft platforms, but some stood out more than others. Check recording for session and presentation at link below

MS Ignite 2018 Session Recording and Presentations

https://myignite.techcommunity.microsoft.com/sessions 

Open Data Initiative: Microsoft, Adobe and SAP entered into an open data partnership at Ignite. Watch Staya's keynote for details 

https://www.youtube.com/watch?v=48Kjm4brpZo&feature=youtu.be 

Power Platform: Big focus on PowerApps with supporting technologies Flow and PowerBI. Recording and slides available for 50+ sessions at the link below.

MS Teams as a fastest growing product in Microsoft history, many recorded videos and presentation available to explain these new features.

SharePoint also saw its share of announcements including the availability of hub sites which will allow you to easily pull together – with consistent navigation – all the SharePoint Team Sites you have in Office 365.

New updates to SharePoint hub sites will support flexible information architecture approach for the organisation. Content and Collaboration keynote: by Jeff Tepper

Azure Digital Twins  Microsoft has announced the ability to create digital twins.  A digital twin is a virtual representation of physical systems which can then take in new information or datasets to test and probe the system's capabilities.

and a lot more….

SharePoint & Power Apps - Few Ideas/Suggestions for product team

I have noticed a few trends/things in the last couple of years and this came as a theme in recent Ignite conference as well so I thought I should share key pointers with you. Big focus on Teams and Comms site which is great but SharePoint is much bigger than this and I haven't heard much on broader topics especially following topics which deserve some attention. 

SharePoint

• Many organizations/Govt are using SharePoint as Document and Record management system and it is a platform of choice for an organization who wants to move from file share or other DMS/Record management system to SharePoint but not much guidance available from Microsoft in modern templates to support these things (except conventional way). I have discussed this with few folks from MSFT IT Services and SharePoint product team but no clear direction available from them. 
• Consistent user experience in Onedrive and new features are great but the story is getting convoluted for OneDrive V/S SharePoint document library especially on Mobile Devices where users have (One Drive V/S SharePoint App). Many whitepaper and options available by partners on when to use what but a clear message from the product team in this regard will be helpful in shaping up better solutions I and improve adoption. 
• Microsoft best practice suggests using metadata and content types to structure content in document libraries but site created with MS Teams use a folder based structure which is confusing for users. 
• If an organization is using Teams and SharePoint based intranet and DMS how they should have a well-connected experience? Having a tab for the site inside Team is usually not good enough. I think having a flow connector which can add a newly created site under (teams) to a hub site could be one way to integrate that experience. 
• The idea of Hub site is great but organizations who have recently implemented intranet structure based on subsite structure, Microsoft should provide some guidance and roadmap for transition to new constructs. 
• Not much clarity or direction available from Microsoft for public-facing portals after this feature is deprecated in O365, partners like site core and other have a solution in this area but will be good to see Microsoft offering in this space.
• Search is a big thing for the end user and no doubt this has improved a lot in the last couple of years but many people in community/groups are not much aware of configurations and options available in SharePoint search beyond configuring few basic settings and not 
• As much as I love SPFx and flexibility and power it offers for techs but I think from an end-user perspective they really don't care much if a yammer web part is loaded using a traditional approach or using the modern web part and same goes on for other constructs so a bit more 

PowerApps:

• Doesn't offer responsive experience, it has to be built either for mobile or tablet view which is not great. 
• The story for model-driven V/S Canvas app with multiple types of licensing and inconsistent experience is confusing for the user as canvas app run on PowerApp in device and model-driven runs on CRM app and many other differences. 
• Technically model driven is a light touch version of CRM so Microsoft should consider a new type of D365 license to make it easier for them to understand and use it. 
• Access for users outside the organization (users who don't have an AD account) is limiting factor and limit many scenarios for contractors/field related staff. Microsoft can consider perhaps something similar to CRM portal licenses for users outside the organization or have at least anonymous access with a fixed fee paid by an organization who host that app. 
•  Recent whitepaper and updates are good about governance and analytics but still much opportunity available to handle dev ops for power platform.  
• Currently, available templates/examples are good but it will be good if these templates get updated with some more business savvy apps and perhaps an area can be created for allowing partners to publish apps and Microsoft can approve it before they are added to the gallery.

SQL Server on Linux step 3: Linux (Ubuntu ) Installing SQL SERVER vNext

In my previous post we learn how to install Linux through Oracle Vitual box that you can found from following link

Sql-server-on-linux-step-2-linux-ubuntu.html

After finishing your Ubuntu installation, you are ready to install SQL Server. You can install it through Ubuntu or your host windows using command line. Installing from Windows you need to install putty easily available on internet. Install it and connect it using Network IP and run all steps mentioned below. 

I am installing it through Ubuntu plate form and following are the steps for that

1-      Execute “sudo apt-get update”

2-      Execute “sudo apt-get upgrade” is an optional step

3-      Run curl command to import GPG key. GPG keys also provide cryptographic privacy and authentication. If curl command is not installed on this machine so we need to run the suggested command to install the curl command first.

4-      After successful installation of curl, we can import key using following command and add it using sudo command.

“curl https://packages.microsoft.com/key/microsoft.asc | sudo apt-key add –“

5-      Due to some network and internet problem I was unable to register key directly from Microsoft site. So if key download fails download key from mentioned path. https://packages.microsoft.com/key/microsoft.asc you can find it in downloads, Open the file, Save it as .asc file and open it in terminal

6-      From the terminal windows register key from the following command

“ sudo apt-key add microsoft.asc”

7-      After successfully registering GPG key download mssql server from following path.

“curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list”

8-      You can check the folder and rights to the downloaded file by executing ls -lrt

“ls –lrt /etc/apt/sources.list.d”

9-      After executing all run “sudo apt-get update” before installing SQL Server.

10-   Now you are ready to install SQL Server now. Start SQL Server installation using following command.

“sudo apt-get install –y mssql-server”

11-   Installation will continue as we can see installation percentage. It will take some time depending on internet connection. Installation will complete with the config setup message.

12-   As mentioned in the message, To complete SQL Server setup run following command

“sudo opt/mssql/bin/mssql-conf setup”

It will prompt license agreement message.

13-   After completing above step Microsoft will ask for administrator password and will start SQL Server. During my installation I set password as “vSqlserver2017”. In the end you will get Setup completed successfully message.

14-   You have successfully completed MSSQL Server installation. On restarting you linux server, you can check SQL Server status by executing following command.

15-   We need to Install SQL Server tools by using following command. Using wget we need to download and copy prod.list on sources.list.d path.

16-   On getting permission message I am unable to copy on given path. I used same path as I did to register key for SQL Server.

17-   I downloaded prod.list file and copy it to sources.list.d

18-   Opening root as file manager by entering following command

“sudo nautilus”

19-   Following root path will open

20-   Reaching at sources.list.d path copyà paste prod.list as follows

21-   It is recommended to run get-update command before and after any installation and then start installing tools by following command

“sudo apt-get install mssql-tools –y”

22-   Installation will start once all required data will be downloaded.

After completing installation we are going to perform basic SQL Server queries using linux Ubuntu command prompt.

23-   SQL Server is active and running. You can connect SQL Server using following command “sqlcmd –S (Server name) –U (username) –P (Password)”

24-   Performing basic SQL Server testing through command prompt.

a.       Create  database

b.      Create Table

c.       Insert data

d.      Query data

SQL Server on Linux step 2: Linux (Ubuntu ) installation using Virtual Box VM

In my previous post we learn how we can get free Virtual machine to install another operating system on our existing windows. We downloaded Oracle virtual Box Virtual Machine and decided to install Linux plate form on it. We downloaded Linux flavor Ubuntu 17.10 64 bit Server .iso image from given link and now we will learn how to install Ubuntu on Virtual Box VM. You can find previous post under following link.

Installing Oracle Virtual Box on Microsoft Windows

Linux Ubuntu 17.10 installation on Virtual Box VM

1-      From your VM box right click -> on your VM and press “Start” or “Normal Start”.

2-      This will start Ubuntu setup. Load downloaded .iso file and press Start

3-      Setup will load Ubuntu from .iso file. Press Install ubuntu

4-      For installation of new operating system, either select erase and install Ubuntu or manual setup. As I am installing on a separate partitioned drive, I select Erase disk and install Ubuntu.

5-      You will get partition formation message. Press Continue

6-      You need to select time zone settings

7-      Press language for your keyboard.

8-      You need to create username and password. Remember password or write it somewhere for future use. I set password is same as username.

9-      You will setup complete message and need to restart setup.

10-   There are some settings needs to change in order to get better performance from installed virtual machine. Open Ubuntu VM settings. Move to System tab and base memory option under Motherboard.

11-   Switch to Processor tab and change your processor setting.

12-   In left side tab go to Network under Adapter 1 tab.

a.       Enable Network Adapter and select Bridged Adapter to select same network that is on your Windows system.

b.      Under Name: Select Network of wireless setting as required.

c.       Promiscuous Mode: Select Allow All.

d.      Tick Cable connected to work with Bridged Adapter.

13-   Setup your network setting as you setup in your windows machine.

a.       Start your Ubuntu VM and on top right corner. Open setting press Network.

b.      Under Network Proxy press proxy server to connect network server.

c.       Press Apply system wide and restart Ubuntu to apply new changes.

14-   Install putty on your windows system and you can use command prompt to connect Ubuntu from outside Ubuntu.

15-   Starting Linux (Ubuntu). To open root command Press Ctrl+Alt+T to open a terminal window. For security purposes (and to avoid damage), the root user account is locked by default. To safely run commands as root, you should use sudo instead. Enabling the root user can put your system at risk and is not recommended by Ubuntu.

16-   You can use Sudo apt-get update as follows

SQL Server on Linux step 1: Installing Oracle Virtual Box on Microsoft Windows

As Microsoft announces next version of SQL server that is ready to install on other plate forms then Windows. SQL Server 2017 CTP 2.0 is supported on Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu. It is also available as a Docker image which can run on Docker Engine on Linux or Docker for Windows/Mac. Question is how to install Linux and SQL Server on it. I am going to show you step by step guide to install both Linux and SQL Server vNext. I tried to cover all aspects as much as possible in both command line and graphically.

SQL Server Latest Releases on Linux:

The next version of SQL Server on Linux will include all the standard SQL Server management features that come with the Windows version. These include advanced features like always-encrypted, row-level security and columnstore indexes etc. You can get a full list of features which are supported in this Microsoft link: SQL Server on Linux Release Notes.

Download Ubuntu:

The first step is to decide which platform is required to install SQL Server vNext. I decided to download Ubuntu as I already have little bit of experience with it. You can download latest version of Ubuntu form https://www.ubuntu.com/ . You will find a list of Ubuntu files to download. I downloaded Ubuntu-17.04 server 64bit (Zesty Zapus) as shown in pic (InstallUbunut-01)

Oracle VirtualBox

There are different ways to install Ubuntu on your machine. For this exercise I decided to use Oracle Virtual Box. VirtualBox is a powerful x86 and AMD64/Intel64 virtualization product for enterprise as well as home use. Not only is VirtualBox an extremely feature rich, high performance product for enterprise customers, it is also the only professional solution that is freely available as Open Source Software under the terms of the GNU General Public License (GPL) version 2. You can download Virtual Box – Windows host from following link https://www.virtualbox.org/wiki/Downloads

Before starting Virtual box installation it is benificial to download some other required tools during ubuntu installation. You can work directly in ubuntu plateform but there is another way to work by connecting through PuTTY. You can work with PuTTY untill you have openssh on your ubuntu server. You can install openssh directly from ubuntu or by downloading it and install form given path. Following is the image that is showing ubuntu and other downloaded files.

There are different ways to install linux. If you have Windows installed on your machine and want to install linux seperately, you can go for dual boot installation. Dual-booting is a technique which allows a single physical computer to run two or more operating systems. This is useful for experimenting with new OS without touching your existing setup. I have found a very good step by step installation guide on following link http://www.instructables.com/id/How-to-Dual-boot-Linux-and-Windows-on-a-PC-with-W/

I choose virtual box to install ubuntu from my existing Windows. Next is step by step example for installing oracle virtual box.

1-      Double click on the VirtualBox setup to start installation.

2-      This setup needs to check free space on your hard drive.

3-      You will get reset warning that you can read and press Yes.

4-      Setup will start installing Virtual box and it will take few minutes only depending on your machine performance.

5-      You need to install Oracle Corporation Universal Serial Bus, press install

6-      Setup may ask for some other network tools for installation

7-      You will get successful installation message, press Finish to start Oracle Virtual box

8-      After finishing setup Oracle VM Virtual Box Manager will open.

Create New VM using Oracle Virtual Box

1-      To start creating new VM setup, Press New icon from left top corner

2-      It will open new VM setup screen where you have to set name for your VM as we are installing Ubuntu for SQL Server so it will be “Ubuntu1710”

3-      Next is Type of your operating system installation, for us it will be Linux

4-      We need to select 64-bit because we have downloaded same version

5-      You need to set required RAM for the VM that will be part from total available system RAM. In this case I have mentioned 4GB out of 6GB. You need at least 3.5 GB in order to install SQL Server on Linux. If you do not have enough RAM, You can change it even after creating VM.

6-      Continue by pressing Next, You need to set Hard disk value. You can add it later or go for virtual hard disk now, which I have selected.

7-      Next pop will be used to select hard disk file type, I have selected Virtual Box disk image

8-      You have to select which type of storage you will prefer, I used dynamically allocated so that I can increase it when required.

9-      In this setup you have to select storage file location and initial size of your VM. I have set 32 GB and will increase on demand.

10-   Press “create” and your new VM will be created. You can see detail on VM main page

11-   Given file location name will be created windows folder.

You have successfully completed virtual box and now need to start installing Ubuntu setup on it.

From this point on words Ubuntu installation will start. I will publish step by step detail in my next post... keep reading :)