Creating local network with internet access using VirtualBox and Ubuntu 16.04

Preconditions

VirtualBox is installed. Download link.

Ubuntu 16.04 server is downloaded. Download link.

Creating a virtual machine

1. Run VirtualBox and click 'New' button.

2. Specify virtual machine name.

3. Set RAM (Depending on your purpose it could be any value).

4. Select 'Create a virtual hard drive now'.

5. Select VDI.

6. Select 'Dynamically allocated'.

7. VirtualBox tries to store the image of the hard drive on disk C, so I recommend to change that and save the image on another logical hard drive.

8. Click 'Create' button.

Installing Ubuntu 16.04 server

1. Select the virtual machine and click 'Settings' button on the left top corner of the window.

2. Open 'Storage' settings → for IDE controller select download Ubuntu 16.04 image.

3. Make sure that network settings have 'Attached to' set as NAT. We need this for ubuntu installation, in other mode operation apt-get might not work (see Troubleshooting section for more info).

4. Start the virtual machine and follow Ubuntu installation instructions, they are pretty straightforward.

Configuring the virtual machine to work on the local network with internet access.

Now we are ready to configure our virtual machine. To do that follow instructions below:

1. Select the virtual machine and click 'Settings' button on the left top corner of the window.

2. Open 'Network' settings → in 'Attached to' select 'Bridged Adapter'.

3. Boot the server.

4. Open /etc/network/interfaces with any text editor.

 sudo nano /etc/network/interfaces

5. Set Ip address and DNS server for the server using the following configuration (NOTE: change IP address in case you are configuring multiple servers).

# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface
auto <your network>
iface <your network> inet static
        address 192.168.1.2
        netmask 255.255.255.0
        gateway 192.168.1.1
        dns-nameservers 75.75.75.75 75.75.76.76
        dns-search local

Troubleshooting

1) If apt-get command does not work due to IPv6 issue. Please follow instructions below to fix it.
https://askubuntu.com/a/787491/733897 

Online stopwatch

00:00:00.000

Start Stop Reset

S - start or stop, ESC - reset

Creating SQL Server database project from existing database

Many enterprise solutions have an existing database in place. To start working with SQL Server project developers have to migrate existing database in it. In this article, I will describe how to migrate the existing database to the database project.

Creating schema compare files

Using Schema comparison tool in Visual Studio, it is possible to compare existing database schema and the schema presented in the project. To start comparison process right-click on project → Schema Compare.

 

After that new windows will open. On that screen, we need to select the source and the target databases. Click on 'Select source' drop-down list, then select 'Select source' option.

On opened dialog select 'Database' radio button and press 'Select Connection' button. After that, you have to provide database connection information.

For 'Target database' select project option.

Click on 'Compare' button. It might take some time to generate differences, but after that, you will see all items which should be updated in the target database.

We will save this Schema compare in our solution. It is better to do that because you will rather frequently run the comparison. It will save some time for you in future. You can have multiple 'schema compare' items, e.g., for local database, staging or production databases. I create the _schemaCompare folder in solution to store schema compare items there. Click save schema and save it to created folder and after that add a new item to solution folder.

Configuring schema compare

Sometimes you need to exclude some objects from schema compare or set it in some specific way. It is possible to do. Click on 'Gear' button on the top panel. 

After you configure the schema compare save it and check into project repository. Your schema compare will be available for all team members.

Creating database project from existing database

Now we are ready to create new database project from existing database. Double-click on local.compare.scmp file → Click Compare button → Click Update button → Done!

Conclusion 

In this article, I showed how to work with schema compare in SQL Server database project and how to create database project from existing database using schema compare.  Source code example for this tutorial could be found using the following link: https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/05-Create-Database-Project-from-Database.

Configuring SQL Server database project

The configuration of SQL Database project is important. It this article we will learn how to create a basic configuration for the project, which would work in 80% of cases.

Configuring Filegroups

Using multiple filegroups allows for a lot of benefits:

1. Separating out internal system data from user data
2. Placing larger tables/indexes on their own filegroup and/or dedicated set of disks for performance
3. Placing archive (or even read-only) data onto their own filegroups and dedicated set of disks to reduce maintenance overhead
4. Ability to bring parts of the database online quickly, no need to wait for a full restore

Having your data separated to several filegroups is important. If you interested into this topic, please read DATABASE FILEGROUPS: JUST LIKE SEATBELTS BUT WITH LESS CHAFING or some other materials, which you quickly find using search engines.

