European Windows 2012 Hosting BLOG

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

OWIN & Katana.NET Hosting - HostForLIFE.eu :: First Look at OWIN and Katana Project

clock March 31, 2015 06:26 by author Rebecca

Today, i'm gonna show you how to get started with OWIN and Katana Project. Open Web Interface for .NET (OWIN) is a specification for the interaction between web servers and web applications. It has its roots in the open source community. By decoupling the web server from the application, OWIN makes it easier to create middleware for .NET web development. Also, OWIN makes it easier to port web applications to other hosts, for example, self-hosting in a Windows service or other process. OWIN is a community-owned specification, not an implementation. The implementation of OWIN is called as Katana Project. The Katana project is a set of open-source OWIN components developed by Microsoft.

Katana is a webframework which is really lightweight and much more modular then ASP.NET. ASP.NET applications always include System.Web, which contains all functionality like caching, authorization, etc. Katana is way more modular. It let’s you build a application where you only add the references you really need. Katana lets you compose a modern Web application from a wide range of different Web technologies and then host that application wherever you wish, exposed under a single HTTP endpoint. This provides several benefits:

  • Deployment is easy because it involves only a single application rather than one application per capability.
  • You can add additional capabilities, such as authentication, which can apply to all of the downstream components in the pipeline.
  • Different components, whether Microsoft or third-­party, can operate on the same request state via the environment dictionary.

So, Katana is a flexible set of components for building and hosting OWIN-based web applications. And this post is just a quick start to give you an impression of the power that Katana gives you.

The code below shows how easy is to get started with Katana. Create a new console application and install the nuget package: Microsoft.Owin.SelfHost. And If you now paste the code, you will already have a working website at localhost:8080.

class Program
{
    static void Main(string[] args)
    {
        var url = "http://localhost:8080";
        using (WebApp.Start<Startup>(url))
        {
            Console.ReadLine();
        }
 
    }
}
 
public class Startup
{
    public void Configuration(IAppBuilder app)
    {
        app.Run(x =>
        {
            x.Response.ContentType = "text/plain";
            return x.Response.WriteAsync("Hello world");
        });
    }
}

We define a url and a startup class. The Startup class needs a configuration method in which we can set the run method with a Func on your IAppBuilder. This appbuilder is the “configuration” of your web application and the run method adds a piece of middleware to your OWIN pipeline which doesn’t invoke a next piece of middleware. Look at it at as the endpoint of all the web requests for this  small web application.

The appbuilder also allows you to add more middleware to your OWIN pipeline. If we for instance want to have some middleware that handles cookies we can add it with a few lines of code.

public class CookieMonster : OwinMiddleware
{
    public CookieMonster(OwinMiddleware next) : base(next)
    {
    }
 
    public async override Task Invoke(IOwinContext context)
    {
        context.Response.Cookies.Append("testcookie", "hello world");
        await Next.Invoke(context);
    }
}

And now, we need to inherit from OwinMiddleware, add a constructor and a async Invoke method. In the invoke method we add our cookie (or whatever logic you want) and make it invoke the next middleware down the pipeline. To  plug this middleware into our application, we only need our appbuilder to use it. Just add the following line in your Startup class above the app.Run line and your done!!

app.Use<CookieMonster>();

Now, you have a working Katana Web Application that adds an annoying cookie. Easy right? This was a really simplified example and there is a lot more to Katana.

For the best OWIN and Katana.NET Hosting, HostForLIFE.eu is the right answer!

HostForLIFE.eu OWIN and Katana.NET Hosting
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 customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2014 Hosting Russia - HostForLIFE.eu :: How to Rename Column Name in SQL Server ?

clock March 17, 2015 07:19 by author Peter

In this post, I will tell you about rename column name in In SQL Server. Now, I’ve got a table “tblRename” in which there are the two columns Id and Name.

But by mistake I misspelled Name to “Names” and now I want to change the column name to the correct name -> “Name”. So, how is it possible to rename the column name without dropping the table?

We can use a System Stored Procedure sp_rename.
EXEC sp_rename 'tblRename.Names', 'Name','column'

