Step by step guide to run SQL Server in a Windows Docker container

In October 2017 Microsoft announced the general availability of SQL Server 2017 for Windows, Linux and Docker. And since I started to play around with Docker I thought it’s a good idea to setup my next development environment for SQL Server on Docker?

Why? Because Docker provides the ability to package and run an application in a loosely isolated environment called a container. And a Docker container is much more lightweight than a Virtual Machine, because it doesn’t need an extra Operating System, it runs directly within the host machine’s kernel.

If you want to learn more about Docker itself, please have a look here: https://docs.docker.com/engine/docker-overview/

Let’s get started

Since I run Windows 10 on my developer machine it need to setup SQL Server in a Windows Docker container and I figured out, that there some pitfalls that I came along. So I though it’s maybe benefitial to write a Step by step setup guide.

Step 1: Download and Setup Docker for Windows

To get started we need to download and install Docker first on our machine. You can find the Windows setup here: https://www.docker.com/docker-windows

And there is also a very nice setup guide you can find here: https://docs.docker.com/docker-for-windows/install/. After your setup I would recommend to test if everything works fine. Just follow these examples and run your “Hello World” on Docker.

Additionally I would recommend to install Kitematic, a simple and powerful graphical user interface to manage your Docker environment.

image

Step 2: Setup Docker for Windows containers

After installing Docker on your machine Docker will start automatically in the background. As per default, Docker runs with Linux containers and you have to switch to Windows containers first. This is very is, just right click on your Docker icon in the taskbar and select “Switch to Windows containers”.

Screenshot

Step 3: Get your SQL Server container image

After we performed the basic setup steps, we now need to get the SQL Server docker image. There are two sources available to get preconfigured images:

Microsoft made SQL Server available in the Store and in the Hub:

I will continue with the SQL Server 2017 Developer Edition for Windows. In order to download the image run the following command in Powershell oder the Commandline.

docker pull microsoft/mssql-server-windows-developer:2017-latest

This will download the latest version of SQL Server 2017. This step can take a while, depending on your internet connection.

Step 4: Start your SQL Server image

After your image download has been completed you can start your SQL Server image with the following command:

docker run -d –p <Port> –name <FriendlyName> -e sa_password=<Password> -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer:2017-latest

For example you can run it like this:

docker run -d -p 1433:1433 --name sql1 -e sa_password=SecurePassword -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer:2017-latest

In this case, the image will have a friendly name “sql1” and SQL Server will run on port 1433.

For more details please have a look here: https://hub.docker.com/r/microsoft/mssql-server-windows-developer/

Step 5: Test your connection with sqlcmd

In order to test if the image works fine, connect with sqlcmd and run a short sql query. To connect with sqlcmd you can run the following command in your command line:

sqlcmd -U sa –S <YourIP>,<Port>

In order to figure the IP address of your Docker image you can run:

docker inspect --format '{{.NetworkSettings.Networks.nat.IPAddress}}' <ImageName>

For example

docker inspect --format '{{.NetworkSettings.Networks.nat.IPAddress}}' sql1

Within sqlcmd run a small SQL statement like:

select @@version;
GO;

Step 6: Create a SQL Server Login

Now that you can connect with your SQL Server I would recommend to create a login instead of using the SA account. You can do this also with sqlcmd:

create login <LOGIN> with password=’<PWD>’;
GO;
add to a role EXEC master..sp_addsrvrolemember @loginame = N'<LOGIN>', @rolename = N'sysadmin';
GO;

Step 7: Connect with SQL Server Management Studio

When you can connect with sqlcmd you can of course also use SQL Server Management Studio to connect to your SQL instance. You can find the latest version of SQL Server Management Studio here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Start SSMS and then connect to SQL Server as you would also do it with a normal local machine:

image

Step 8: Download and install sample Database AdventureWorks

Very good now we are able to use our SQL Server environement as we are normally used to it. But it makes for fun, if we would have at least some data to play with. Let’s get AdventureWorks and install it on our Docker image.

AdventureWorks is a sample database that you can download here.

In my case I downloaded the AdventureWorks Data Warehouse backup file.

Next we need to get the backup into our image. To do so you have to first stop your image and then run the following command:

docker cp <LocalFolder>\AdventureWorksDW2017.bak <ImageName>:<ImageFolder>\AdventureWorksDW2017.bak

