Honolulu learning event: Basic SQL

You are welcome to attend my course at the Pacific Center for Advanced Technology Training (PACTT). Please register at the PCATT.org site.

Summary:

Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about databases and the basics of Structured Query Language (SQL) including sorting, distinct, and aggregate functions and grouping of data. All SQL statements will initially be written from one table. Most practical, modern and relational databases will include a large number of tables. SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course.


New in 2015: You will also get an introduction to Power Shell.

Dates: (Section 1) Section 1 - Feb 17 - Mar 12, 2015; TTh; 5:30 pm - 7:30 pm; 10 mtgs.
Venue: Honolulu Community College;

Note: SQL Server 2012 Express will be used and a knowledge of SQL Server is not assumed. Students will be using the free version.

For details : Access http://pcatt.org/index.php/search and look up the course or write to:
Hodentek@live.com with course name in the Subject line.

Honolulu Technical Training: SQL and SQL Server Reporting Services 2012

The two non-credit courses on SQL and Reporting Services offered by the Pacific Center for Advanced Training are going to start soon. Register and upgrade your database skills.

Course content and more details here:

http://hodentek.blogspot.com/2013/12/sql-training-in-honolulu-introduction.html

Troubleshooting the connection to a Contained Database in SQL Server 2012

Contained databases are the best choice if you have migration in mind and with
SQL Servers both in-house and the cloud, migration will always be an important
item to consider.

Read this article for the benefits of Contained databases:
http://stackoverflow.com/questions/5932293/what-is-the-real-benefit-of-
contained-databases

SQL Server 2012 only supports partially contained databases and in future
versions this may change.

You can create a partially contained database in SQL Server 2012 Express using
either T-SQL or SQL Server Management Studio.

Creating a contained database
1.The first step is enabling containment in the Server. It is assumed here that
you have an instance of SQL Server 2012 Express installed on your desktop/laptop.

Right click the server and click on Properties to open the Server Properties
window. Click on Advanced in the Select a Page list.
The Advanced Page is displayed and here you can enable containment as shown.

Right click Databases node and click New Database... to open the New Database
window.

• In the General page you can provide a name for the database, herein HContained.
• In the Options page pick Partial for Containment type as shown.

The database will be created as shown in the Server (local)/PCATT owned by
Hodentekwin7\mysorian, the computer administrator.

2. Now you need to create a User with a password (which means the server
authentication should be SQL Server and Windows Authentication mode as shown in
the Server properties page):

3. Click Security node of the HContained database to expand and right click Users
node and click New User....

This opens the Database User - New window where you provide a username and a
password (herein huser, password:resuh). By default the new user will neither
own any schema,nor has any membership in database role. For him to be able to
connect, he should be the owner of db_owner schema. The new user should also
have login for the computer.

Here is how the user huser was created
USE [HContained]
GO
/****** Object:  User [huser]    Script Date: 1/27/2014 10:40:01 PM ******/
CREATE USER [huser] WITH PASSWORD=N'hÀ]™t#Ux ÐöÀ Ó <Ö—Y¡nWâýgÁ˜éayÅÄ',
DEFAULT_SCHEMA=[dbo]
GO

Connecting to partially contained database HContained
1. Bring up the Connect to Server dialogue as shown. Change authentication to SQL
Server Authentication, enter the user created earlier(huser, password resuh)

and if you just click connect you will get an error as huser has no server
login.

2.Now click Options button in the Connect to Server dialogue.
Click the download handle on Connect to database and replace <default> with
HContained as shown.

3. Now click Connect.
You immediately get connected to Hcontained database as shown.

This was achieved after taking care of some errors that cropped up. The fixes
were as follows:
1. SQL Server Management Studio version 11.0.2100.60 was used. Attempts to
connect to HContained resulted in the following error message. The fix to
rectify the situaion is also shown in the image.

2. The above fix still did not allow connecting to the Contained database. The
reason appears to be that the RTM version of SSMS does not support connection as described. In order to connect, the SP1 version of SSMS 2012 Express was needed. There are atleast two ways to get the SP1 version of SSMS 2012. Install SQL Server 2012 Express SP1 Advanced version which installs the client tools or install SSMS 2012 Express SP1.

Follow this link to download SQL Server 2012 Express SP1 files:
http://msdn.microsoft.com/en-us/evalcenter/hh230763.aspx
You will be able to download any of the following:

• LocalDB (MSI installer)
• Express (Containing only the database engine)
• Express with Tools (with LocalDB, Includes the database engine and SQL Server Management Studio Express)
• SQL Server Management Studio Express (Tools only)
• Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search)

Note that SSMS 2012 Express RTM is 11.0.2100.60 and SSMS 2012 Express SP1 version is 11.0.3128.0.




 

Deploying DAC to SQL Server 2014

The acronym DAC in SQL Server has many different meanings as in the following bullet list:

• DAC - Data-tier Application
• DAC - Dedicated Administrator Connection
• DAC - Data Access Components
• DAC - Data Application Component

This post describes deploying a Data-tier Application extracted from SQL Server 2012 Express database to SQL Server 2014 CTP1 (should work with CTP2 as well). The two servers are on two partitions on a x64 bit Windows laptop, with SQL Server 2012 on Windows 7 Partition and CTP1 on Windows 8 Preview partition.

There are no issues for this deployment and the next few images shows the stages of deployment wizard while deploying to SQL Server 2014 CTP1. We start with a .dac file created on Windows 7 partition and copied over to the desktop on Windows 8 partition.

 

The steps are very clear and you may also review the following for more detailed discussion of DAC with earlier versions on older OS.


https://www.packtpub.com/article/working-with-data-application-components-sql-server-2008-r2
http://hodentekmsss.blogspot.com/2010/07/sql-server-2008-r2-and-data-tier.html

Training Event Honolulu: Introduction to Structured Query Language (SQL)

You are welcome to attend my course at the Pacific Center for Advanced Technology Training (PACTT).

Summary:
Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about databases and the basics of Structured Query Language (SQL) including sorting, distinct, and aggregate functions and grouping of data. All SQL statements will initially be written from one table. Most practical, modern and relational databases will include a large number of tables. SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course.

Dates: (Section 1) Nov 5 - Dec 10; Tuesdays and Thursdays; 5:30 pm - 7:30 pm; 10 meetings.

Venue:  Honolulu Community College
874 Dillingham Blvd Honolulu, HI 96817-4598 Phone: (808) 845-9211
Official Website: honolulu.hawaii.edu/Venue: Honolulu Community College;

Course code: 134F1TSQL001A
Note: SQL Server 2012 Express will be used and a knowledge of SQL Server is not assumed.

For details : Access http://pcatt.org/index.php/search and look up the course or write to:
Hodentek@live.com with course name in the Subject line.