Category Archives: SQL

Building SharePoint 2016 development environment – Part 8 – Installing SQL 2016 ready for SharePoint 2016.

Posted on by

A few years ago I wrote “Build your SharePoint 2013 development machine on Windows Server 2012” series, I mainly work in the cloud now, but as the blogs was so popular, I thought I would create a new series for the newer version of SharePoint.

You can access other parts of this post below.

Setting up directories for SQL.

We are going to set up following directories on the d: drive.

  • Bring up Explorer by click on Windows Key + E
  • Double click on the D drive.
  • Create the following directories.
    • D:\SQL
    • D:\SQL\Data
    • D:\SQL\Logs
    • D:\SQL\Temp\Data
    • D:\SQL\Temp\Logs
    • D:\SQL\Backup

Installing SQL Server 2016 Standard Edition 64 bit

From the part 5 of these blogs we have already inserted the ISO file into the virtual machine. By opening Explorer inside the virtual machine, you should see DVD Drive with the SQL disk inserted.

  • Double click on the DVD drive, or run Setup.exe
  • The SQL Server Installation Center will open up. On the left hand side, click Installation.
  • Click on New SQL Server stand-alone installation or add features to an existing installation
  • The product key should already be entered, click Next, accept the License Terms. Click Next.
  • Tick Use Microsoft Update to check for updates (Recommended). Click Next.
  • Setup install rules will identify any problems that might occur when installing SQL Server Setup support files. They should all passes. (Ignore warnings) Click Next.
  • On the Feature Selection Page. Select Database Engine Services, Full-Text and Semantic Extractions for Search. Click Next.
  • On the Installation Rules page click Next.
  • On the Instance Configuration page click Next.
  • On the Server Configuration page, change the SQL Server Agent Start Up Type to Automatic. Also change the SQL Server Database Engine to use your SP_SQL account, and enter the password. Pa55w0rd
  • Click Next
  • On the Database Engine Configuration page, select Mixed Mode and enter the password and confirm password as Pa55w0rd. And Click Add Current User.
  • On the Data Directories tab, change the location of the directories.
    • Data Root Directory – d:\SQL
    • User Database Directory – d:\SQL\Data
    • User Database Log Directory – d:\SQL\Logs
    • Backup Direcotory – d:\SQL\BackUp
  • On the TempDB tab
    • Data Directories – d:\SQL\Temp\Data
    • Log Directory – d:\Sql\Temp\Logs
  • On the FILESTREAM tab, tick Enable FileStream for Transact-SQL access and Enable FILESTREAM for file I/O access. Click Next.
  • On the Ready to Install page, click Install
  • After installation your SQL Server 2016 is installed. You may be asked to reboot.

Installing SQL Server Management Tools

It seems that SQL 2016 have now separated out the SQL Server and SQL Server Management Tools. So this is an additional step to carry out.

  1. Back on the SQL Server Installation Center (run setup.exe from DVD drive if not showing), click on Install SQL Server Management Tools
  2. This takes you off to a website address https://msdn.microsoft.com/en-us/library/mt238290.aspx to download the SQL Server Management Studio (SSMS) seperately.
  3. Click the download link, and run it. (Or Save and run it afterwards) Over 800MB in size.
  4. Click Install.
  5. Once installed, you will get a success message. Close this screen.

Configuring SQL Server 2016

  1. From the start screen type SQL Server Configuration Manager and select the application.
  2. Click to expand SQL Server Network Configuration (not the 32 bit), and choose Protocols for MSSSQLServer, and ensure TCP/IP and Named Pipes are enabled. To enable them right click them and select Enable. Click OK at the warning.
  3. Close the SQL Server Configuration Manager.

Apply the DisableLoopbackCheck Registry Fix

  1. Click the Windows PowerShell icon in the Taskbar.
  2. Run the following PowerShell commands, pressing [Enter] after each one:
    $regKeyPath = "HKLM:\System\CurrentControlSet\Control\Lsa"
    $key = "DisableLoopbackCheck"
    New-ItemProperty –Path $regKeyPath –Name $key –Value "1" –PropertyType dword
    view raw DisableLoopBackCheck.ps1 hosted with ❤ by GitHub

Giving SP_Setup account access

Note: If you continue without doing the DisableLoopback Check registry you will get an error message saying “Login Failed. The login is from an untrusted domain and connot be used with Windows authentication”

  1. From the start screen type SQL Server 2016 Management Studio and open the application.
  2. Change the server name to SQL2016 (we set a host name in an earlier post)
    and logon as Windows authentication.
  3. Expand Security from the object explorer and right click Logins and select New Login…
  4. Next to the Login Name click Search
  5. Ensure the Location is set for Entire Directory, and then type SP_Setup and click Check Names.
  6. Click OK.
  7. On the left hand panel, select the Server Roles page, and tick dbcreator, securityadmin and sysadmin. Then click OK.

Now we can think about Installing SharePoint 2016.

Now is another good time to shut down both Domain Controller, and the SharePoint machine and take a checkpoint/snapshot before continuing.

Temporal Tables in SQL 2016 and SQL Azure

Posted on by

Have you ever been asked to create a History/audit table for your database? Do you need to? If so, then read this blog post on the awesome feature now built into SQL 2016 and SQL Azure.

What is a temporal table?