In my case:

docker cp C:\download\AdventureWorksDW2017.bak sql1:C:\AdventureWorksDW2017.bak

Start your image again, get your new IP-Address and connect with SQL Server Management Studio. Then restore your database from your backup file and you are good to go.

image

Step 9: Query some data and have fun

The last step is the easiest one. Just fire some SQL queries in your Management Studio.

image

Sources:

Back alive, with more ideas than ever before

It’s been a while since my last post on my blog. I always liked writing blogs an I still do and I decided to put it back on my priorities.

Why? I always liked to share my experiences I made working together with inspiring people,  great teams and great customers. I’m a big fan of communities and I truly believe that great things can only be achieved with great teams,  communities and great environments. So here I am, back alive…

DSC04047DSC04049DSC04367DSC04100DSC04031

What happened in the last 2 years and what can you expect happening here?

My last post was from the PASS Business Analytics Conference in California, 2 years ago. I really enjoyed the trip and I got so many fantastic impressions from my visit at the Silicon Valley, San Francisco and Stanford University. Stanford and San Francisco are some of the best places I have ever been, I can really recommend them. The spirit and the energy that you can take from there is unbelievable.

Welcome in the Start-up space

So my adventure started right away after that conference. Together with 3 friends (Artus, Markus and Gernot) we decided to start a company in the area of Machine Learning and Data Science and we used the summer of 2015 to find another partner for that adventure. Artus was our Chief Data Scientist and already had some experience with his company Lytiq. Markus,  Gernot and me already worked together as a team since 2011, so we knew each other very well. We also had some experiences in the area of Data Science and Machine Learning with some really nice customers like Runtastic and TUIfly. And also inspired by the great team of Runtastic and their founders we decided to start our own company.

We made our homework, started with a plan for our business model and set some high level goals for the first 2 years. A good starting point for business model development are Blue Ocean Strategy and Business Model Canvas. I can really recommend these books.

Image result for blue ocean strategy  Image result for business model canvas book

Funding – not an easy game

After we had our business plan and a good team setup ready we needed to get some funding, which is not that easy for a consulting company. So we created our pitch deck and sent it around to different VCs, Accelerators and other founders and tried to get some funding for our start. We got a lot of “no thank you, we are not interested in consulting”, but still this was really an interesting and funny experience with no big expectations in the first step. We were a bit naïve in that game, but we still had very good meetings and opportunities. To keep the long story short and the end we choose pmOne (the company Gernot, Markus and I worked before) as our partner for our Start-up.

The start

At the 15th of September 2015 together with pmOne and Lytiq we founded pmOne Analytics in Paderborn (a small town in the middle of Germany) and started this company with 5 people (Artus, Markus, Gernot and me) and Euclides our first Data Scientist and employee.

Why Paderborn? Because Artus is also a professor at the University of Paderborn teaching Data Science, Machine Learning and Business Intelligence. This gave us a great opportunity the get more young talents in that area. So in the last two years I spent my time with the team to build a Data Science company in Germany and Austria and it was the best team I have ever worked with.

IMG_0063

We already have a second office in Vienna and a team of around 20 people mixed with Data Scientist and Data Engineers and a lot of great projects and customers. We also created a product called Wundermailing, that was mainly invented and driven by me with nice customers like TUIfly, T3N or SHT.

wundermailing_logo

So far, this was my far the best time in my career and I want to share my experiences we had in different areas:

  • How do we build a team?
  • How do we grow?
  • How do we earn money?
  • How do we lead a company with people much smart than us, most of them with a PhD?
  • Which technology should we pick? Amazon, Google, Microsoft or Open Source?

What’s next?

As already mentioned this was by far the best time in my career and there are so many things I learned, that I want to give back and share with others.

Related image

I want to take the chance in my next blob posts to structure my experience in 3 different areas:

  • Leadership and Team Building
  • Business Growth
  • Data Science and Technology

So thank you for reading and sorry for the long pause. But stay tuned…

PASS Business Analytics Conference 2015 – Day 2

Good morning. So we here go with the second day of the PASS BA Conference. After a really good first day todays keynote started with a Lady I have never heard before. Her name is Mico Yuk, CEO of BI Brainz and she is a great lady. And although it was very hard to have a keynote after Day 1 keynote of Carlo Ratti, who showed us more the visionary future of Analytics, she really made it very well and grounded the people with the day to day challenges.