sp_rename is the Stored Procedure.The first value is the tblRename that is the table in which the column is to be renamed, as in "Names" -> tblRename.Names. The second value is the new segment name that we need to change it to, in other words Name. The third value is the sort and here the sort is segment.
Execute the previous query.

Finally, the column name is now changed to Name. But when we execute our query we get an error message:

Along these lines, if there are any Stored Procedures and scripts indicating this tblRename table in which you have determined the "Names" segment then that Stored Procedure or script won't come being used any longer in light of the fact that now there is no section present with a name "Names". In this way, in the event that you need to change the segment name then do it before making any important Stored Procedures or scripts.

HostForLIFE.eu SQL Server 2014 Hosting
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 customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2014 Hosting UK - HostForLIFE.eu :: Script TSQL Database Level Security

clock March 12, 2015 08:42 by author Peter

Here's a convenient script that is a piece of my tool stash all over I go. Scripts out and identifies basic database level security objects, and creates a TSQL proclamation to reproduce the items. Note that this script just chips away at SQL 2005 or above. This is a long way from an authority script.

I invite to all input on these scripts. I likewise have a server-level security script here.
--Run the below on each database for database-level security. 
SELECT DB_NAME() as Database_Name 
--Database Level Roles
SELECT DISTINCT
     QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
,    TSQL = 'EXEC sp_addrolemember @membername = N''' + d.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + r.name + ''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in ('dbo', 'sa', 'public') 
--Database Level Security
SELECT     rm.state_desc
     ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
     ,   TSQL = rm.state_desc + N' ' + rm.permission_name + N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))  
FROM sys.database_permissions AS rm
     INNER JOIN
     sys.database_principals AS u
     ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like '##%'
and u.name not in ('dbo', 'sa', 'public')
ORDER BY rm.permission_name ASC, rm.state_desc ASC
 --Database Level Explicit Permissions
SELECT     perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + ')' END AS [Object]
     , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
   ,  TSQL = perm.state_desc + N' ' + perm.permission_name
           + N' ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
           + N' TO ' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
     INNER JOIN
     sys.objects AS obj
     ON perm.major_id = obj.[object_id]
     INNER JOIN
     sys.database_principals AS u
     ON perm.grantee_principal_id = u.principal_id
     LEFT JOIN
    sys.columns AS cl
     ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
     obj.name not like 'dt%'
and obj.is_ms_shipped = 0
and u.name not in ('dbo', 'sa', 'public')
ORDER BY perm.permission_name ASC, perm.state_desc ASC


Alternately, wrap the entire thing in a msforeachdb:
exec sp_msforeachdb 'use [?];
SELECT DB_NAME() as Database_Name
--Database Level Roles
SELECT DISTINCT
 QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
, TSQL = ''EXEC sp_addrolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in (''dbo'', ''sa'', ''public'')
--Database Level Security
SELECT  rm.state_desc
 ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
 ,   TSQL = rm.state_desc + N'' '' + rm.permission_name + N'' TO '' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))  
FROM sys.database_permissions AS rm
 INNER JOIN
 sys.database_principals AS u
 ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like ''##%''
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY rm.permission_name ASC, rm.state_desc ASC
 --Database Level Explicit Permissions
SELECT perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + '')'' END AS [Object]
 , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
    ,  TSQL = perm.state_desc + N'' '' + perm.permission_name
  + N'' ON '' + QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
  + N'' TO '' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS u
 ON perm.grantee_principal_id = u.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
 obj.name not like ''dt%''
and obj.is_ms_shipped = 0
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY perm.permission_name ASC, perm.state_desc ASC
';

HostForLIFE.eu SQL Server 2014 Hosting
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 customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



HostForLIFE.eu Launches New Data Center in Frankfurt (Germany)

clock March 10, 2015 11:59 by author Peter

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

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

HostForLIFE.eu expansion into Frankfurt gives them a stronger European market presence as well as added proximity and access to HostForLIFE.eu 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.

The new data center will allow customers to replicate or integrate data between Frankfurt data centers with high transfer speeds and unmetered bandwidth (at no charge) between facilities. Frankfurt 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.