In our test project, we will create two filegroups. One filegroup we will use for users' data, another will we use to store indexes. If you forgot how to add new items in solution, please use Creating stored procedures and additional objects in SQL Server Database Project article for more information.

DataFileGroup.sql code

/*
Do not change the database path or name variables.
Any sqlcmd variables will be properly substituted during 
build and deployment.
*/
ALTER DATABASE [$(DatabaseName)]
     ADD FILEGROUP [DATA]

IndexesFileGroup.sql code

/*
Do not change the database path or name variables.
Any sqlcmd variables will be properly substituted during 
build and deployment.
*/
ALTER DATABASE [$(DatabaseName)]
     ADD FILEGROUP [INDEXES]

I've put files in the root of the project. So the structure of the project looks like on the picture below.

Database project has default filegroup with name 'PRIMARY'. We have to change that and set 'DATA' as default filegroup. To change default file group, please use Set default filegroup for Microsoft Database project article.

Configuring Target platform

Target platform specifies the version of SQL Server that you are targeting with this database project. In Visual Studio 2017 there are few possible options for the target platform: 

• SQL Server 2005
• SQL Server 2008
• SQL Server 2012
• SQL Server 2014
• SQL Server 2016
• Microsoft Azure SQL Database
• etc.

Choose what suits you best. I will keep target platform as SQL Server 2016.

Configuring Default schema

Default schema property specifies the default schema in which both SQLCLR and Transact-SQL objects are created. You can override this setting by specifying schema directly on objects.” In our scenario, we are not going to change default schema.

Configuring Build options (optional)

From my perspective, there is one option among build option which can bring some value to the project. This option is 'Treat Transact-SQL warnings as errors'. This option specifies whether a Transact-SQL warning should cause the build and deployment process to be canceled. If this check box is cleared, warnings appear, but the build and deployment process continues. This setting is specific to the project, not the user, and is stored in the .sqlproj file. Personally, I prefer to set this option to true, because strict rules can reduce potential issues in future. More information about build option you can find using link https://msdn.microsoft.com/en-us/library/hh272681(v=vs.103).aspx#SQLCLR and SQLCLR Build.

Database settings

To change database settings you have to navigate to Project Setting → Database Settings.

Configuring database collation

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it is important that you understand the locale requirements, sorting order, and case and accent sensitivity of the data that you will be working with. 

A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data. SQL Server supports storing objects that have different collations in a single database. For non-Unicode columns, the collation setting specifies the code page for the data and which characters can be represented. Data that is moved between non-Unicode columns must be converted from the source code page to the destination code page. Read more about collation https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support.

To configure database collation navigate to Project Settings → Database Settings → Common → Database Collation.

Configuring compatibility level

A lot of enterprise solutions have a long history, so many of them started on old versions of SQL server. It might be required to setup your project in compatibility level. To do that you have to navigate to Project Settings → Database Settings → Miscellaneous → Compatibility level.

Code analysis

Code analysis is turned off by default. I recommend to activate it and check all 'Treat Warning as Error' checkboxes. To do that navigate to Properties → Code Analysis. (see picture below)

Conclusion

In this article, I tried to provide vision about the configuration of SQL Server Database project. I highlighted the most common and important settings, which should be configured for each project. You can find source code examples using following link https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/04-Configuring.

Compiling SQL Server Database project

One of the great features of SQL Server Database project is the compilation of the project. If the project setup up correctly, developers can use advantages of compilation. Let's assume you have changed the name of the column in some table. You know that this column is used in many places in your database code. It is hard to find all locations where you have to change the name of the column. Compilation of project helps a lot in such scenarios. Just compile your project and Visual Studio will show all places where you have mistakes. To compile the project right-click on project file → Build or Rebuild. After build finishes open 'Error List' window and check if there any error in warning in it.

From previous example http://www.maniuk.net/2017/05/creating-stored-procedures-and-additional-objects-in-sql-server-database-project.html we have some warning which we have to fix. As you can see on the picture below, Visual Studio helps us to identify places which could be the cause of the warning. 

Warning say to us: Severity Code Description Project File Line Suppression State
Warning SQL71502: Procedure: [Application].[CreateRoleIfNonexistent] has an unresolved reference to object [sys].[database_principals].

It means that we have to add a reference to the master database. To add a reference to the master, database you have to navigate to WideWorldImporters project → right-click on References → Add Database Reference → Select System database → Ensure master is selected.

After that, if you recompile your project, all warning will go away. Once again it is critical to use compilation feature of SQL Server Database project. It saves a lot of time for maintenance and issue investigation in future. Source code for this tutorial you can find using link https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/03-Compiling.