A temporal table is a new type of user table in SQL Server 2016 and SQL Azure. These tables allow a point-in-time analysis by keep a full history of data changes, without the need of custom coding using triggers etc. You can create any new user table as a temporal table, or convert an existing table into a temporal table. By converting an existing table to a temporal table you will not need to do anything to any stored procedures or T-SQL statements to allow your application to continue working, it will just continue working, while storing the history data of any changes. These tables can also be known as system-versioned temporal tables because each row is managed by the system.

Every temporal table has two explicitly defined datetime2 columns. These columns are referred to as period columns and are used by the system to record period of validity for each row whenever a row is modified. A temporal table also has reference to another table with the same schema as itself. This is the history table and automatically stores the previous version of the row each time a row in the temporal table gets updated or deleted. This allows the temporal table to remain as the current table, and the history table to hold… well the history data. During temporal table creation users can specify existing history table (which must match the schema of the temporal table) or let the system create a default history table.

How does temporal work?

All current entries are stored within the Temporal table with a Start time and non-ending End time. Any changes will cause the original row to be stored in the history table with the start time and end time for the period for which is was valid.

Let me show you an example.

On first input on a row, the value would be entered only into the Temporal table.

Temporal

ID Value StartTime EndTime
1 My First Value 2016-05-01 10:26:45.15 9999-12-31 23:59:59.99

History

ID Value StartTime EndTime

On Update to ID 1, the original inputted value is entered into the history table with the EndTime updated to match when the Update took place, and Temporal table is updated with the updated value and the new start time.

Temporal

ID Value StartTime EndTime
1 My Second Value 2016-05-14 14:54:44.54 9999-12-31 23:59:59.99

History

ID Value StartTime EndTime
1 My First Value 2016-05-01 10:26:45.15 2016-05-14 14:54:44.54

On second update to ID 1, again the current value is entered into the history table with the EndTime updated to match when the Update took place, and the Temporal table is updated with the new version and new start time.

Temporal

ID Value StartTime EndTime
1 My Third Value 2016-05-24 01:59:41.82 9999-12-31 23:59:59.99

History

ID Value StartTime EndTime
1 My First Value 2016-05-01 10:26:45.15 2016-05-14 14:54:44.54
1 My Second Value 2016-05-14 14:54:44.54 2016-05-24 01:59:41.82

On deletion of ID 1, the current value is entered into the history table, with the EndTime updated to match when the row was deleted. The row is then removed from the Temporal table.

Temporal

ID Value StartTime EndTime

History

ID Value StartTime EndTime
1 My First Value 2016-05-01 10:26:45.15 2016-05-14 14:54:44.54
1 My Second Value 2016-05-14 14:54:44.54 2016-05-24 01:59:41.82
1 My Third Value 2016-05-24 01:59:41.82 2016-06-01 13:12:17.72

Creating or converting exiting table to a temporal table.

You can create a temporal table by specifying the Transact-SQL statements directly as show below. I recommend using SQL Management Studio 2016 which can be obtained and downloaded from here. You do not need a SQL Server license to install and use this, and it can be used with SQL Azure.

By using SQL Management Studio 2016, you can obtain the correct T-SQL by right clicking Tables > New > Temporal Table > System-Versioned Table..


I’m going to create an Employee Table.

CREATE TABLE dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
-- This point below is the Period/Temporal set up on the table.
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
GO

If I was going to convert my existing Employee Table into a Temporal table, I would use the following T-SQL Statement

ALTER TABLE Employee
ADD
    ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN
        constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , ValidTo datetime2 (0)  GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
ALTER TABLE Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
GO

AS you can see from above, SQL Management Studio indicates the System-Versioned and History table.

Inserts, updates and deleting data

When you come to doing your Inserts, Updates and Deletes there are no changes to T-SQL code, you would perform all against the Temporal table, (Employee table in my case). The T-SQL code below is demo code, that inserts 3 people a minute apart and then every 5 minutes something else will happen to the data. Either an update, inserting a new record, or delete.

--Create Lisa Fane
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (1234,'Lisa Fane','Sales Rep','Sales','Hertforshire', 25000)
GO

WAITFOR DELAY '00:01'
--Create Dan Wilson
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (2435,'Dan Wilson','Developer','Development','Kent', 35500)
GO

WAITFOR DELAY '00:01'
--Create David Hamilton
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (3445,'David Hamilton','Developer','Development','Croydon', 20000)
GO

WAITFOR DELAY '00:05'
--Update Lisa Fane with new job title and payrise.
UPDATE [dbo].[Employee]
SET  [Position] = 'Lead Sales Rep',[AnnualSalary] = 32000
WHERE EmployeeID = 1234
GO

WAITFOR DELAY '00:05'
-- Give Lisa Fane a Pay Rise.
UPDATE [dbo].[Employee]
SET  [AnnualSalary] = 33000
WHERE EmployeeID = 1234
GO

WAITFOR DELAY '00:05'
-- Give Dan Wilson a new job title and payrise
UPDATE [dbo].[Employee]
SET  [Position] = 'Development Manager',
[AnnualSalary] = 45500
WHERE EmployeeID = 2435
GO

WAITFOR DELAY '00:05'
--Employ Lucy Williamson
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (8875,'Lucy Williamson','Project Management','PMO','Sutton', 20000)
GO