Our network is built from best-in-class networking infrastructure, hardware, and software with exceptional bandwidth and connectivity for the highest speed and reliability. Every upstream network port is multiple 10G and every rack is terminated with two 10G connections to the public Internet and two 10G connections to our private network. Every location is hardened against physical intrusion, and server room access is limited to certified employees.

All of HostForLIFE.eu controls (inside and outside the data center) are vetted by third-party auditors, and we provide detailed reports for our customers own security certifications. The most sensitive financial, healthcare, and government workloads require the unparalleled protection HostForLIFE.eu provides.

Frankfurt (Germany) data centres meet the highest levels of building security, including constant security by trained security staff 24x7, electronic access management, proximity access control systems and CCTV. HostForLIFE.eu is monitored 24/7 by 441 cameras onsite. All customers are offered a 24/7 support function and access to our IT equipment at any time 24/7 by 365 days a year. For more information about new data center in Frankfurt, please visit http://hostforlife.eu/Frankfurt-Hosting-Data-Center

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

HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.asp.net/hosting/hostingprovider/details/953). Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, we have also won several awards from reputable organizations in the hosting industry and the detail can be found on our official website.



Node.js Hosting Germany - HostForLIFE.eu :: How to Create Server Using Express.js in Node.js ?

clock March 10, 2015 06:32 by author Peter

This time we will follow a complete and a smart way to create a HTTP server that is exact and quick in Node.js.

Express is insignificant and adaptable Node.JS web application framework that gives a vigorous set of gimmicks of web and portable application. What's more, it will help you in making APIs that verifiably take a shot at the HTTP protocol.

Installation
Express.JS is a framework file hosted by the Node Package Manager (NPM). For installation, we have a special node command to install on the local computer. Write the following code:
npm install [PACKAGE NAME]

And this is code for installing express:
node install express

Note: express is the package name.

After Installing, you will get a folder node_modules that consists of all the modules that you install from npm.

Finally, you have successfully installed the express module in your node folder. Now, write the following code:
// Express
var fs=require("fs");
var host="127.0.0.1";
var port="1337";

//Express
var express=require("express");
var app=express();
app.ger("/",fuction(request, response){
  response.send("Express is Working !!");
});

//Listening Socket
app.listen(port,host);


We utilize the term standard code with express on the grounds that it evacuates the undesirable lines. The code that minimizes the lines of code to do a certain task.  Also, express permits us to make a HTTP server in a couaple of lines of code. And here is the output:

I hope it works for you!

HostForLIFE.eu Node.js Hosting
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 customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

 



European AngularJS Hosting - UK :: Why Use AngularJS for Web Development

clock March 5, 2015 06:50 by author Scott

AngularJS Introduction

AngularJs is an open source Javascript framework to organise and assist web applications and single page applications. In 2012 we witnessed rise of Javascript MVC frameworks and libraries including Backbone.js , Ember.js and Angular.js.

AngularJS is created by Google to build single page applications which could be more architectured and maintainable. AngularJS is completely client-side and entirely JavaScript, so wherever Javascript runs AngularJS also runs. It is even less than 29kb making it highly minified and compressed. Angular is the next generation framework , in which every tool is designed such that it works with every other tool in an interconnected way.

Interesting Point Using AngularJS

Angular has some compelling features for not just the developers but for designers as well.

Two Way Data Binding
It is the most crucial and useful feature of Angular. This feature is what all modern web apps are all about i.e Real Time. Two way binding permanently binds the view to the model and reduces refresh cycles, it also saves a considerable amount of code as previously 80% of code was dedicated to manipulating, traversing and listening to DOM . With data binding this code disappears and hence more concentration can be laid to application. Normally with change in model the DOM elements and attributes need to be manually manipulated to reflect the changes, it proves to be a complex process mainly when application grows in complexity and in size. But with two way data binding the synchronization between the DOM and the model is well taken care of.

HTML Template
AngularJS doesn’t rely on any rendering engine but uses browser parseable .html files for its partials. The HTML templates are parsed by the browser in the DOM. The DOM is now the input to the AngularJS compiler . Angular then traverses the DOM template for rendering called the Directives. The input here is bowser DOM and not the HTML string , this is the noticeable change between angular and all fellow frameworks.

