Friday, August 19, 2016

Setting up .NET Core on Ubuntu and publishing a website

What can I say, I love Linux, but I also love Visual Studio and C#. When I first learned about .NET Core being able to run on Linux, it was the joining of two worlds. In this post, I hope to walk through the setup and publishing a website using these technologies.
Prep the environment -

  • Install Ubuntu 16.04.01 server
  • standard system utilities
  • OpenSSH server

Install .NET Core for Ubuntu
1. Add .NET Core to your sources-list
sudo sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/dotnet-release/ xenial main" > /etc/apt/sources.list.d/dotnetdev.list'
sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893

2. Update and install .NET Core SDK
sudo apt-get update
sudo apt-get install dotnet-dev-1.0.0-preview2-003121
sudo apt-get install npm
sudo npm install -g bower grunt-cli gulp

3. Configure the firewall to only allow SSH and web traffic
sudo apt-get install ufw
sudo ufw allow 80/tcp
sudo ufw allow 443/tcp
sudo ufw allow 22/tcp
sudo ufw enable

4. Create your first .NET Core web app
Install Visual Studio 2015 Community Edition: https://www.visualstudio.com/en-us/visual-studio-homepage-vs.aspx
Install Visual Studio 2015 Update 3: https://go.microsoft.com/fwlink/?LinkId=691129

Open Visual Studio Community Edition and create a New Project 
For the project template, select Installed | Templates | Visual C# | Web | ASP.NET Core Web Application (.NET Core)
Name the project HelloWorld and click OK to create the project
Select Web Application, clear the checkbox Host in the cloud, and click OK
When the project opens, you can select Debug | Start Debugging, or click F5 to see the contents of the website

Publish the website
In Visual Studio Solution Explorer, under the src folder, right-click the project HelloWorld and click Deploy. You may need to configure a deployment template to deploy to the local file system.

Using WinSCP, copy the published folder to /var/aspnetcore/HelloWorld
Set permissions to root - 
sudo chown root:root /var/aspnetcore -R

5. Install / configure NGINX
sudo apt-get install nginx
sudo service nginx start
sudo mv /etc/nginx/sites-available/default /etc/nginx/sites-available/default.old
sudo nano /etc/nginx/sites-available/default

server {
    listen 80;
    location / {
        proxy_pass http://localhost:5000;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection keep-alive;
        proxy_set_header Host $host;
        proxy_cache_bypass $http_upgrade;
    }
}



6. Setup and configure Supervisor
sudo apt-get install supervisor
sudo nano /etc/supervisor/conf.d/helloworld.conf

[program:helloworld]
command=/usr/bin/dotnet /var/aspnetcore/HelloWorld/HelloWorld.dll
directory=/var/aspnetcore/HelloWorld/
autostart=true
autorestart=true
stderr_logfile=/var/log/helloworld.err.log
stdout_logfile=/var/log/helloworld.out.log
environment=ASPNETCORE_ENVIRONMENT=Production
user=www-data
stopsignal=INT

sudo service supervisor stop
sudo service supervisor start
sudo update-rc.d supervisor enable
The website should now be visible. Supervisor should automatically start the website if a system reboot occurs or if the site crashes.


References:
Installing .NET Core: https://www.microsoft.com/net/core#ubuntu
Getting started with .NET Core: https://docs.asp.net/en/latest/getting-started.html
Publishing a website to .NET Core: https://docs.asp.net/en/latest/publishing/linuxproduction.html


Tuesday, March 22, 2016

One method to synchronize SQL users in AlwaysOn groups

I have recently started working with AlwaysOn availability groups. The suggested method to deal with users in AlwaysOn groups is with partially contained databases, then the user is in the DB only, which is replicated to all nodes. However, there are some situations where you cant make a DB a contained DB.

One of the things I like to do with SQL is create a read-only SQL user for reporting name 'ReportUser'. Not knowing the intricacies of AlwaysOn, I created the SQL user on both nodes using traditional SQL. This worked at first, but when I failed over the node, my access stopped working.

After some googling, I found the answers at https://support.microsoft.com/en-us/kb/918992 and https://msdn.microsoft.com/en-us/library/ms175475.aspx. It turns out that each user has a SID, which is different on each server, but only one matched the SID in the DB.

How I resolved it
I started by looking at the Orphaned User report
exec sp_change_users_login @Action='Report'

This returned the SID of my ReportUser account in the database. I failed the DB over to the other server and then deleted the ReportUser account from my system.
Then, I recreated my user, but this time specifying the SID:
CREATE LOGIN [ReportUser] WITH PASSWORD = 'oiu234rno89aSdf', SID = 0x144186B7023F4D43BDA437284FB3E1D9, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF


Now, since the SIDs match on the two servers, when the DB migrates between the servers, my account works.