WAITFOR DELAY '00:05'
--Lisa Fane change address
UPDATE [dbo].[Employee]
SET  [Address] = 'Barnet'
WHERE EmployeeID = 1234
GO

WAITFOR DELAY '00:05'
--Adam Crane joins the team
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (4454,'Adam Crane','Sales Rep','Sales','Islington', 26000)
GO
WAITFOR DELAY '00:05'

--David Hamilton has a payrise
UPDATE [dbo].[Employee]
SET  [Position] = 'Manage Services',[AnnualSalary] = 20500
WHERE EmployeeID = 3445
GO

WAITFOR DELAY '00:05'
--Lucy Williamson left the company.
Delete From Employee
Where EmployeeID = 8875

Running the above script takes about 30-35 mins.

Querying Temporal data

To obtain the current information in the Temporal table, there is no changes to your typical SQL Select statements.

SELECT * FROM Employee

As you can see from the above image, the results are as if it’s not a temporal table.

To view history data, there is a new clause you can use within the SELECT FROM statement. This is the FOR SYSTEM_TIME clause with 5 temporal-specific sub-clauses to query data across the current and history tables. This new SELECT statement syntax is supported directory on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

View All Data

Use the following command to see current and past records, the IsActual flag indicates if the row is current. This query is also useful as a view so that BI tools such as PowerBI can display a graph over time.

SELECT Name, Position, Department, [Address], AnnualSalary, ValidFrom, ValidTo, IIF (YEAR(ValidTo) = 9999, 1, 0) AS IsActual FROM Employee
FOR SYSTEM_TIME All
ORDER BY ValidFrom

Between two dates

Using BETWEEN <startDateTime> AND <endDateTime> will return rows that were active at least for a portion of period between the two times.

SELECT * FROM Employee
FOR SYSTEM_TIME
BETWEEN '2016-06-18 10:27:00' AND '2016-06-18 10:47:00'
ORDER BY ValidFrom

Contains two dates

Using CONTAINS IN (<startDateTime>,<EndDateTime>) will return rows that were only active within a period (and not outside it). This only queries the history table. As you can see below Lisa Fane was updated 3 times within the time period.

SELECT * FROM Employee
FOR SYSTEM_TIME CONTAINED IN ('2016-06-18 10:25:00', '2016-06-18 11:50:00')
ORDER BY ValidFrom

Point in time search

Using AS OF <dateTime> will return how the database looked at that given moment in time. Below are multiple statements which returns results from those points in the database. I’ve highlighted within the next result set what has changed. This type of query is perfect for BI tools such as Power BI to query the data 24 hours, 7 days, 30 day etc.

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:28:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:31:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:36:00'
ORDER BY EmployeeID


SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:46:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:56:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 11:01:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 11:06:00'
ORDER BY EmployeeID

References

I found quite a bit of good information available to help me understand Temporal tables, I have listed the main sites below.

Temporal Tables – https://msdn.microsoft.com/en-IN/library/dn935015.aspx 

Getting Started with Temporal Tables in Azure SQL Database – https://azure.microsoft.com/en-us/documentation/articles/sql-database-temporal-tables/

Getting Started with System- Versioned Temporal Tables – https://msdn.microsoft.com/en-us/library/mt604462.aspx

Temporal in SQL Server 2016 (Video) – https://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016 


Exporting and Importing SQL Azure database in different Tenants

Posted on by

This was easier than I thought it was going to be. Using purely point and click, and Microsoft Azure Storage Explorer.

To be able to backup/export a database you need to have an Azure Blob Storage. If you don’t have one the steps below will show you how to create one.

Creating an Azure Blob Storage

  • Open up https://portal.azure.com and log in with your credentials, on the tenant where your SQL server source is.
  • Click New and select Data + Storage and then select Storage account
  • On the Create storage account blade you will be asked the following information:
    • Name: <Give a unique name>
    • Deployment model: Resource manager
    • Account Kind: Blob Storage
    • Performance: Standard
    • Replication: Locally-redundant storage (LRS) <- This may be different for you. I’m just doing a simple export and restore, not planning on keeping this storage.
    • Subscription: <Your subscription>
    • Resource group: Create New <- You might wish to use an existing resource group.
    • Resource Group Name: <Resource Group Name>
    • Location: <Your closest location>
  • Click Create
  • This will take a small amount of time while Azure creates this storage

Exporting Source Database

  • If not continuing from last step, open up https://portal.azure.com and log in with your credentials, on the tenant where your SQL server source is.
  • Go into SQL Database, and select the database you wish to export.
  • At the top of the blade there is a menu button item called ‘Export’. Click this button.

  • On the Export database blade, you will be asked the following information:
    • File name: Give a meaningful export name.
    • Subscription: Select the subscription that you can find your storage account in that you created earlier.
    • Storage: Select the storage account you created earlier.
      • Containers: Add a container name, and give it private access type, then select it.
    • Server admin login: Provide the Server Admin Username.
    • Password: Provide the password for the Server Admin.
  • Click OK.
  • Once you have clicked OK, your request to export the database is sent Azure, and is put into some sort of Microsoft Azure queue behind the scenes. Here you have to wait until the job has run, this can take some time. (Mine took 15 mins before complete) Please see Viewing the Import/Export history later in this blog post for job status.

Downloading the Blob file.