Directives
Directives are stand alone reusable elements separated from the app . All DOM manipulations are performed by Directives. Directives are used to create custom HTML tags to serve as new custom widgets .With Directive you can create a new HTML tag or attribute and make it do anything you want . Directives are very unique, useful, powerful and reusable feature available only in angular. With Directives you can invent new HTML syntax that are specific to your application.

Dependency Injection
Dependency Injection is an angular feature that enables developers to easily build, develop, test and manage applications . With this feature you merely ask for for the dependencies instead of making manually , it will provide you an instance for any service asked provided you add the service as a parameter to get access to this service.

Testing
AngularJS is designed by keeping testability in mind such that angular applications can be tested easily as any Javascript code comes with a strong set of tests . Angular comes with a end to end test runner setup .

Why Use AngularJS?

AngularJS is a new Javascript framework by Google and is designed to make front end development easier . The popularity of single page applications and angular is flaming. Angular provides numerous concepts to to organize and manipulate the web applications.

Enabling a Parallel Workflow
It enables a parallel workflow between designers and developers. For a project both designing and hard coded developing can go side by side. For a project that is estimated to be completed in 4 months then by following the traditional sequential approach there would be dedicated 4 months of design followed by 4 months of coding making it 8 months altogether. But XAML allows to work in parallel by agreeing upon an interface for a screen. Developers can work on grabbing the data and writing all properties and tests around them while designers can animate and manipulate until they reach their final desired design . Those not familiar with XAML it is a declarative XML based language to instantiate object graphs and set values. The reason XML became so popular is because they translate well to angular.

Handling Dependencies
AngularJS easily handles dependency injections , angular lets you divide your app into modules that are initialized separately and having dependencies on each other. This enables you to test only the modules you want at once while also unfolding the ability to create end to end tests as well.

Dynamic loading is used by single page applications to deliver native app feel, but it involves a lot of dependencies on various modules and services, angular organises these and even manages the lifetime of an object for you.

Declarative UI
Having a declarative UI has many advantages associated with it. A structured UI is always easier to understand and manipulate. Without ,then by mere looking at the markup it can’t be figured what UI will actually do. So its not apparent whether any translations and validations are taking place by looking at some form tags.

But by declaring UI and by directly placing markup in HTML one can understand the extended markup angular provides. It makes it clear where and to what data is being bound to. With added tools like filters and directives the intent of UI is much clearer.

Development <-> Design Workflow
This works very well with angular, markup can be added without breaking an application as it depends on a particular structure or id to locate element and do task. Even rearrangement of code is much easier as the corresponding code that binds with it also moves along.

Flexibility with Filters
Filters are standalone functions and filter the data before reaching the view, it can involve formatting decimal places or reversing an array or simply implementing pagination. Filters are separate from app just like directives and are so resourceful that creation of a sorted HTML table is possible with filters without writing any Javascript.

These fundamental features and principles will let you create a performance driven, maintainable , extensible and efficient front end codebase . AngularJS provides a rich experience to the end user, it is a robust and well maintained Javascript framework suitable for any professional web development.

 



SQL Server 2014 Hosting Germany - HostForLIFE.eu :: How to Use a Filtered Index to Enforce Filtered Uniqueness in SQL Server ?

clock March 5, 2015 06:37 by author Peter

One of the benefits and uses of Filtered Indexes in SQL Server is to produce filtered uniqueness. clearly this has some implications, thus please perceive what you are making an attempt to accomplish. In the below example, we've a compound natural key of the 2 fields key1 and key2. the field bit1 isn't a member of the natural key, however will inform us as to some table usage. maybe bit1 indicates that this record is Active (1) or Inactive (0), or whether or not the info is Confirmed (1) or unconfirmed  (0), or Deleted (0) or Not Deleted (1).

In any case, we have a tendency to solely wish to enforce uniqueness for when bit1 = 1, that indicates:
This value is often filtered to be used wherever bit1 = 1
We do not care whether or not there are duplicate records for once bit1 = 0.
In this means, you'll "deactivate" (in business terms) a record by setting bit1 = 0, while not violating your natural key's uniqueness on (key1, key2).
drop table dbo.tabletestdups
go
create table dbo.tabletestdups
( key1 int not null
, key2 int not null
, bit1 bit not null
)
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1)
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,0) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,0) –succeed
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) –fails
go
drop index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1) WHERE bit1 = 1 --Note the important WHERE clause here at the end of the index.
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) --succeeds because the unique constraint only enforces bit1 = 1.
go
select * from dbo.tabletestdups

