#MSSQLServer2017

[ryadel]

How to Install, Setup and Configure MS SQL Server 2017 Express Edition

If you're a .NET developer working with Visual Studio, you'll most likely know SQL Server LocalDB: a specialized edition of the well-known Microsoft SQL Server engine - firstly introduced in SQL Server Express 2012 - acting as a minimal, on-demand, version of SQL Server designed for application developers. Since its first introduction LocalDB has proved itself to be a great development tool, especially when used as an "embedded database" to quickly test our Data Model without having to setup  a whole DBMS service. We briefly talked about it in a couple posts some months ago, such as when we compared the Entity Framework Data Modeling patterns (Code-first, Model-first and Database-first) and in our ASP.NET MVC 5 Web Application Project Setup Guide. Although LocalDB proved itself to be a great development choice, it's not a good idea to use it in a production environment of any kind. The reasons for that are fairly simple to explain and understand: Despite having the same performances of a regular SQL Server Express instance - since they share the same database engine - the LocalDb underlying process will shut down itself if it doesn't have any request for a while. When this happens, the next request(s) will most likely have poor performance and could even get a timeout. There are some (minor and fixable) issues with the LocalDB default user profile and permissions that you'll need to fix to use it with an IIS-based production website: for further info about these problems - and their workarounds - I strongly suggest to take a look to this great two-part MSDN blog post (part 1 | part 2). The "LocalDB in production" debate is also a recurrent topic on tech sites such as StackOverflow, which hosts some really interesting threads providing a great analysis of the pros and cons, such as: Is it normal to use LocalDb in production? Since LocalDB is for development purpose, then use what DB for production? Having read and said all that, here come my 2 cents: although LocalDB is great, for a production machine I would strongly recommend using a standard, full-fledged and service-based instance of SQL Server: as for the chosen edition, we can either go for Express, Web, Standard, or Enterprise, depending on what we need and/or can afford. In this post we'll see how to properly setup and configure an instance of  Microsoft SQL Server 2017 Express Edition, aka MSSQL2017, which can be downloaded for free from this Microsoft page. Needless to say, we need to install it on a machine that is reachable from our web server via a Local Area Network (LAN) or within the web server itself, although this is definitely not a recommended choice: both IIS and SQL Server are resource-intensive, hence it could be advisable to keep them in two separate environments. In the following tutorial we'll take for granted that we'll have a Windows Server available and accessible either phisically or via Remote Desktop, which would be the case for a server hosted, housed, virtualized or even available as a VPS or within a public, private or hybrid cloud farm.

Installing SQL Server 2017 Express

The installation process is pretty straightforward. Unless we don't need anything specific, we can just go for the basic type:

Eventually, we'll be prompted with the Installation Complete window, which will also give us some useful info, including the database instance name and a default connection string ready for a connection test:

Installing SQL Server Management Studio

From here, we can click the Install SSMS button and download SQL Server Management Studio, a tool that we can use to create our fist sample database and also a dedicated user that can access it. SQL Server Management Studio is a separate product and can also be retrieved for free download at the following URL.

Configuring the MSSQL Service

Once we've downloaded and installed it, we can launch SQL Server Management Studio and start the required steps to create our first database. We will be prompted by a Connect to Server modal window that will allow us to connect to our local SQL Server instance. To do this, select the Database Engine server type and then, from the Server name combo box, choose . Another pop-up window will appear, from which we'll be able to select the database engine we just installed on our server:

As for the Authentication part, we can leave Windows Authentication for now, being it the default SQL Server authentication mode: however, we're going to change it soon enough. When we're done, click on the Connect button and a Server Explorer window will appear, containing a tree view representing the structure of your SQL Server instance. This is the interface we'll use to create our database and also the user/password that our application will use to access it. Changing the authentication mode The first thing we need to do is to change the default SQL Server authentication mode, so we won't be forced to use an existing Windows account. To do so, right-click on the root tree view node, which represents our SQL Server instance, and select Properties from the contextual menu. From the modal window that appears, select the Security page, then switch from Windows Authentication mode to SQL Server and Windows Authentication mode:

Adding the TestMakerFree database Now we can create the database that will host our application's tables. Right-click on the Databases folder and choose Add Database from the contextual menu. Give it the TestMakerFree name and click on OK. Adding the TestMakerFree login Go back to the root Databases folders, then expand the Security folder, which should be just below it. From there, right-click on the Logins subfolder and choose New Login. Again, give it the TestMakerFree name. From the radio button list below, select SQL Server Authentication and set a suitable password - for example, SamplePassword123$ - and click on OK. If you want a simpler password you might have to also disable the enforce password policy option. However, we advise against doing that: choosing a weak password is never a wise choice, expecially if we do that in a production-ready environment. Instead, replace the sample password we used above with a custom one and store it carefully: we're going to need it later on. Mapping the login to the database The next thing we need to do is to properly map this login to the TestMakerFree database we added earlier. From the navigation menu to the left, switch to the User Mapping tab. Click on the checkbox right to the TestMakerFree database, then write TestMakerFree in the User cell and assign the db_owner membership role:

As soon as we click on the OK button, a new TestMakerFree user will be added to the TestMakerFree database with full administrative rights. We can easily confirm that by going back to the root Databases folder and expanding it to TestMakerFree > Security > Users:

That's it! Now we'll be able to access our brand new TestMakerFree database with a standard connection string using the credentials we just created. This article is part of the ASP.NET Core 2 and Angular 5 book, available as paperback, e-book and as a 26-lessons video-course. Promo Code: ASPCA50 to get it with a 50% discount!   Read the full article

[marcosplavsczyk]