WP_20150422_08_08_02_Pro

Keynote – Visual Storyboarding

Her talk was about Visual Storyboarding and there where plenty other talks about that subject today.  So let me try to get that story first for you. She started with the general disappointments companies have with Business Intelligence (BI) projects. We hear that a lot these days, normally addressed by some software vendors mostly out of the Hadoop world, but she made it very clear. Let’s start with 2 facts she presented:

  • $97 Billion annual BI spend by 2016
  • But the average Business Intelligence user adoption rate is only 21%

P1030409

Lot’s of BI users still seem to use a lot of “export to Excel” functionality and do the Analytics work on their own. That must make us think. So why is the user adoption rate of BI and Data Warehouse projects so low? It reminds me of a quote of John Naisbitt:

“We are drowning in Information but starved for knowledge”.

But what are the reasons for? BI projects in general are still to technical driven by the IT and we still have lot’s of silos in organizations, like a BI Team, Analysts, Data Science Team, etc. But these teams have to work together, because a lot of Data Scientists spent a long time at school and are smart, but with not much experience in the field and not enough business understanding. That is a gap that business departments and analysts can fill. In the world of BI the main focus is about explaining the things that happened (the past). A lot of Data Scientists can ask question on things, nobody thought before and can give you insides in your data and your business people never noticed before. So again, these teams have to work together and we can’t give it just to the IT.

As Mico said a lot of BI projects stop on just presenting numbers, but as a decision maker your are more focused on actions. So the last step of creating the right actions out of my numbers & trends is often the missing part. But it is also not that easy to achieve, because in order to get it right, you first need the trust of the users and this has a lot to do with communication. So what has this to do with Visual Storyboarding?