And here is the output:

Note that rows four and seven have allowed duplicate combination of key1 =1, key2= 1 and bit1 = 0, but that previous attempts to insert a duplicate combination of key1 =1, key2= 1 and bit1 = 1 unsuccessful.

HostForLIFE.eu SQL Server 2014 Hosting
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 customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2014 Hosting Italy - HostForLIFE.eu :: How to Check SQL Server CPU usage?

clock February 26, 2015 06:50 by author Peter

There are some condition &amp; times once SQL Server CPU usage exceeds threshold value (ex 80th or more) and keep constant even until 100 percent for a undefined duration. A DBA don't get notified mechanically concerning performance degrade till its according by application team or service desk.

This typically happens once all in explosive unannounced application work goes to SQL Server for processing; but at smart range of times, it’s as a result of some T-SQL Queries are taking longer than expected or as a result of bad execution plan or block. There is also a lot of reason to add, but ultimately high processor usage on production systems goes to harm performance.

Given below could be a script that you simply will convert into stored procedure and call from SQL Server agent job, regular for each five minutes or additional to ascertain CPU usage on a vital info server. This script uses sys.dm_os_ring_buffers to spot CPU usage from last five minutes and if average CPU usage goes beyond 80th then send an alert email together with top 50 CPU consuming SQL sessions and details. And now change the 3rd code line to: SELECT @TimeNow = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info

