European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

European VirtoCommerce Hosting - UK :: How to Install VirtoCommerce with Plesk Control Panel?

clock August 26, 2014 06:53 by author Scott

In today tutorial, we will share how to install VirtoCommerce in our windows shared hosting environment. In this article, we also use Plesk Control Panel to manage the database. Our assumption here, that you have configured the latest ASP.NET on your server and configure your Control Panel correctly.

Ok, let’s begin the installation.

Download the VirtoCommerce source

You can download the source on http://virtocommerce.codeplex.com/releases/view/126088. The new release is version 1.11

Extract The Files

Once you have download the source, then please kindly extract all the files to your root folder. We have stored our files on C:/……/ourdomain.com/httpdocs

Create MSSQL Database

Please make sure that you create the database via your Control Panel. We use Plesk Control Panel here.

Please make sure you create MSSQL database via your Control Panel as VirtoCommerce requires MSSQL database.

Begin the VirtoCommerce Installation

Then browse your site and fill all your details. Please just see the figure below:

Opps… When you click Finish Setup, we got issue on the database. Please make sure that your db user have the full permission to alter the database. Please tick db_owner, it will fix your problem.

Then, try again to click Finish Setup. It will fix all your problem

VirtoCommerce Installation Complete

Well done, now you can browse your VirtoCommerce site. It is easy, right?

Conclusion

If you need VirtoCommerce hosting, no need to look further as our hosting environment support the latest VirtoCommerce hosting. You can always start from as low as €3.00/month to get this application installed on your site. What are you waiting for?



Comparison of Windows ASP.NET Hosting between HostForLIFE.eu Hosting Platform and Windows Azure Platform

clock August 25, 2014 11:12 by author Peter

Given the length of this review and the number of plans, it is unrealistic to compare everything of them one by one. As usual, the review focuses on price, features, uptime & speed as well as customer support. Before starting our detailed comparison, we’d like to share with you the overall ratings of the plans based on our first-hand hosting experience and large-sampled customer reviews.

Please refer to this table for more differences and similarities.


HostForLIFe.EU

Windows Azure

 

 

 

Price

€3.00/month

$76 / month

Website

Unlimited

100

Disk Space

Unlimited

1 GB

Bandwidth

Unlimited

Unlimited

1 SQL Server Size

Include 50MB

0 to 100 MB = $ 5.041/mo

Server Features

Include 4 GB RAM or higher

768 MB = $16/month

SLA

99.90%

99.90%

ASP.NET 4.5.2 / ASP.NET 4.5.1

Yes

Yes

ASP.NET 4.5 / ASP.NET 4.0

Yes

Yes

ASP.NET 3.5 / ASP.NET 2.0 / ASP.NET 1.1

Yes

Yes

Classic ASP

Yes

Yes

ASP.NET MVC 6.0 / 5.2 / MVC 5.1.2 / MVC 5.1.1 / MVC 5.0

Yes

Yes

ASP.NET MVC 4.0 / MVC 3.0 / MVC 2.0

Yes

Yes

WordPress

Yes

Yes

Umbraco

Yes

Yes

Joomla

Yes

Yes

Drupal

Yes

Yes

Node.js

Yes

Yes

PHP 5

Yes

Yes

Conclusion
Both companies are able to provide brilliant Windows hosting service. If a choice has to be made, we recommend HostForLIFE.eu as your web host because the price is more reasonable, features more complete and the performance and our technical support are awesome.To learn more about HostForLIFE.eu web hosting, please visit http://www.hostforlife.eu



European Reporting Services 2008 Hosting :: How to Troubleshoot The Timeout Error in MS SQL Reporting Services

clock August 18, 2014 06:27 by author Onit

This article will guide you how to solve timeout error in MSSQL Reporting Service. This error happened in SSRS 2008 R2 you may got error such System.Web.HttpException:Maximum request length Exceeded, so in this article I will show you the steps to solved this kind of error.


Well  I’ve found this error while trying to deploy the project:

System.Web.Services.Protocols.SoapException: There was an exception running the extensions specified in the config file. —> System.Web.HttpException: Maximum request length exceeded.    at System.Web.HttpRequest.GetEntireRawContent()    at System.Web.HttpRequest.get_InputStream()    at System.Web.Services.Protocols.SoapServerProtocol.Initialize()    — End of inner exception stack trace —    at System.Web.Services.Protocols.SoapServerProtocol.Initialize()    at System.Web.Services.Protocols.ServerProtocol.SetContext(Type type, HttpContext context, HttpRequest request, HttpResponse response)    at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)

What makes this error?

This error caused by exist a property MaxRequestLength under the httpRuntime elementin the file web.config (ssrs folder\Reporting Services\ReportServer) its default value is 4096 Kb only 4Mb) it makes reporting services cannot upload a report successfully while the report size is higher than this value.

How to solve this error?

  1. Go to Example-> (C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER*\Reporting Services\ReportServer) (*your SSRS installation folder)
  2. Open web.config file
  3. On that file change the line
    <httpRuntime executionTimeout=”9000″/>
    To
    <httpRuntime executionTimeout=”9000″ maxRequestLength = “16384” />

By default, the property maxRequestLength doesn’t exist in the config file and the default value is 4096 KB so you have to increase that value. In example above I am increasing the value to 16384 KB.



European SQL 2014 Hosting - UK :: How to Join The First Row in SQL

clock August 14, 2014 12:41 by author Onit

To easily making report, we need a list of users and the most recent widget each user has created. We have a users table and a widgets table, and each user has many widgets. users.id is the primary key on users, and widgets.user_id is the corresponding foreign key in widgets.

To solve this problem, we need to join only the first row. There are several ways to do this. Here are a few different techniques and when to use them.

Use Correlated Subqueries when the foreign key is indexed

Correlated subqueries are subqueries that depend on the outer query. It's like a for loop in SQL. The subquery will run once for each row in the outer query:

select * from users join widgets on widgets.id = (
    select id from widgets
    where widgets.user_id = users.id
    order by created_at desc
    limit 1
)

Notice the where widgets.user_id = users.id clause in the subquery. It queries the widgets table once for each user row and selects that user's most recent widget row. It's very efficient if user_id is indexed and there are few users.

Use a Complete Subquery when you don't have indexes

Correlated subqueries break down when the foreign key isn't indexed, because each subquery will require a full table scan.

In that case, we can speed things up by rewriting the query to use a single subquery, only scanning the widgets table once:

select * from users join (
    select distinct on (user_id) * from widgets
    order by user_id, created_at desc
) as most_recent_user_widget
on users.id = most_recent_user_widget.user_id

This new subquery returns a list of the most recent widgets, one for each user. We then join it to the users table to get our list.

We've used Postgres' DISTINCT ON syntax to easily query for only one widget peruser_id.

Use Nested Subqueries if you have an ordered ID column

In our example, the most recent row always has the highest id value. This means that even without DISTINCT ON, we can cheat with our nested subqueries like this:

select * from users join (
    select * from widgets
    where id in (
        select max(id) from widgets group by user_id
    )
) as most_recent_user_widget
on users.id = most_recent_user_widget.user_id

We start by selecting the list of IDs repreenting the most recent widget per user. Then we filter the main widgets table to those IDs. This gets us the same result as DISTINCT ON since sorting by id and created_at happen to be equivalent.

Use Window Functions if you need more control

If your table doesn't have an id column, or you can't depend on its min or max to be the most recent row, use row_number with a window function. It's a little more complicated, but a lot more flexible:

select * from users join (
    select * from (
        select *, row_number() over (
            partition by user_id
            order by created_at desc
        ) as row_num
        from widgets
    ) as ordered_widgets
    where ordered_widgets.row_num = 1
) as most_recent_user_widget
on users.id = most_recent_user_widget.user_id
order by users.id

The interesting part is here:

select *, row_number() over (
    partition by user_id
    order by created_at desc
) as row_num
from widgets
over (partition by user_id order by created_at desc

specifies a sub-table, called a window, per user_id, and sorts those windows by created_at desc.row_number() returns a row's position within its window. Thus the first widget for each user_id will have row_number 1. 

In the outer subquery, we select only the rows with a row_number of 1. With a similar query, you could get the 2nd or 3rd or 10th rows instead.



European BlogEngine.NET Hosting - UK :: How to Create Responsive BlogEngine.NET Theme?

clock August 8, 2014 12:47 by author Scott

Well.. Our blog is also use BlogEngine.NET. We have used this CMS for long time. In this article, we will share short tutorial how to make your BlogEngine.NET site optimized perfectly and improve your google SEO score.

Creating a theme

I've never created a blog theme before, for any engine, but BlogEngine.NET has a handy tutorial that walks you through the necessary steps. I had to refer to some of the stock themes occasionally for clarification, but most of what I needed was in the tutorial - if you've done any ASP.NET development before you won't have any problems.

There are really only 4 things you need to create for your theme:

- Master page (site.master) - This is the master page applied to every blog page. Use this to define your basic layout, etc.
- Post View (PostView.ascx) - This defines the user control for displaying a single post.
- Comment View (CommentView.ascx) - The user control for displaying comments.
- Your own CSS to style the elements created above.

(These are all described in more detail in the tutorial)

We took the appropriate bits of boilerplate HTML from the sample index.html that came with Skeleton and used them as the start of my site.master:

<!DOCTYPE html>
<!--[if lt IE 7 ]><html class="ie ie6" lang="en"> <![endif]-->
<!--[if IE 7 ]><html class="ie ie7" lang="en"> <![endif]-->
<!--[if IE 8 ]><html class="ie ie8" lang="en"> <![endif]-->
<!--[if (gte IE 9)|!(IE)]><!--><html lang="en"> <!--<![endif]-->
<head runat="server" profile="http://gmpg.org/xfn/11"> 

    <!-- Mobile Specific Metas
  ================================================== -->
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"> 

    <!—CSS
  ================================================== -->
    <link rel="stylesheet" href="css/base.css">
    <link rel="stylesheet" href="css/skeleton.css">
    <link rel="stylesheet" href="css/layout.css"> 

    <!--[if lt IE 9]>
        <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
    <![endif]--> 

    <!—Favicons
    ================================================== -->
    <link rel="shortcut icon" href="images/favicon.ico"> 

</head>

Make sure you include that mobile-specific meta if you want your responsiveness to work on mobiles. Html5shim makes your HTML5 elements work in older versions of IE.

We then roughed out where everything needed to go and used the Skeleton grid to lay things out on the page, eg. this is the header:

<div class="header">
        <div class="container">
            <div class="ten columns">
                <h1><a href="<%=Utils.AbsoluteWebRoot %>"><%=BlogSettings.Instance.Name %></a></h1>
                <h4><%=BlogSettings.Instance.Description %></h4>
            </div>
            <div id="headerextra" class="six columns">
                <div id="headerlinks">
                    <ul>
                        <li><a href="<%=Utils.AbsoluteWebRoot %>page/My-CV.aspx">CV/Resume</a></li>
                        <li><a href="<%=Utils.AbsoluteWebRoot %>contact.aspx"><%=Resources.labels.contact %></a></li>
                        <li><a href="<%=Utils.AbsoluteWebRoot %>archive.aspx"><%=Resources.labels.archive %></a></li>
                        <li><a href="<%=Utils.FeedUrl %>" class="feed"><img src="<%=Utils.RelativeWebRoot %>themes/<%=BlogSettings.Instance.Theme %>/images/rss.png" alt="Subscribe" title="Subscribe" /></a></li>
                    </ul>
                </div>
                <div id="headersearch">
                    <blog:SearchBox ID="SearchBox1" runat="server" />
                </div>
            </div>
        </div>
    </div>

Bingo!!

Now, you can see that your site has optimized for mobile phone. Your site scales perfectly down to small, phone-size screens.



European SQL 2014 Hosting - UK :: Know Further About SQL 2014

clock August 5, 2014 07:22 by author Scott

We are so happy that Microsoft has announced the SQL 2014. With this newest launch, we are so happy to introduce our SQL 2014 hosting our hosting environment.

SQL Server 2014 helps organizations by delivering:

1. Mission Critical Performance in SQL 2014

SQL Server 2014 delivers new in-memory capabilities built into the core database for OLTP and data warehousing, which complement existing in-memory data warehousing and business intelligence capabilities for a comprehensive in-memory database solution. In addition to in-memory, there are new capabilities to improve the performance and scalability for your mission critical applications.

  • New In-Memory OLTP – built in to core SQL Server database and uniquely flexible to work with traditional SQL Server tables allowing you to improve performance of your database applications without having to refresh your existing hardware
  • Enhanced In-Memory ColumnStore for Data Warehousing – now updatable with even faster query speeds and with greater data compression for more real-time analytics support.
  • New buffer pool extension support to non-volatile memory such as solid state drives (SSDs) – Increase performance by extending SQL Server in-memory buffer pool to SSDs for faster paging.
  • Enhanced AlwaysOn – Built upon the significant capabilities introduced with SQL Server 2012, delivers mission critical availability with up to 8 readable secondaries and no downtime during online indexing operations.
  • Greater scalability of compute, networking and storage with Windows Server 2012 R2
  • Enhanced Resource Governance – With Resource Governor, SQL Server today helps you with scalability and predictable performance, and in SQL Server 2014, new capabilities allow you to manage IO, in addition to compute and memory to provide more predictable performance.
  • Enhanced Separation of Duties – Achieve greater compliance with new capabilities for creating role and sub-roles. For example, a database administrator can now manage the data without seeing sensitive data or personally identifiable information.

2. Faster Insights on Any Data

SQL Server 2014 is at the heart of our modern data platform which delivers a comprehensive BI solution that simplifies access to all data types big and small with additional solutions like HDInsight, Microsoft’s 100% Apache compatible Hadoop distribution and project code name “Data Explorer”, which simplifies access to internal or external data. New data platform capabilities like Polybase included in Microsoft Parallel Data Warehouse allows you to integrate queries across relational and non-relational data using your existing SQL Server skills.

With SQL Server 2014, you can accelerate insights with our new in-memory capabilities with faster performance across workloads. You can continue to refine and manage data using Data Quality Services and Analysis Services in SQL Server and finally analyze the data and unlock insights with powerful BI tools built into Excel and SharePoint.

Conclusion

If you want to try the newest SQL 2014 as your database hosting, then you don’t need to look further as we (HostForLIFE.eu) has supported the latest SQL 2014 hosting on our hosting environment. You just need to start as low as €3.00/month to get the latest SQL 2014 hosting. Are you ready??



European Moodle 2.7 Hosting - HostForLIFE.eu :: What’s new in Moodle 2.7 ? Let’s Find out and Try With Us!

clock July 16, 2014 07:04 by author Peter

Moodle 2.7 has been released. The upgrade to the free and open source learning management system includes new themes with improved responsive design, a new accessibility-focused text editor and more. These are just the minimum supported versions. In addition to a number of bug fixes and small improvements, security vulnerabilities have been discovered and fixed.  We highly recommend that you upgrade your sites as soon as possible. Upgrading should be very straightforward. As per our usual policy, admins of all registered Moodle sites will be notified of security issue details directly via email and we'll publish details more widely in a week.

Moodle 2.7 brings a few headline feature improvements, including the replacement of the TinyMCE editor with ‘Atto’ and the continued emphasis on responsive design with ‘Clean’ the default theme and the new ‘More’ theme being released. Compared to a number of earlier Moodle releases though, some might question whether there is a compelling reason to upgrade to Moodle 2.7 (especially if you have only recently upgraded to Moodle 2.6).

And here’s Top new features of Moodle 2.7:

Clean and Responsive Design
Moodle 2.7 has two core themes; Clean and More.  Clean is the responsive bootstrap based theme which has now been made the default theme.  More is a completely new, customisable theme, created for the novice user or administrators who are unable to access theme files on the server.

New Text Editor, Atto
 In the latest release of Moodle, they have named the text editor ‘Atto’, the new Atto feature allows tighter integration with Moodle meaning there are enhancements to usability and accessibility like never before.

Mobile Notifications. This is possibly the most exciting new feature to enable ‘push’ learning to users on the go. The essential tool for Learning and Development professions in marketing the platform, this new feature will allow you to push notifications about new learning modules created within Moodle which your learners can take advantage of on the go.

Improved quiz reports and other general improvements to quizzes and question banks, such as the ability to duplicate a question with a single click, the ability to require an attachment for essay questions and the ability to save changes and continue editing;

Improved Scheduling System. Administrators can now schedule, more precisely routine tasks, by specifying the minute/hour/day or month the task is to be run.

First long term support release: 3 years until May 2017 for security and data loss bug fixes .

Easily create and manage Quiz & Question bank. As well as an updated question type selector, duplicating and moving questions is now easier, and there is an option to 'Save changes and continue editing'.

Install the new Moodle software manually

1. First, Move your old Moodle software program files to another location. Do NOT copy new files over the old files.
2. Then, Unzip or unpack the upgrade file so that all the new Moodle software program files are in the location the old files used to be in on the server. Moodle will adjust SQL and moodledata if it needs to in the upgrade.Copy your old config.php file back to the new Moodle directory.
3. As mentioned above, if you had installed any plugins on your site you should add them to the new code tree now. It is important to check that you get the correct version for your new version of Moodle. Be particularly careful that you do not overwrite any code in the new version of Moodle.
4. Do not forget to copy over your moodledata folder / directory. If you don't you will get a "fatal error $cfg- dataroot is not configured properly".



HostForLIFE.eu Proudly Launches New Data Center in London (UK) and Seattle (US)

clock July 15, 2014 09:56 by author Peter

HostForLIFE.eu, a leading Windows web hosting provider with innovative technology solutions and a dedicated professional services team proudly announces New Data Center in London (UK) and Seattle (US) for all costumers. HostForLIFE’s new data center in London and Seattle will address strong demand from customers for excellent data center services in Europe and United States, as data consumption and hosting services experience continued growth in the global IT markets.

The new facility will provide customers and their end users with HostForLIFE.eu.com services that meet in-country data residency requirements. It will also complement the existing HostForLIFE.eu. The London and Seattle data center will offer the full range of HostForLIFE.eu.com web hosting infrastructure services, including bare metal servers, virtual servers, storage and networking.

"Our expansion into London and Seattle gives us a stronger European and American market presence as well as added proximity and access to our growing customer base in region. HostForLIFE.eu has been a leader in the dedicated Windows & ASP.NET Hosting industry for a number of years now and we are looking forward to bringing our level of service and reliability to the Windows market at an affordable price,” said Kevin Joseph, manager of HostForLIFE.eu, quoted in the company's press release.

The new data center will allow customers to replicate or integrate data between London and Seattle data centers with high transfer speeds and unmetered bandwidth (at no charge) between facilities. London and Seattle, itself, is a major center of business with a third of the world’s largest companies headquartered there, but it also boasts a large community of emerging technology startups, incubators, and entrepreneurs.

For more information about new data center in London and Seattle, please visit http://www.HostForLIFE.eu

About Company
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



European Windows Cloud Hosting Service :: Why Migrate Your Site to Cloud Hosting? What is the Benefits Using Cloud Hosting?

clock July 14, 2014 11:45 by author Scott

In this article, I’ll introduce you to the cloud, help you interpret the buzzwords, and explain how your business might save time and money using a cloud hosting service.

Cloud computing is a general term for anything that involves delivering hosted services over the Internet. One can also say that Cloud computing is Internet-based computing, whereby shared resources, software and information are provided to computers and other devices on-demand. These hosted services are broadly divided into three categories: Infrastructure-as-a-Service (IaaS), Platform-as-a-Service (PaaS) and Software-as-a-Service (SaaS). The name cloud computing was inspired by the cloud symbol that's often used to represent the Internet in flowcharts and diagrams.

 

A cloud service has three distinct characteristics that differentiate it from traditional hosting. It is sold on demand, typically by the minute or the hour; it is elastic -- a user can have as much or as little of a service as they want at any given time; and the service is fully managed by the provider (the consumer needs nothing but a personal computer and Internet access).

Who Use Cloud Computing?

There are many hosting provider use Cloud providers now, like Yahoo, Amazon, Google, and IBM. We as Microsoft hosting partner also update our technology and we also offering Windows Cloud Dedicated Server from affordable price.

Why many hosting provider choose Cloud?

Here are several reasons why many hosting provider switch to cloud service:

1. Data protection

Data is key and possibly the most important asset for organisations - a single breach or leak of sensitive data can cripple the entire business, so a data protection strategy must protect the data itself. The ability to move sensitive information into and throughout the cloud is essential for businesses to function and collaborate efficiently, quickly and freely - but this ability must be supported by a comprehensive data protection strategy. The trick is to protect data at the moment of creation, before it moves out of the enterprise or even enters the cloud. Only by doing that can you ensure that any data source is comprehensively protected, and the risk to potential exposure is minimised.

2. Scalability and flexibility

The cloud has opened up previously unseen opportunities for organisations to grow and expand quickly, smoothly and with ease. With information immediately available wherever you are, the cloud offers the flexibility and scalability that in the past was an insurmountable obstacle for businesses restricted by their on-site resources. The key to successfully harnessing this opportunity is a flexible data security architecture that is adaptable across multiple applications and systems, while not adversely impacting the user experience. Failure to put a comprehensive, data-centric protection program can cause cloud initiatives to be delayed or fraught with hidden security issues.

3. Cost efficiencies

This element is two-fold. First, reap the powerful cost savings by only paying for what you use. The second element is that most cloud computing platforms provide the means to capture, monitor, and control usage information for accurate billing. A single, comprehensive data protection platform can eliminate the threat of risky fines from compliance breaches or data loss, while also reducing the need to invest into multiple security tools.

Benefits Choosing Cloud Hosting with HostForLIFE.eu

You have found the best and most affordable windows cloud dedicated server with us. Here are several reasons why choose our Windows Cloud dedicated server:

- Our Windows Cloud Dedicated Server is very affordable

You'll get the greatest cost savings in the public cloud, where your virtual servers run on physical servers that you share with other customers. You can start from only €16.00/month to test our cloud server.

- Our Windows Cloud Dedicated Server is very secure

For the utmost security, create a private cloud on dedicated hardware. But always remember to put appropriate security measures in place, no matter which cloud you choose.

- Our Windows Cloud Dedicated Server is very flexible and scalable

You don’t need to wait any longer to start using our Windows cloud dedicated server. You just need to wait few minutes and your server is ready to use.

- Our Windows Cloud Dedicated Server is very stable

Unlike in-house server solutions where you might have hardware and software problems or outages that potentially last an entire afternoon, with an established and dependable Cloud hosting provider like HostForLIFE.eu you can be sure of a constant level of service. Our business depends on guaranteeing stability to our customers, and this is exactly what we provide.

 



European SQL 2014 Hosting - France :: How to Fix The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA in In-Memory OLTP SQL 2014

clock July 4, 2014 08:45 by author Scott

Microsoft's new release of SQL Server 2014 comes pretty close on the heels of the last SQL Server 2012 release. In this article I will be exploring and explaining about In Memory OLTP with SQL Server 2014.

SQL Server 2014 CTP is available for download and evaluation and it contains a couple of exciting performance enhancements. One of these is OLTP databases optimized to be memory resident.

This is 2 concern that I want to check:

1. Because SQL 2014 is only at CTP1 compatibility with earlier versions is not guaranteed and therefore Microsoft won’t let you install this side by side with any other versions.

2. Because of gotcha number one you’ll probably decide,  for example you install it in a virtual machine. Using Oracle Virtual Box you might strike this error when you try to create a memory optimised filegroup:

Msg 41342, Level 15, State 1, Line 5

The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA. This error typically occurs with older processors. See SQL Server Books Online for information on supported models.

To resolve this – navigate to the virtual box install folder and run this command:

VBoxManage setextradata [vmname] VBoxInternal/CPUM/CMPXCHG16B 1

I had to restart the guest and the host for this change to stick.

DDL.

Now with that all working time to create the database.

CREATE DATABASE InMemDB
GO

Add a filegroup for the in memory objects (alter the path for your instance).

ALTER DATABASE InMemDB ADD FILEGROUP InMemDB_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE InMemDB ADD FILE (name='InMemDB_mod1', filename='E:\SQLData\InMemDB_mod1') TO FILEGROUP InMemDB_mod
GO

A database can contain a mix of in memory tables (and the new natively compiled stored procedures) and traditional disk based tables. The in memory tables are marked with the keywords MEMORY_OPTIMIZED=ON and they must have at least one hash index – it is not possible to create a heap.

USE InMemDB
GO

CREATE TABLE dbo.Table1 (
   Id_tb1 int not null primary key nonclustered hash with (bucket_count=20480),

   Int_Val int not null index ix_Int_Val nonclustered hash with (bucket_count=10240),
   CreateDate datetime2 not null,
   [Description] varchar(255)
)
WITH (MEMORY_OPTIMIZED=ON)
GO

CREATE TABLE dbo.Table2 (
   Id_tb2 int not null primary key nonclustered hash with (bucket_count=4096),
   Int_Val int not null,
   CreateDate datetime2 not null,
   Id_tb1_fk int,
   index ix_Int_Val nonclustered hash (Int_Val) with (bucket_count=4096)
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)
GO

In memory tables have a durability property that can be set to SCHEMA_AND_DATA or SCHEMA_ONLY. There are two new columns in sys.tables to track this. SCHEMA_ONLY tables are volatile, SCHEMA_AND_DATA are persisted to disk.

SELECT name,type_desc,durability_desc FROM sys.tables

The hash indexes can be tracked with a new catalog view.

SELECT * FROM sys.hash_indexes

DML.

The two big selling points of in memory OLTP are improved performance with entirely memory resident data accessed with hash indexes and use of an optimistic multiversion concurrency control … no locking.

So let’s run some DML and test this out. Insert a row:

BEGIN TRAN

INSERT dbo.Table1 VALUES (1,427,getdate(),'Insert transaction')

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
SELECT * FROM sys.dm_db_xtp_transactions

COMMIT TRAN

The sys.dm_tran_locks DMV will return a shared object lock and a Schema shared lock on the database. The new sys.dm_db_xtp_transactions is a new DMV for in memory OLTP and returns information about current transactions against the database.

Now run an update inside an explicit transaction. Note that we will get an error if we don’t use the WITH(SNAPSHOT) hint.

BEGIN TRAN

UPDATE dbo.Table1  WITH (SNAPSHOT)
SET [Description] = 'Updated transaction'
WHERE Id_tb1 = 1

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
SELECT * FROM sys.dm_db_xtp_transactions

In another session run a select.

SELECT *
FROM dbo.Table1
WHERE Id_tb1 = 1

The select returns the pre updated version of the row – but there is no blocking. This is achieved using a versioning system that is, despite the keyword SNAPSHOT, not the familiar SNAPSHOT ISOLATION row versioning based in tempdb. (Details of how this is achieved can be found in the readings at the end of this blog.)

Commit the update and run the following delete.

BEGIN TRAN

DELETE dbo.Table1 WITH (SNAPSHOT)
WHERE Id_tb1 = 1

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
SELECT * FROM sys.dm_db_xtp_transactions

Note that the delete transaction is still only holding lightweight shared locks. The select in another session will now return the updated row.

SELECT *
FROM dbo.Table1
WHERE Id_tb1 = 1

Commit the delete transaction.

Concluding Remarks.

As of CTP1 not all of the features are fully working and there are a number of limitations that are likely to roll into RTM – hopefully to be slowly improved in subsequent releases. The whitepaper in the reading list below has an exhaustive list of these restrictions but one of the biggest ones for me is that foreign key constraints are not supported.

Another big feature that is available for in memory tables are the native stored procedures. I didn’t touch on these but they are tipped to offer highly optimised performance. A stored procedure that only operates against in memory tables can be marked using the WITH NATIVE_COMPILATION option and this will compile the sproc into a DLL using C code.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in