MS SQL Server – Management Studio

SQL Server Management Studio is a workstation component\client tool that will be installed if we select the workstation component in installation steps. This allows you to connect to and manage your SQL Server from a graphical interface instead of having to use the command line.

In order to connect to a remote instance of an SQL Server, you will need this or similar software. It is used by Administrators, Developers, Testers, etc.

Administering SQL Server instances is a job for professional users who have both the knowledge and befitting tools. Designed by Microsoft itself, SQL Server Management Studio 2017 is an advanced application that aims to provide its users with all the necessary functions to carry out this task successfully.

This package is tailored for SQL Server (including the latest version), Azure SQL Database and SQL Data Warehouse, providing the ability to manage the server database engine instances, allowing the quick management of databases and their contents.

Microsoft provides two specialized free editions to work on MS SQL server:

1. Developer – It has all feature which MS SQL server offers but we cannot use it in production. From the learning perspective, is it an ideal candidate to start.
2. Express: This is also a free version but with a limited set of features with no business intelligence applications.

SQL Server Management Studio Main Features

SQL Server has many built-in functions. This reference contains string, numeric, date, conversion, and some advanced functions in SQL Server.

1. Always on Availability Groups— This feature takes a database mirroring to a whole new level. Always on, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.
2. Windows Server Core Support — If you don’t know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). The core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported by SQL Server.
3. Columnstore Indexes— This a cool new feature that is completely unique to SQL Server. They are a special type of read-only index designed to be used with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

1. User-Defined Server Roles— DBAs have always had the ability to create custom database roles, but never server-wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of these was a good solution. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.
2. Enhanced Auditing Features— Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.
3. BI Semantic Model— This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It’s a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics
4. Sequence Objects— For those folks who have worked with Oracle, this has been a long-requested feature. A sequence is just an object that is a counter — a good example of its use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.
5. Enhanced PowerShell Support— Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of its server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.
6. Distributed Replay— Once again this is the answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.
7. Power View— You may have heard of this under the name “Project Crescent” it is a fairly powerful self-service BI toolkit that allows users to create mashups of BI reports from all over the Enterprise.
8. SQL Azure Enhancements— These don’t really go directly with the release of SQL 2012, but Microsoft is making some key enhancements to SQL Azure. Reporting Services for Azure will be available, along with backup to the Windows Azure data store, which is a huge enhancement. The maximum size of an Azure database is now up to 150G. Also, Azure data sync allows a better hybrid model of cloud and on-premise solutions
9. Big Data Support— I saved the biggest for last, introduced at the PASS (Professional Association for SQL Server) conference last year, Microsoft announced a partnership with Hadoop provider Cloudera. One part of this involves MS releasing an ODBC driver for SQL Server that will run on a Linux platform. Additionally, Microsoft is building connectors for Hadoop, which is an extremely popular NoSQL platform. With this announcement, Microsoft has made a clear move into this very rapidly growing space.

What’s new in Microsoft SQL Server

• Support to Persistent Memory (PMEM) Devices
• Column store Index Enhancements
• Resumable Online Index Creation
• Up to Five Synchronous Replica Pairs for Availability Groups
• Enable High Availability Configurations for SQL Server Running in Containers
• Better Scale-out with Automatic Redirection of Connections Based on reading/write Intent
• SQL Data Discovery and Classification
• Always Encrypted with Secure Enclaves
• UTF-8 Support
• Certificate Management Functionality in SQL Server Configuration Manager
• Vulnerability Assessment

Benefits Microsoft SQL Server

• Easy Installation: All the Microsoft products are easy to install with the one-click installation procedure and readable GUI with lots of instructions for the layman. MS SQL Server contains all these characteristics and it has an extremely user-friendly installation interface, unlike other database servers that require extensive command-line configurations.
• Improved Performance: MS SQL Server Management Studio contains excellent compression and encryption capabilities that result in improved data storage and retrieval functions.
• Security: MS SQL server is considered one of the most secure database servers with complex encryption algorithms making it virtually impossible to crack the security layers enforced by the user. MS SQL server is not an opensource database server which reduces the risk of attacks on the database server.
• Multiple Editions and Price Variations: A good thing about MS SQL server is that it is available in multiple editions in order to cater to the needs of huge corporate sector organizations to a domestic user. The price range also varies which allows anyone to buy the product which meets their price range.
• Excellent Data Restoration and Recovery Mechanism: MS SQL server is fully aware of the importance of your data. Hence MS SQL Server contains many sophisticated features that allow you to recover and restore the data which has been lost or damaged. Although you cannot recover individual records it is possible to restore the complete database using some advanced recovery tools contained in the MS SQL Server database.