After the SQL export has completed, you will need to download the blob file so that you can then upload it to your destination tenant. To do this follow these steps:

  • In https://portal.azure.com select your Storage account where you exported SQL to.
  • Under the container selector you should find the container you created when exporting SQL data. Select this.
  • This container blade shows you all the files within this container. As I’ve just created it for this purpose the only file within here is my export file. Select this file.
  • Then click the download button.

Uploading export file to new tenant storage.

Before you can upload your export file to your new tenant, first you will need to ensure you have a storage account to upload to. If there isn’t one, follow my previous steps in this blog about creating an Azure Blob Storage.

Once you have a blob storage on your destination tenant, download and open Microsoft Azure Storage Explorer

  • Sign in with your destination tenant credentials.
  • Select the Storage account and then blob container.
  • Click Upload.
  • Upload your export file.

Importing to Destination Database

  • Open up https://portal.azure.com and log in with your credentials, on the tenant where your destination SQL server is.
  • Go into SQL Servers, and Add/Select the SQL server you wish to import the database too.
  • At the top of the blade there is a menu button item called ‘Import database’. Click this button.

  • On the Import database blade, you will be asked the following information:
    • Subscription: Select the subscription that you can find your storage account in that you created earlier.
    • Storage: Select the storage account you created earlier.
      • Containers: Select the Container
        • File: Select the export file.
    • Select Pricing Tier: <Select a pricing tier>
    • Database name: <Name the database>
    • Collation: Leave as is, or change if you require too.
    • Server admin login: Provide the Server Admin Username for this tenant.
    • Password: Provide the password for the Server Admin.
  • Click OK.
  • Once you have clicked OK, your request to import the database is sent Azure, and is put into some sort of Microsoft Azure queue behind the scenes. Here you have to wait until the job has run, this can take some time. (Mine took less than two minutes to import.) Please see Viewing the Import/Export history below for job status.

Viewing the Import/Export history.

After you have imported/exported a database, you can view the progress of the request by following these steps:

  • In https://portal.azure.com select SQL servers
  • Select your server where the import/export is taking place.
  • If you scroll down to Operations on the Server blade, you will see a tile called Import/Export history. Click this.



Simple SQL statement to see membership within a database

Posted on by

In SQL Azure if you connect up Microsoft SQL Server Management Studio, you have to do everything using SQL statements, there is no ability to point and click your way through creating accounts, memberships, new tables etc. I’m sure a good DBA would tell me that this is the correct way of building any database. Unfortunately, (or fortunately) I’m not a DBA, and I like point and click tools.

So the other day I was having a problem seeing what accounts had what access to a given database. I found running this SQL statement on a given database gave me the information I needed. I have written this blog post today mainly so I have reference to this in the future.

SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

Fig 1. Example results.

Entity Framework Error – The server principle “user” is not able to access the database “master” under the current security context

Posted on by

I’m pretty new at Entity Framework and really haven’t even skimmed the surface of what it can do. But as a newbie, I encountered this error right from the start and my bet is other people have too. Let me first explain the setup of my environment.

I’ve created a code first entity framework application. I have created an Azure Server and database (doesn’t have to be Azure database), and created a SQL user so that they are db_owner on the database. On first running of my application I hit the following error: –

“The server principal “demouser” is not able to access the database “master” under the current security context.

Database ‘master’ on server ‘xxxxxxxx.westeurope1-a.worker.database.windows.net’ is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of ‘6214BE0A-7962-4728-B0BE-B129401F98B0’.

Login failed for user ‘demouser’.”

So why does it need access to the master database?

It requires this to log into SQL server.  If the user database doesn’t exist and you want the code to create your database, you will also need to give your account dbo access to your master database, I don’t recommend doing this.

What is the solution?

Add the account to the master database so that it has public access. The SQL Script below is the full script I now use to create the account. The line highlighted is the line you need to run on against your master database to fix your error message.

use [master]
--Run within the master database.
-Create the Login account.
CREATE LOGIN demouser WITH password 'P@ssword123'
--This is the line you need
CREATE USER demouser FOR LOGIN demouser

--Run within your Database
use [demoDatabase]
CREATE USER demouser FOR LOGIN demouser
ALTER ROLE [db_owner] ADD MEMBER demouser

SQL Azure – Connecting to SQL Azure server and database using the browser

Posted on by

In my last post I showed you how to connect to your SQL Azure using SQL Management studio. This post is showing you how to manage your SQL Azure Server and Database within the browser, but please note, I’m not sure how long this feature will be around due to the fact that if you create a server using the V12 (Latest SQL Database), you will not be able to view your database in the browser. Also that the page uses Silverlight to render, and that was killed off in 2012.

To connect through the browser you have two choices:
1. You can log into your Azure Portal, and navigate to your database. At the bottom of the Quick Start page for your database there is a link “Design your SQL database” this will open up another page where you can log into your SQL azure.

2. The other way of access the login page for SQL Azure in the browser is by navigating to the following URL, replacing servername with the name of your server. https://[servername].database.windows.net you will be presented with a login screen.

Using your SQL admin account you can log in. (The login doesn’t have to be the SQL admin, it can be any SQL user account that has already been created)

Once you have logged in, you can then navigate to your database, and click the pencil icon to design.

Once in design mode you could create a new table, view, stored procedure. As I’m not a true SQL developer, this design view is what I’m used to within SQL Management studio. But when connecting to SQL Azure with SQL management studio this functionality is missing.