One of the many tasks of the DBA in charge on a development project is to make sure the team stays consistent in their code. Routine checks for SQL best practices such as naming conventions, right data types usage, problematic things like cursors, potentially fatal things like Deletes without Where clauses can cause a lot of headaches.

ApexSQL Enforce makes best practices reviewing easy. With its more than 100 pre-installed rules, it can detect and fix issues, lets you write your own or customize existing rules and even setup automated reviewing.

This article is covering the process of setting up an automated reviewing process.

Preparing the rulebase

The process of setting an automated best practices review starts by choosing the rules that will be run against a database and configuring the software. The easiest way to do this is by loading a rulebase in ApexSQL Enforce GUI.

Quick tip:

Rulebases, as the name implies, are files where rules are stored in ApexSQL Enforce. Beside rules, rule selection and rulebase threshold values are stored along with some other useful information like: rulebase description, categories, version number and author information

Loading rules in ApexSQL Enforce can be done in few ways:

In the Home ribbon menu select the Open button and navigate to the desired rulebase

Opening a new rulebase and importing rules

Select New from the Home ribbon menu, in the New rulebase dialog choose Create rulebase form existing, make sure that including rules is checked and navigate to the desired rulebase.

The pre-installed rulebases can be found in the default ApexSQL Enforce folder: Documents\ApexSQL\ApexSQL Enforce\Rulebases

The default rulebase location can be changed in the Options window.

For demonstration purposes, “ApexSQL SQL code rules C#” rulebase.

Rule selection and management

Once the rulebase is loaded, a list of rules will populate the main window. By simply clicking the check boxes in the rules list, the rule selection is made to include this rule, the next time the rulebase is executed. This selection will be saved in the rulebase. Additional rules can be imported from the Import rules window

Set the rulebase threshold values which can be found in the Edit rulebase window, under the Threshold. To learn more about the importance of threshold values, please read this article

Now, that the rulebase is set, it can be saved as a new rulebase file.

Writing automation scripts using the ApexSQL Enforce CLI

As with most ApexSQL tools, ApexSQL Enforce includes a CLI interface/console application. To demonstrate how to setup CLI switches it is always the easiest to show on an example:

Quick tip:

The shown examples assume that the current working directory is the application folder In this example, the rulebase has been moved to the same directory (application root) Results are written to the ApexSQL Enforce results sub-directory

‘ApexSQLEnforce’ /s:P3TAR\MSSQLSERVER2017 /d:AdventureWorks2014 /rb:$rulebase /ot:f /on:$fixsqlOutput.sql’ /f /v $LASTEXITCODE

The first step is to define ApexSQL Enforce.com which is the console application. This is a mandatory step for the script to work.

Next, connections switches are specified:

/s:P3TAR\MSSQLSERVER2017 – defines the SQL Server connection.

/d:AdventureWorks2014 – selects a database on the specified server

Using the /rb switch, specify the rulebase that will be used in the reviewing process.

The previously prepared rulebase is specified using the $rulebase variable.

These are the three main switches in ApexSQL Enforce CLI, by adding a /v or /verbose switch the script can be run as it is ant it will give a valid result of the reviewing process.

Quick tip:

Rulebases can be also run against SQL scripts, in addition to databases. To process SQL scripts via the CLI the /sc switch must be placed between /d and /rb switches

By adding an /ot switch the CLI will create output of the resultset in one of the following formats:

c – console output (this exports the messages from during the process provided via /verbose switch to a txt file) advised to be used along with /out switch

x – creates an xml export file of the resultset at the end of the process

f– Spool out Fix SQL scripts form the result set into a single SQL script

h – Wraps the resultset into a detailed HTML report file.

The /on switch defines the output file path, name and extension. The /on switch can also specify just the output file name in which case the working directory will be used as output location.

Finally at the end of the script the /v (verbose) and /f (force) switches are specified. Both of these switches are common in ApexSQL tools with the only difference being that /v switch is mandatory in ApexSQL Enforce.

The $LASTEXITCODE in the new row serves to display the return codes.

This CLI script is just an example of common switch usage, to see a fully explained list of ApexSQL Enforce CLI switches with usage examples, please read ApexSQL Enforce Command Line Interface CLI switches

Script execution and results

ApexSQL Enforce CLI scripts can be executed as PowerShell scripts. The result of a successfully finished execution will output a result summary screen similar to the one in GUI:

In addition to with a full detail summary, ApexSQL Enforce produces a return code, which is helpful in the CICD process and can be passed to calling scripts.

When the review is finished succeesfully ApexSQL Enforce will return

0 for Pass, meaning that the application successfully process, without errors and that the rulebase was also processed with a cumulative result of “Pass”

1 means an application error was experienced

102 indicates there was no application error but the rulebase generated a result of “Fail”

Additionally, using /os switch, displayed results can be filtered. The arguments for the switch are:

p – to include passed Pass results

f – to include fail results

e – to include error results

If the /os switch is not specified, fail and error results will be displayed, by default which makes this switch optional.

For more information about return codes in ApexSQL tools read General usage and the common Command Line Interface (CLI) switches for ApexSQL tools

At the output destination, the Fix SQL script is generated:

It isn’t always necessary to specify output switches to save results because by default ApexSQL Enforce generates detailed trace logs of each run both in from CLI scripts and from GUI:

Trace logs details can be customized from the Trace logging tab in the Options menu:

Once the CLI combination is set, the PowerShell script can be sheduled in one of the Four ways of scheduling ApexSQL tools

Useful links

ApexSQL Enforce Command Line Interface CLI switches

How to fail at running rulebases

How to import new rules into an existing rulebase

  The post How to execute an unattended best practices review against a database appeared first on Solution center.