Creating stored procedures and additional objects in SQL Server Database Project

In the previous post Creating a new SQL Server Database Project we learned how to create SQL Database project, now we are going to show how to work with some other SQL objects like stored procedures, indexes, key, etc.

Adding stored procedure to the project

When you create stored procedure, you have to keep in mind that you have to put your stored procedure in the appropriate folder related to the particular application domain. In your example, we will create stored procedure in 'Application' domain. So at first, let's create folder 'Stored Procedures' in 'Application' folder. After that right-click on created folder → Add → Stored Procedure.

CreateRoleIfNonexistent.sql stored procedure code

CREATE PROCEDURE [Application].CreateRoleIfNonexistent
@RoleName sysname
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @RoleName AND type = N'R')
    BEGIN
        BEGIN TRY

            DECLARE @SQL nvarchar(max) = N'CREATE ROLE ' + QUOTENAME(@RoleName) + N';'
            EXECUTE (@SQL);

            PRINT N'Role ' + @RoleName + N' created';

        END TRY
        BEGIN CATCH
            PRINT N'Unable to create role ' + @RoleName;
            THROW;
        END CATCH;
    END;
END;

Now we have learned now to create stored procedure in SQL Server Database project.

How to add other SQL objects

Select appropriate folder → right-click on it → Add → New Item.

There are several groups of items:

• Programmability
• Security
• Service Broker
• Server Objects
• SQL CLR
• SQL CLR C#
• Storage
• Table and Views
• User Scripts

If you do not like to search for each group, there is search textbox in the right top corner, and you can use it to find necessary item. Code example for this tutorial could be found using following link https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/02-Add-Stored-Procedure.

Creating a new SQL Server Database Project

In my previous post Migration-base vs State-based database development approach, I've shown two main approaches for database development. Today we will concentrate on state-based approach and learn how to start working with Microsoft Database Project.

Creating new project

Open Visial Studio → click 'File' → 'New' → 'Project'

If you are running Visual Studio 2017 navigate to 'Other languages' → 'SQL server' and select SQL Server Database Project.

After this operation Visual Studio will create an empty database project in the specified folder.

Creating table

To give good understanding for developers about business domains included into the project, the project should be well-structured. It is better to allocate specific schema for each of business domains and put all associated items (stored procedures, tables, triggers, etc.) with the schema in a separate folder. So let's create first business domain with the name 'Application'. At first we have to create folder with name 'Application' in solution. To do that, right-click on solution file → Add → New folder

After you create domain folder, let's create the file with schema. Right-click on 'Application' folder → Add → Script.

Specify an appropriate name for the schema file and make it part of build process. That will allow us to identify issues with SQL code during project compilation.

Application.Schema.sql code

CREATE SCHEMA [Application]
    AUTHORIZATION [dbo];

GO
EXECUTE sp_addextendedproperty 
    @name = N'Description', 
    @value = N'Tables common across the application. Used for categorization and lookup lists, system parameters and people (users and contacts)', 
    @level0type = N'SCHEMA', 
    @level0name = N'Application';

Now we are ready to create our first table. Let's create 'Tables' folder in 'Application' folder and create People table there.

People.sql code

CREATE TABLE [Application].[People] (
    [PersonID]     INT             NOT NULL,
    [FullName]     NVARCHAR (50)   NOT NULL,
    CONSTRAINT [PK_Application_People] PRIMARY KEY CLUSTERED ([PersonID] ASC)
)

GO
EXECUTE sp_addextendedproperty
    @name = N'Description',
    @value = 'Full name for this person',
    @level0type = N'SCHEMA',
    @level0name = N'Application',
    @level1type = N'TABLE',
    @level1name = N'People',
    @level2type = N'COLUMN',
    @level2name = N'FullName';

GO
EXECUTE sp_addextendedproperty
    @name = N'Description',
    @value = 'Numeric ID used for reference to a person within the database',
    @level0type = N'SCHEMA',
    @level0name = N'Application',
    @level1type = N'TABLE',
    @level1name = N'People',
    @level2type = N'COLUMN',
    @level2name = N'PersonID';

GO
EXECUTE sp_addextendedproperty
    @name = N'Description',
    @value = N'People known to the application (staff, customer contacts, supplier contacts)',
    @level0type = N'SCHEMA',
    @level0name = N'Application',
    @level1type = N'TABLE',
    @level1name = N'People';

Finally, we created our first SQL Server Database project. Congrats! Source code related to this tutorial could be found using following link https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/01-Create-Database-Project.