SCRIPT
SET NOCOUNT ON
DECLARE @TimeNow bigint
SELECT @TimeNow = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info
-- Collect Data from DMV
Select record_id, dateadd(ms, -1 * (@TimeNow - [timestamp]),
GetDate())EventTime, SQLSvcUtilization, SystemIdle,
(100 - SystemIdle - SQLSvcUtilization) AS OtherOSProcessUtilization into #tempCPURecords
from ( select record.value('(./Record/@id)[1]', 'int')record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')SystemIdle, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')SQLSvcUtilization,
timestamp
from ( select timestamp, convert(xml, record)record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%')x )y  order by record_id desc
-- To send detailed sql server session reports consuming high cpu
-- For a dedicated SQL Server you can monitor 'SQLProcessUtilization'
-- if (select avg(SQLSvcUtilization) from #temp where EventTime>dateadd(mm,-5,getdate()))>=80
-- For a Shared SQL Server you can monitor 'SQLProcessUtilization'+'OtherOSProcessUtilization'
if (select avg(SQLSvcUtilization+OtherOSProcessUtilization)
from #tempCPURecords where EventTime>dateadd(mm,-5,getdate()))>=80
begin
print 'CPU Alert Condition Ture, Sending Email..'
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>High CPU Utilization Reported</H1>' +
    N'<H2>SQL Server Session Details</H2>' +
    N'<table border="1">' +
    N'<tr><th>SPID</th><th>Status</th><th>Login</th><th>Host</th><th>BlkBy</th>'+
   N'<th>DatabaseID</th><th>CommandType</th><th>SQLStatement</th><th>ElapsedMS</th>'+
   N'<th>CPUTime</th><th>IOReads</th><th>IOWrites</th><th>LastWaitType</th>'+
N'<th>StartTime</th><th>Protocol</th><th>ConnectionWrites</th>'+
N'<th>ConnectionReads</th><th>ClientAddress</th><th>Authentication</th></tr>'+
CAST ( ( SELECT  TOP 50 -- or all by using *
td= er.session_id,'',
td= ses.status,'',
td= ses.login_name,'', 
td= ses.host_name,'',  
td= er.blocking_session_id,'', 
td= er.database_id,'', 
td= er.command,'', 
td= st.text,'', 
td= er.total_elapsed_time,'', 
td= er.cpu_time,'', 
td= er.reads,'', 
td= er.writes,'', 
td= er.last_wait_type,'', 
td= er.start_time,'', 
td= con.net_transport,'', 
td= con.num_writes,'', 
td= con.num_reads,'', 
td= con.client_net_address,'', 
td= con.auth_scheme,'' 
FROM sys.dm_exec_requests er 
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st 
LEFT JOIN sys.dm_exec_sessions ses 
ON ses.session_id =er.session_id  LEFT JOIN sys.dm_exec_connections con 
ON con.session_id = ses.session_id 
WHERE er.session_id > 50 
ORDER BY er.cpu_time DESC ,er.blocking_session_id
FOR XML PATH('tr'), TYPE
)AS NVARCHAR(MAX))+
N'</table>'
-- Change SQL Server Email notification code here
EXEC msdb.dbo.sp_send_dbmail
@recipients='dk@sqlknowledge',
@profile_name = 'SQLProfileName',   
@subject = 'ServerName:Last 5 Minutes Avg CPU Utilization Over 80%',
@body = @tableHTML,
@body_format = 'HTML';
END
-- Drop the Temporary Table
DROP Table #tempCPURecords

HostForLIFE.eu SQL Server 2014 Hosting
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 customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL 2014 Hosting Italy - HostForLIFE.eu :: Change Primary Key Column for Existing Table with MSSQL

clock February 24, 2015 05:04 by author Peter

In this post, I will tell you how to change primary key column for existing table in MSSQL 2014. First, you must create table User_Details with the code below:

CREATE TABLE [dbo].[Users_Details](
            [Username] [nchar](20) NOT NULL,
            [Password] [nchar](20) NOT NULL,
            [Email] [nchar](30) NULL,
            [Mobile] [nchar](15) NULL,
            [Address] [nchar](100) NULL,
            [USER_ID] [int] IDENTITY(1,1) NOT NULL,
            [Gender] [varchar](15) NULL,
            [Country] [varchar](50) NULL,
    CONSTRAINT [PK_Username] PRIMARY KEY CLUSTERED
    (
            [Username] ASC
    )
    )

And the change the column as
ALTER TABLE Users_details
ALTER COLUMN USERname NVARCHAR(100)

Now, Run it. It will show the error. Because Username column contains Primary Key.
Msg 5074, Level 16, State 1, Line 1
The object 'PK_Username' is dependent on column 'Username'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Username failed because one or more objects access this column.

First Drop the Primary Key Constraint and alter table and add  Primary Key Constraint
ALTER TABLE Users_Details
DROP CONSTRAINT PK_Username
ALTER TABLE Users_Details
ALTER COLUMN Username NVARCHAR(100) NOT NULL
ALTER TABLE Users_Details
DD CONSTRAINT PK_Username PRIMARY KEY(Username)


Hope this tutorial works for you!

HostForLIFE.eu SQL Server 2014 Hosting
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 customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



European ASP.NET Hosting - UK :: Simple ASP Script to Send Email Using ASPEmail

clock February 23, 2015 07:22 by author Scott

Simple ASP Script to send mail using AspEmail

The following is a very basic script to send a mail from an .asp page (we will build on this mail in the next scripting example to retrieve and e-mail information from a form). Note that lines starting ' are explanatory only - these lines are not parsed by the server.

<%

' First Step is to create the AspEmail message object
Set Mail = Server.CreateObject("Persits.MailSender")

' Set the from address - replace value within the quotes with your own
Mail.From = "[email protected]"

' Add the e-mail recipient address - again replace value within the quotes with your own
Mail.AddAddress "[email protected]"

' Set the subject for the e-mail
Mail.Subject = "Test mail via AspEmail"

' Create the body text for the e-mail
Mail.Body = "This mail was sent via AspEmail"

' The mail server requires that we authenticate so supply username and password
Mail.Username = "[email protected]"
Mail.Password = "your_password"

' The e-mail is now ready to go, we just need to specify the server and send
Mail.Host = "smtp.hostforlife.eu"
Mail.Send

' Mail is sent - tidy up and delete the AspEmail message object
Set Mail = Nothing

%>

That's it - you just need to substitute your own values where required and you should be able to copy this script to your account and send your first e-mail.

Note that as we use authentication the 'Mail.From' e-mail address must be a live user on our mailserver and should match the address used for the 'Mail.Username'.

 



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