If you are a true SQL Developer, there is nothing stopping you clicking New Query in the menu bar and writing T-SQL Statements to create your tables, columns, stored procedures etc.

SQL Azure – Configuring SQL Azure so you can connect from Local SQL Management Studio

Posted on by

In my second post of my SQL Azure series, I’m going to show you how to connect to you SQL Azure server database from SQL Management Studio. You can only connect to SQL Azure from somewhere that is not a Windows Azure service by defining IP Firewall rules.

 

Configuring SQL Azure Firewall rules.

  • Open up the Management Azure Portal and log in using your username and password.
  • Navigate to the SQL databases area.
  • Then for your database, click on the Server Name.

  • Click on the Configure Tab.
  • Find out your external IP address(s) that you will be connecting from, the screen will tell you your current IP address at the top of the screen.
  • In the Rule area below, Put in a Rule Name and then fill out either a range (if using dynamic/multiple IP locations), or the given IP address(es) you will be connecting from.
  • Click Save.

Connecting from a Local SQL Management Studio

You will need to use SQL Server 2012 or above to connect to SQL Azure. Alternatively you can download SQL Server 2014 express for free and download the MgmtStudio Download.

  • Once you have opened the Management SQL Studio, put in the Server name with .database.windows.net at the end. (E.g., Server Name is ctwsc5d11a, then the Server name you put in is ctwsc5d11a.database.windows.net
  • Change the authentication to SQL Server Authentication.
  • Login using the UserName and Password you created when setting up the SQL Azure Server and Database.
  • Click Connect.
  • Once connected you can create Tables, Store Procedures etc. using point and click (which creates valid T-SQL templates) or T-SQL Statements*

(*Please note, not all T-SQL statements that work in Standard SQL works in Azure. For example SELECT INTO doesn’t work unless you predefine the Table and columns first, also there is no designer mode when creating Azure SQL Tables in SQL Management Studio, all you can do is get the T-SQL template to create the table)

SQL Azure – Creating SQL Azure Server and Database in Azure Management

Posted on by

As I’ve recently started using SQL Azure recently at work, and although the steps are basic really to create a database and get up and running, I thought I would post a couple of “simple 101” blogs just showing you how. This first post of my SQL Azure series will show you the steps to create a SQL Azure Server and database using the good old “point and click” method.

  • Open up the Management Azure Portal and log in using your username and password.
  • Click on New > Data Services > SQL Database > Custom Create.
  • On the dialog window, fill in the boxes.

  • Give the database a name. This is the Database name not the server name. Azure will automatically assign the server name.
  • At the bottom of the page, change the Server tab to New SQL database server, as soon as you do this a second page to the Wizard will appear.
  • Click the next arrow.
  • On the second page fill in the Database Server settings. You need to provide a Login name, password and region. The Login name can be anything you want, however think of this as your SA username and password. (E.g, SQLAdmin)


  • At the bottom of Page 2 wizard is two check boxes:
    • Allow Windows Azure Services to Access the Server – With this ticked it allows you to connect to SQL Azure database from other Azure services (With the username and password). This means any Azure services including Azure services from a different tenant.
    • Enable Latest SQL Database Update – This will give you the latest and greatest version of SQL Azure, sometimes this will be a preview version. At the time of writing this post, V12 was the latest update and it had better T-SQL compatibility. For example I know using V12 allows you to create a table that has no clustered index. This feature is especially helpful for its support of the T-SQL SELECT..INTO statement which creates a table from a query result.
  • Click the tick.
  • Once created you will be given a server name. (I’ve hidden mine for security reasons)


Building Your SharePoint 2013 development machine on Windows Server 2012 – Part 5 – Configuring SQL 2012 Ready for SharePoint 2013

Posted on by

UPDATE: SharePoint 2016 development machine

I am doing a collection of blog posts, you can access the other parts of this post below.

When I started planning the 10 parts of the blog, the one bit I forgot about was Kerberos. So I have squeezed part of it in here. As I am a developer and not really an IT-Pro I had to look up information on the web to help me write this bit on Kerberos and speak to the IT Pro at my work. My references are blksthl blog and my work colleague Adam McEwen, who blogs you can access via these links.

Creating the Accounts required.

First we need to create 4 different accounts.

SP_Farm – SharePoint Farm Account

SP_Content – SharePoint Content account for the Content Databases.

SP_Services – SharePoint account for the Shared Services within SharePoint.

SP_SQL – The account to run SQL with.

  1. In the Start Menu, type Active Directory Users and Computers. Select the application.
  2. Expand the tree in the left hand pane to see the Users OU. Select the Users OU.
  3. Right Click on User and select New > User. Create a new user called SP_Farm. Set the Full Name and Log on name to SP_Farm. Click Next.
  4. In the password dialog screen, enter the following and click Next.
    1. Password and Confirm Password as: Pa55w0rd
    2. Untick User must change password at next logon.
    3. Leave User cannot change password as unticked.
    4. Tick Password never expires
    5. Leave Account is disabled as unticked.
    6. Click Next. Then click Finish.
  5. Repeat the steps 1-4 for the remaining accounts (SP_Content, SP_Services and SP_SQL).

Setting up Kerberos for SP_SQL account.

We will need to set up Kerberos for the other accounts, but this section of blog is getting your SQL ready for SharePoint 2013. Therefore at this point we will just set SQL up for Kerberos.

Name Resolution.

  1. In the Start Menu, type DNS. Open the application to the DNS Manager.
  2. Expand the forward lookup zones container in the left panel.

  3. Right click on the zone cannonfodder.local and click on a New Host (A or AAAA).

  4. Type in the name of the record. In this case we are going to name our SQL Server SQL2012. Set the IP address at the IP of the machine. We set this to a static IP in the last blog. (If you have forgotten you can find out by bringing up a command prompt and type ipconfig /all and look at the IP4 IP address.) Click on Add Host.

  5. Click on Done. You will then get a verification dialog, and after click OK to that, you will see the record has been created in the right pane of the DNS manager.
  6. To check that this is all working, open a command prompt, and type
    ipconfig –flusdns

    Then type

    Ping SQL2012

Service Principle Name

  1. Open a command prompt from the Start Menu by typing in cmd. Right click Command Prompt and along the bottom of the screen will be an option to Run as Administrator.
  2. Type the following in the command window. This will create the SPN for the service account paired with the host name.
    setspn –S MSSQLSvc/SQL2012:1433 CANNONFODDER\SP_SQL
  3. Type the following in the command window. This will create the SPN for the service account paired with the FQDN.
    setspn –S MSSQLSvc/SQL2012.cannonfodder.local:1433 CANNONFODDER\SP_SQL
  4. Now by typing in setspn –L CANNONFODDER\SP_SQL it will list all the SPN for that account.


Trust for delegation

As our server is a domain controller we do not need to set it up so that it is Trusted for Delegation. We can prove this by doing the following.

  1. From the Start Menu type Active Directory Users and Computers. Open the application.
  2. In the left hand panel, expand cannonfodder.local and select Domain Controllers.
  3. In the right hand panel you will see the computer CANNONFODDERSER.
  4. Right click this computer and select Properties. From the properties window, select the Delegation tab. As you can see this computer is already set to Trust this computer for delegation to any service (Kerberos only).

Configuring SQL Server 2012

As SQL was installed using a built in account, and also installed on the machine before it was sysprepped, SQL now needs to be configured to work properly on this server in preparation for SharePoint.

  1. From the start screen type SQL Server Configuration Manager and select the application.
  2. Click to expand SQL Server Network Configuration (not the 32 bit), and choose Protocols for MSSSQLServer, and ensure TCP/IP and Named Pipes are enabled. To enable them right click them and select Enable. Click OK at the warning.
  3. Now in the left hand pane, click SQL Server Services.
  4. For SQL Server (MSSQLSERVER) right click the service name and choose Properties.
  5. Select the Log On tab.
  6. Select this account: radio button and enter cannonfodder\SP_SQL and enter the password as Pa55w0rd. Click Apply, and Yes to restart the service, or Start the service if not already.
  7. Click OK and close SQL Server Configuration Manager.

Now we need to give cannonfodder\administrator access and update SQL Server 2012 local instance after the machine name has been changed.

  1. From the start screen type SQL Server Management Studio and select the application.
  2. Change the server name to SQL2012 and logon using SQL Authentication with the sa account using the password Pa55w0rd. If you try to log in at the moment as cannonfodder\administrator using windows authentication, you will fail to access SQL.
  3. Now give cannonfodder\administrator the correct security access.
    1. Expand Security and right click Logins to select New Login…
    2. Next to the Login Name click Search
    3. Type cannonfodder\administrator, and then click check names. Select the user administrator, not the group.
    4. On the left hand panel, select the Server Roles page, and tick sysadmin. Click OK
    5. Right click the database connection in Object Explorer and choose disconnect.
    6. Click Connect in the Object Explorer, choose database engine, and connect to SQL2012 using Windows Authentication, then confirm you can browse objects in the database server.
  4. Now we need to update the server name. If you Click New Query and type
    
Select @@SERVERNAME

    You will notice that the server name is incorrect.

    Click New Query and copy the script

    
sp_dropserver @@SERVERNAME
GO
sp_addserver 'CANNONFODDERSERVER', local
GO

    run the query.

  5. Restart the SQL server service, right click the SQL Server service, right click the database connection in Object Explorer, and choose Restart. You will then get a prompt asking if you want to restart the MSSQLSERVER service. Say Yes.

  6. Right click the database connection again, and choose disconnect.
  7. Click Connect to SQL2012 using Windows Authentication.
  8. Click New Query, and run the following script to confirm the instance name has been changed to reflect your chosen machine name:
    
Select @@SERVERNAME

  9. You can now close SQL Server Management Studio. No need to save the queries.

Now we are past the half way point, and we can now finally start installing SharePoint 2013!

Building Your SharePoint 2013 development machine on Windows Server 2012 – Part 3 – Building a base disk to use for differencing disks.

Posted on by

UPDATE: SharePoint 2016 development machine

I am doing a collection of blog posts, you can access the other parts of this post below.

Building a base disk to use for differencing disks.

Now as this is a single build machine, you might wonder why bother building a base disk for differencing? The reason is simply that it will save you time in the future, if you wish to expand your farm, or create another server at a later date. By doing this, you will find that all the default programs and updates are already on the machine.

What needs to go on here?

I would put on the base build everything that you probably will need on all your virtual machines. So I will be adding;

  • Windows Server 2012 Standard Edition 64 bit.
  • SQL 2012 Standard Edition 64 bit with SP1 – Basic set up with database and management studio
  • Office 2013 – Access, Excel, InfoPath, Lync, OneNote, Outlook, PowerPoint, Publisher, Visio and Word
  • PowerShell Plus from Idera
  • FireFox
  • Chrome
  • Visual Studio 2012 Ultimate
  • Fiddler

Creating your Virtual Machine.

  1. Open up Hyper-V Manager.
  2. Under Actions, click on New and select Virtual Machine. Click Next.
  3. Name your Virtual Machine, and your choice if you wish to store the virtual machine in a different location.

  4. Assign it some memory. At this Stage just 6GB will be enough for installing everything. Click Next.

  5. Configure the Network to point to the Internal LAN connection that we made in the previous blog.

  6. Name your Virtual Hard disk. Change the location if you wish, and I’d recommend creating a 400GB hard disk. (If you have room). We will split the disk inside the virtual machine into 100GB and 300GB for C and D drive. Click Next.

  7. Installation Options, you can select Install an Operating system from a Boot CD/DVD-ROM and in my case I have selected the Windows Server 2012 Image file.

Install Windows 2012 Server Standard Edition.

Inside your Hyper V manager you should now see your Virtual machine. In my case it is called Cann0nF0dderBase. By right clicking on the machine you can connect to it. A window will open. You can then click the Start Button.
As your virtual machine starts up it will load the Windows 2012 files.

  1. Configure the language. Click Next.
  2. Then Click Install Now.
  3. Insert your product key. Click Next.
  4. Select Windows Server 2012 Standard (Server with a GUI) and then click Next.
  5. Tick I accept the license terms. And then click Next.
  6. Select the Custom: Install Windows only (advanced) option.
  7. In the Where do you want to install Windows. Select Drive options (advanced) and create a new space of 102400MB. This will create a 100GB space. Accept the message that says Windows might create additional partitions for system files. You should now see Drive 0 Partition 1: System Reserved, Drive 0 Partition 2 and Drive 0 Unallocated Space. Select the last one and click New, then Apply. This will set up your drive space. Click Next, and Windows 2012 will start installing.
  8. Once it has finished installing itself, it will ask you for a password for the Administrator. I use Pa55w0rd. Click Finish.
  9. Now you can log in. To perform Ctrl + Alt + Del in a Hyper V Machine. Press Ctrl + Alt + End.
  10. Before you continue any further, install Hyper V Client tools. At the menu on the Virtual Machine Connection, click Action > Insert Integration Services Setup Disk. Then run the setup disk. You will find the disk has been attached as drive E within your virtual Machine. It will warn you about upgrading to the latest version. Accept this, and restart the VM when requested.

Configuring Windows 2012

  1. Once you have logged back in again the Server Manager appears. On the left hand menu, click on Local Server. This will bring up all the properties for this machine.
  2. Disable the Windows Firewall, by clicking on the Windows Firewall Public: On link and then selecting Turn Windows Firewall on or off. And then turn the firewall off.

  3. Back on the properties screen click the On link for IE Enhanced Security Configuration. Then turn this off for Administrators and Users. Click OK.
  4. Go back to the Start menu by moving your mouse to the bottom left of the screen, or by pressing the windows key. Type UAC, and select settings from the right hand side. Click on Change User Account Control Settings.
  5. On the User Account Control Settings, move the slider down to the bottom. This will prevent the screen keep prompting you when an administrative task needs to be made. Click OK. A administrative prompt will appear, but this will be your last one.
  6. Back in the Server Manager properties window for the server. Enable Remote Desktop. Click the Disabled link. Then select Allow remote connections to this computer. Click OK.
  7. Back in the Server Manager properties window for the server, Click on Windows Update Not Configured link. This will open up the Windows Update dialog.
  8. Click Let me choose my settings. Then on Choose your Windows Update settings, I always have Download updates but let me choose whether to install them. And then I tick Recommended updates. Click OK.
  9. Your server will now check for updates. Install anything that is outstanding.

Apply the DisableLoopbackCheck Registry Fix.

  1. Click the Windows PowerShell icon in the Taskbar.
  2. Run the following PowerShell commands, pressing [Enter] after each one:
    
$regKeyPath = &quot;HKLM:\System\CurrentControlSet\Control\Lsa&quot;
$key = &quot;DisableLoopbackCheck&quot;
New-ItemProperty –Path $regKeyPath –Name $key –Value &quot;1&quot; –PropertyType dword

Disable the Shutdown Event tracker.

  1. Open the start menu and type gpedit.msc. Click on the program to open it.
  2. In Group Policy Editor, navigate to Computer Configuration\Administrative Templates\System, open the Display Shutdown Event Tracker policy, and set it to Disabled.

    Now when you shut down you shouldn’t see the Shutdown tracker appear.

Enabling the Desktop Experience.

  1. Open up the Server Manager.
  2. On the right of the screen click Mange > Add Roles and Features
  3. Click Next.
  4. Ensure Role-based or Feature-based installation is selected. Click Next.
  5. On Select destination server click Next.
  6. On Select server roles click Next.
  7. On Select features. Expand User Interfaces and infrastructure (Installed) and tick Desktop Experience. Add the addition features that it requires. Click Next.
  8. Click Install.

Getting the Sound to work within your Virtual Machine.

Now you might have noticed there is no sound coming from your virtual machine. It might not bother you. It appears that you cannot get sound going through a Hyper V client window. However if you remote desktop onto the machine you can then get sound from your virtual machine, if you follow my previous blog on Getting sound to work within your Windows Server Hyper V Client.

Set up directories for SQL.

  1. Bring up Explorer by click on Windows Key + E.
  2. Double click on D drive. It will ask if you wish to format it. Say yes and format the disk.
  3. Create the following Directories on D.
    1. d:\SQL\Data
    2. d:\SQL\Logs
    3. d:\SQL\Temp\Data
    4. d:\SQL\Temp\Logs
    5. d:\SQL\BackUp

SQL 2012 Standard Edition 64 bit with SP1

Now we can Install SQL Server 2012 Standard Edition with SP1. Select the ISO in Virtual Machine Settings.

  1. Run Setup.exe
  2. The SQL Server Installation Center will open up. On the left hand side, click Installation.
  3. Click on New SQL Server stand-alone installation or add features to an existing installation
  4. After click next Setup support rules will identify any problems that might occur when installing SQL Server Setup support files. There should be all passes. Click OK.
  5. Choose Enter the product Key the product key should already be entered. Click Next.
  6. Accept the license terms. Click Next.
  7. The setup files will now install.
  8. Click Next
  9. On the Setup Role Page. Select SQL Server Feature Installation and click Next.
  10. On the Feature Selection Page. Select Database Engine Services, Full-Text and Semantic Extractions for Search. Then under Shared Features select Management Tools – Basic and Management Tools Complete. Click Next.
  11. On the Installation Rules page click Next.
  12. On the Instance Configuration page click Next.
  13. On the Disk Space Requirements page click Next.
  14. On the Server Configuration page, change the SQL Server Agent Start Up Type to Automatic. Click Next.
  15. On the Database Engine Configuration page. Select Mixed Mode and enter the password as Pa55word. And Click Add Current User.
  16. On the Data Directories tab, change the data to the following.
    1. User Database Directory – d:\SQL\Data
    2. User Database Log Directory – d:\SQL\Logs
    3. Temp DB directory – d:\SQL\Temp\Data
    4. Temp DB Log Directory – d:\SQL\Temp\Logs
    5. Backup Directory – d:\SQL\BackUp
  17. On the FILESTREAM tab tick Enable FileStream for Transact-SQL access and Enable FILESTREAM for file I/O access. Click Next.
  18. On the Error Reporting page, click Next.
  19. On the Installation Configuration Rules page, click Next
  20. On the Ready to Install page, click Install.
  21. After installation your SQL Server 2012 is installed. You may be asked to reboot.

Installing Office 2013

Now we can Install Office 2013. Select the ISO in Virtual Machine Settings.

  1. Run Setup.exe
  2. Accept the license terms. Click Continue.
  3. I just except the defaults, and click Install Now.
  4. Office 2013 is now installed. You will be requested for the key code when you open up an office product.

Don’t forget that Visio is a separate installation. (I’m not going to teach you how to do Next Next on that as well. I think you can work it out yourself.

Installing Visual Studio.

Now we can Install Visual Studio 2012 Ultimate. Select the ISO in the Virtual Machine Settings. (Again I probably don’t need to walk you through this bit).

  1. Run VS_ultimate.exe
  2. Agree to the License terms and Conditions. Click Next
  3. Decide what you want to install, you will need Microsoft Office Developer Tools, Microsoft SharePoint Developer Tools, Silverlight Development Kits, and Microsoft Web Developer Tools. Blend for Visual Studio might be useful for SharePoint. Click Install.

Extra Stuff to install.

Setting up the Menu.

If you currently look at your menu you will notice that everything is everywhere. You can click and drag tiles and position them in more logical sections. By moving them between other sections you can create new sections.

By clicking on the bottom right of the menu screen there is a minus icon. By clicking this, you will zoom out. Then by right clicking a section you can then name it.

I organised my menu to look like below screen shot.

Sysprep the machine.

As I have already written a blog on Sysprepping a machine while keeping the Menu the same after sysprep follow the following blog. Windows 8 and Windows Server 2012 Start Screen is Reset when Sysprep

Once you have completed the above post of Sysprep while keeping the Start menu. Do not turn on your Virtual Machine again. If you do it will start the Sysprep process. At this point, go to where the base hard drive is for the Virtual Machine, and set it to Read Only.

Create your SharePoint machine from your Base disk.

  1. First remove the Virtual Machine from the Hyper V manager. Select the base virtual machine and on the right hand side, under the machine name, click Delete. This just deletes the machine, but keeps the virtual machine disk. By deleting this from Hyper V there is no chance of accidently starting it.
  2. Next follow my previous blog on Creating your hyper V windows Server 2012 machine from a differencing disk.
  3. When you start up your new Virtual Machine based on the base disk, you will be required to enter a valid Key Code for Windows Server 2012 Standard Edition. Let the sysprep process complete, answer any installation questions required and then log in.
  4. You now have a Windows Server 2012 with Office, SQL, Visual Studio ready for SharePoint 2013, or whatever else you might want to install on it instead.