Visual Storytelling is the common language that can break silos and bring people together to talk in the same language (http://visual.ly/science-storytelling) and focus on the important things. Mico presented some nice samples on slide 20 that you also find here:

Something that I also recognized in the keynote of day 1 was that a lot of great visualizations are still hand made by graphic designers and developers. So there is still a larger gap in the ease of use of tools and the quality of presenting data. This has also to do with a new kind of user experience that people also expect in business application that is more driven by consumer products and apps. 

Summary

For me it was great to had the chance to visit the PASS Business Analytics conference this year. California in general is a great place for that kind of conference. As already said it is a different kind of community. I learned a lot of new interesting thoughts on data analytics and get to know new interesting people. For me I take 3 main things away from this conference:

  • Data Analytics is still hard work. You have to leverage so many skills, also non-technical skills, to get it really right.
  • Visualization and Storytelling is something we will see more in the future and is for me definitely a way to go to close the last mile to the user.
  • I see many people catching up with R. R is more and more used in the community and also in business departments, so I think Microsoft made a big step in the right direction by acquiring Revolution Analytics.

Sources

PASS Business Analytics Conference 2015 – Day 1

Pic1This is my first time to visit the PASS Business Analytics Conference and so I want to take the chance to give a wrap up of my impressions of the first day. The conference takes place in Santa Clara and I personally think that California as a great place to be. The sunny weather makes it very easy to combine interesting and challenging sessions and an open mood of people for networking and get to know each other. I really like the very friendly and open nature of the people here.

Maybe I give you some more details on the conference itself. The clear focus of that conference is Data Analytics. It’s a very good mixture of technical, business and strategic sessions. The audience is very different to the typical and more technical PASS Conference in Seattle. Altogether there are 600 participants out of 24 countries and more than 60 hours of sessions with great speakers. Most of them I never heard before, but they really give you some new ideas or food for thought that you never be thought of before.

Pic2

Keynote

The keynote started with the PASS president Thomas LaRock, followed by a great talk of Carlo Ratti, the Director of the MIT Senseable City Lab. The MIT Senseable City Lab is focused on who sensors are changing cities into real-time cities with lot’s of new opportunities. Today cities are already covered with digital sensors, so that people can react on it. There is an estimation that by 2020  about 50 billion connections need to handled in the world. Most of these connections will be sensors and devices than mobile phones. He showed several examples how cities are covered with digital sensors, react on it and become more smart cities these days. There are several research projects that the MIT is doing in that area like:

Mobility and Smart Cities

The Hub Cab is a project started on analyze Taxi Peaks in New York and try to bring in shared mobility. Beside the MIT also Audi and GE are part of that project, which makes me, as a German, also proud of that we are part of that innovation.  He presented some interesting numbers that although safe driving cars are on the way only 20% of the cars are need today for our mobility. An average car is only used 5% of the time so there is a huge opportunity to make mobility more efficient, especially in large city, also in order enhance the quality of live. So sharing is much more intelligent and what if we take the self-driving experience and the the sharing concepts together and even think about how traffic is organized. Do we still then need traffic lights? There are some nice concepts and researches on self-driving intersection that organizes traffic much more intelligent and efficient as traffic lights did for the last 100 years. This is a big opportunity for all of us.

The Copenhagen wheel

Another interesting and fascinating thing that came out of a MIT research project is the Copenhagen Wheel. This is a complete new way of bicycle mobility and a great example how sensors and technology can help us to innovate new experiences. And the best way to understand it, is to watch the video: https://www.youtube.com/watch?v=S10GMfG2NMY

Follow the trash

One last interesting project to me was the “moma-followup”. The idea is to follow obsolete electronics, that is normally just thrown away, across the world for recycle or reuse. In order to do this the MIT developed two different types of self-reporting technologies that are coupled with the obsolete electronics and than see what happened: https://www.youtube.com/watch?v=5fHZYyOZU4c

Summary

For me the first day really gave me some new ideas what is going on in Data Analytics. I think it is an amazing time to see, what can be achieved with technologies in these days and this is not always just to earn more money. It is interesting to see how fast the technology adoption happens and what role these great Universities like the MIT, Boston, Harvard, Stanford and many others play in that area. So a really great day one and I will follow up on day 2.

Sources:

Recap SQL Server Conference 2015 – A Kickoff to new era

After 3 fantastic days at the SQL Server Conference 2015 in Darmstadt I would like to take the chance to take a short recap. First of all I would like to say thank you to our German PASS Community for organizing this great conference and to all the volunteers, sponsors and speakers that helped to make this happen. In 4 tracks and together with 42 international speakers we delivered for our conference visitors a great selection of sessions, half of them in English and with lot’s of MVPs on board.

Especially this year I had the feeling that there is a shift that is happing in the market. People are very interested in new technologies especially in the area of Cloud Computing and Data Analytics.  I personally believe that this year we will see a big movement in the BI business: https://nexxtjump.com/2014/07/22/a-call-to-all-data-engineers-and-bi-workers-2/

I was also happy to give a talk for our great community and visitors about “SAP HANA, Power Pivot, SQL Server – In Memory-Technologien im Vergleich”. You can find my slides on slideshare: http://www.slideshare.net/marcelfranke/in-memorytechnologien-im-vergleich-sql-server-konferenz-2015

 

So I personally believe that this year will be a great one for all of us with lot’s of new stuff to learn and great projects to come. In the meantime we can all remember some very special moments of this conference. Some of mine are shared below, the rest you can find on Flickr: https://www.flickr.com/photos/dirkhondong/collections/72157650641795156/. Thanks to our Paparazzi Dirk to keep and save these moments for us.
 

image

 

image

 

WP_20150203_18_51_15_Pro

 

image

 

image

 

image

 

Eingebetteter Bild-Link

Big Data = Big Fun – The worldwide first Microsoft carnival session

imageI think today was a very historic day. Ralph, de Bräd,  Kemperdick (Microsoft Germany), organized the worldwide first carnival session at Microsoft in Cologne. Together with some other Jecks (Hilmar, Christos, Tillman and me) we presented some funny sessions about Big Data and we had a lot of fun together with our guests and visitors. Thanks to Ralph and Microsoft for organizing this and to all the guests who joined. We all agreed that this year was only the small start of a tradition that will hopefully fill pavilions and arenas in the next years. And for the rest of you who couldn’t join here are some impressions.

In this sense…Kölle Alaaf!

WP_20150212_10_05_21_Pro

WP_20150212_14_57_52_Pro

WP_20150212_12_21_44_Pro

WP_20150212_12_28_59_Pro

WP_20150212_12_29_29_ProWP_20150212_18_38_09_ProWP_20150212_14_29_38_Selfie

WP_20150212_15_03_59_Pro