In this article, we will see about fixed database roles in SQL Server. As the name suggests, fixed database roles cannot be removed or modified and performs specific administrative tasks as it has pre-defined set of permissions. You need to be very careful while assigning these roles and assign these roles only when there is a serious requirements. You can execute the sp_helpdbfixedrole system procedure to get the list of fixed database roles.

EXEC sp_helpdbfixedrole
GO

• db_owner
• db_accessadmin
• db_securityadmin
• db_ddladmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_denydatareader
• db_denydatawriter

You can navigate to Database Roles from Object Explorer –> Expand Databases Node –> Select Database –> Navigate to Security –> Roles –> Database Roles

db_owner

All the members of db_owner fixed database roles can perform all the maintenance activities and setting configurations. This role should not be given to regular users and should be assigned very carefully as it can perform almost all the operations in a database.

db_accessadmin

As the name suggests, all the members of db_accessadmin can handle access related issues. This role controls security, grants access, revokes access for logins to enter database. This role is rarely used as these operations are performed by DBAs as he has relevant fixed server role.

db_securityadmin

Members of db_securityadmin role manages all the permissions and security related activities. As database administrators usually manages security, permissions, role membership etc. so this role is hardly assigned and used. You should not assign this role to regular users.

db_ddladmin

If any member is assigned with db_ddladmin role then he can perform all DDL operations and can execute, create, drop and alter any objects. Normally this role is assigned to developers to perform the related operations in application. This role is usually not assigned to regular users as he can misuse the DDL operations.

db_backupoperator

db_backupoperator role can perform the database backup operations. This role is rarely used as backup activity is a role of database administrator and he has much higher permissions rather than using this specific fixed database role.

db_datareader

If any user is requesting for SELECT permission on database tables then you can tag him with db_datareader fixed database role as this role allows a member to perform SELECT operations on database tables and views and tagged member will not be able to modify any object. This role is mostly assigned to developers and regular users who need table access on production database.

db_datawriter

All the members of db_datawriter fixed database role can perform INSERT, UPDATE, DELETE operations on all tables and views in respective database. This role is basically assigned to developers to perform operations on QA servers. Testing applications sometimes require this role.

db_denydatareader

As the name suggests this role doesn’t allow to read data from tables in a database. So user will not be able to perform SELECT operations on a table or views. I haven’t used this role any time and also never seen members assigned with this role.

db_denydatawriter

This role is basically opposing db_datawriter role which means members with this role will not be able to perform INSERT, UPDATE or DELETE operations on tables and views. Again as a DBA I have not used this role yet and never seen anyone using this role.

HostForLIFE.eu SQL Server 2012 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.

In this tutorial, I will explain about how to make time converter. The time converter converts the most common time units, second, hour, minute, day, week, month, year and century. Second is the SI base unit of time and officially defined as the duration of 9 192 631 770 periods of the radiation corresponding to the transition between the two hyperfine levels of the ground state of the caesium 133 atom.

We have an integer number which represents seconds (secs) part of the time. We need to convert this integer number into year, month, days, hour, minute and second parts. Let me explain with the help of the examples to make it clear.

Examples:

100 Seconds – 0 year(s) 0 month(s) 0 day(s) 0 hour(s) 1 minute(s) 40 second(s)

3600 Seconds – 0 year(s) 0 month(s) 0 day(s) 1 hour(s) 0 minute(s) 0 second(s)

86400 Seconds – 0 year(s) 0 month(s) 1 day(s) 0 hour(s) 0 minute(s) 0 second(s)

2678400 Seconds – 0 year(s) 1 month(s) 0 day(s) 0 hour(s) 0 minute(s) 0 second(s)

35110011 Seconds – 1 year(s) 1 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

etc.

Convert number into year, month, day, hour, minute and second

Below is the code to convert the input integer number into year, month, day, hour, minute and second as above:

DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEPART(YEAR, @VARDT) - 1900 AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Or, we can use the below code.


DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEDIFF(YEAR, 0, @VARDT) AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
 + CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

I have added the given integer value in second part of the default datetime value (‘1900-01-01 00:00:00.000’) of SQL Server to generate a datetime value. Once we have a datetime data type, we can easily apply the DATEPART function to extract the required parts from that. In this scenario SQL Server automatically takes care of the date time conversions. Finally, I have subtracted the respective date time parts from the generated datetime data type value from their respective parts in the final SELECT statement wherever required.

In second logic, I have just used a DATEDIFF function for the first step computation which can take care of all the conversions specially in below scenarios smoothly. For example, we don’t need to worry about to year to month, month to day and day to hour conversion. We just need to use DATEDIFF function for the first part.

Convert number into month, day, hour, minute and second

SELECT CAST((DATEDIFF(MONTH, 0, @VARDT)) AS VARCHAR(10)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

13 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into day, hour, minute and second

SELECT CAST((DATEDIFF(DD, 0, @VARDT)) AS VARCHAR(10)) + ' day(s) '
 + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

406 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into hour, minute and second

SELECT CAST(DATEDIFF(HOUR, 0, @VARDT) AS VARCHAR(20)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

9752 hour(s) 46 minute(s) 51 second(s)

Convert number into minute and second

SELECT CAST(DATEDIFF(MINUTE, 0, @VARDT) AS VARCHAR(25)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

In this tutorial I will show you Stored Procedure usage with step by step. First of All, you have good knowledge about SQL Server  after that, you will understand as well. Basically, Stored Procedure which is used in SQL Server, SQL Server is the product of Microsoft. Stored Procedure is mostly used with Insert, Update and Delete Data in Your Tables. If you make a table and insert update and delete record a records you have used insert update and delete query simply  again and again but Stored Procedure different. Stored Procedure you make a one time simply after that you don’t use long insert update and delete query. Stored Procedure is very helpful to make a Web Application or Desktop Application with the help of ASP.NET.

How to Make a Stored Procedure

First of All, you make a table, for example, I have made a Table namely Employee and I have inserted five columns ID_Number, Name_in_Full, Designation and Salary in my table you are see below with query and screenshot.
Make a Table in SQL Server
create table employee
(
ID_Number int primary key identity(1,1),
Name_in_Full varchar(50),
Employee_Number varchar(50),
Designation varchar(50),
Salary int
)

I have made a table  with you can see table query and display screen shot, after that I have inserted data in a table.
Insert into employee values
(‘Asif Ali’, ‘5050’, ‘Web Developer’, 250000),
(‘Aamir Ali’, ‘5051’, ‘Businessman’, 250000),
(‘Asadullah’, ‘5052’, ‘Network Engineer’, 150000),
(‘Sameer Ali’, ‘5053’, ‘Accountant’, 450000)

I have made the table and insert a record in the table.

Create Stored Procedure in SQL Server

I have made a Stored Procedure namely CreateProc and I have used same column name with I have already used in a table below you can see complete query and screenshot. 

create procedure createProc
(@Name_in_Full varchar(50),
@Employee_Number varchar(50),
@Designation varchar(50),
@Salary int)
as
begin
insert into employee values(@Name_in_Full, @Employee_Number, @Designation, @Salary)
end

After that I have made complete Stored Procedure after that I have inserted a data in Table via Stored Procedure, complete query and screen shot as below.

Afer Run above query my data is inserted in my table with the help of SP

Alter Stored Procedure in SQL Server

After that I have changed with column data type I have used with Alter Query and change my column DataType, the complete query, and screen shot as below.

alter procedure createProc(@Name_in_Full varchar(50),@Employee_Number nvarchar(50),
@Designation varchar(50),@Salary int)
as
begin
insert into employee values(@Name_in_Full, @Employee_Number, @Designation, @Salary)
end

After that, I have made UpdateProcedure which is used with an update a record with your table with the help of SP, the query and screenshot as below.

Update Stored Procedure in SQL Server

create procedure updateProc
(@ID_Number int,
@Name_in_Full varchar(50),
@Employee_Number nvarchar(50),
@Designation varchar(50),
@Salary int)
as
begin
update employee set Name_in_Full=@Name_in_Full,
Employee_Number=@Employee_Number,
Designation=@Designation, Salary=@Salary where ID_Number=@ID_Number
end

After that, I have update a Record with Table with the help of SP, the complete query, and screenshot as below.

exec updateProc 5, ‘Sadam Hussain’, ‘50504’, ‘Chemical Engineer’, 250000

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.

In this tutorial, I will show you how to create Web Services Using C#. This service will help you to communicate with the server.Windows Communication Foundation (WCF) is a framework for building service-oriented applications.

Using WCF, you can send data as asynchronous messages from one service endpoint to another. A service endpoint can be part of a continuously available service hosted by IIS, or it can be a service hosted in an application. An endpoint can be a client of a service that requests data from a service endpoint. The messages can be as simple as a single character or word sent as XML, or as complex as a stream of binary data.

Now, write the following code:
    using System; 
    using System.Collections.Generic; 
    using System.Linq; 
    using System.Web; 
    using System.Web.Services; 
    using EntityLayer; 
    using BusinessLayer; 
    namespace WebApplication1  
    { 
        /// <summary> 
        /// Summary description for WebService1 
        /// </summary> 
        [WebService(Namespace = "http://hostforlife.eu")] 
        [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] 
        [System.ComponentModel.ToolboxItem(false)] 
        // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
        // [System.Web.Script.Services.ScriptService] 
        public class WebService1: System.Web.Services.WebService  
        { 
            [WebMethod] 
            public PurchaseEntity item(PurchaseEntity Vp)  
            { 
                PurchaseBusiness ObjectPurchaseOrder = new PurchaseBusiness(); 
                return ObjectPurchaseOrder.item(Vp); 
            } 
            [WebMethod] 
            public PurchaseEntity Insert(PurchaseEntity obj1)  
            { 
                PurchaseBusiness ObjectPurchaseOrder = new PurchaseBusiness(); 
                return ObjectPurchaseOrder.Insert(obj1); 
            } 
            [WebMethod] 
            public PurchaseEntity Delete(PurchaseEntity obj2)  
            { 
                PurchaseBusiness ObjectPurchaseOrder = new PurchaseBusiness(); 
                return ObjectPurchaseOrder.Delete(obj2); 
            } 
            [WebMethod] 
            public PurchaseEntity Update(PurchaseEntity pe)  
            { 
                PurchaseBusiness ObjectPurchaseOrder = new PurchaseBusiness(); 
                return ObjectPurchaseOrder.Update(pe); 
            } 
        } 
    } 

HostForLIFE.eu WCF 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.

In this article I will make tutorial about how to make read, write and delete cookies in ASP.NET WebPages 3.1. Cookies are a small text file containing information about a user; for example, a cookie may contain information on the last video the user watched or what items they have in their shopping cart. Many e-commerce websites like Amazon save cookies to your computer that can either collect your data or store the items in the shopping cart.

In Microsoft WebMatrix create a new website based of the Personal Site template name the website Cookies. Next in the root folder create a page called Settings.cshtml.

Writing a Cookie

Writing, reading and deleting a cookie are all fairly simple, as will be displayed in this tutorial. We will give the user an option to choose between two site layouts, and once they select which one they prefer a cookie will be saved to their computer. This will be read by the main master page and it will load the correct style sheet.

Writing Cookie Syntax

Response.Cookies["Cookie Name"].Value = "Value";
Response.Cookies["Cookie Name"].Expires = DateTime.Now.Add (Either, days, months, years, minutes etc);
If you do not specify when the cookie expires then it will automatically expire at the end of the session (when the user closes their browser).

In the Settings page insert this code:

C# Code:

@{

if (IsPost){   

    var layout = Request["layout"];

    if (layout == "Dark"){
         Response.Cookies["Theme"].Value = "Dark";
         Response.Cookies["Theme"].Expires = DateTime.Now.AddYears(1);    
    }
  }

}

Here we have var layout which requests the HTML control layout. After the user is given an option, if they choose Dark a cookie with the name Theme and value Dark will be saved to their browser, and it expires in one year.

HTML Code:

<form method="post">

<fieldset>
<legend><h4>Choose Layout</h4></legend>
    <label>Select Layout</label>

    <select name="layout">
    <option value="Dark">Dark</option>
    <option value="Default">Default</option>
    </select>

    <input type="submit" />
</fieldset>
</form>
Leave the option default for now because we will come back to it. Now run the page and select Dark as your theme; the page will refresh. Then in your chosen browser go to your stored cookies and you should see that a new cookie has been made called Theme (this should be under a folder called localhost by default) with the value Dark. For chrome users simply copy and paste this in the address bar of your browser: chrome://chrome/settings/cookies.

Reading Cookies

The domain which inserts the cookie can read the cookie. So for example, if thecodingguys.net writes a cookie called "Theme" Google.com cannot read that cookie. This will apply to sub-domains as well; m.thecodingguys.net cannot read a cookie which was created by www.thecodingguys.net.
Now the cookie should be inserted in your browser. Before we can read the cookie we need to make a new layout, so we will make a new style sheet. In the content folder copy the Site.css file and paste it in that folder, and re-name it to Site_Two.css. Next open the Site_Two.css find the body style and change the background colour to a colour of your choice (make sure it is different than the default).

Navigate to the layouts folder and open the _SIteLayout.cshtml file. Find the linked style sheet (below title tag) and replace it with:

C# Code:

@if (Request.Cookies["Theme"] != null){//MAKE SURE COOKIE EXIST

    if (Request.Cookies["Theme"].Value == "Dark"){//READ COOKIE VALUE
         <link href="~/Content/Site_Two.css" rel="stylesheet" />
    }
}else{
  <link href="~/Content/Site.css" rel="stylesheet" />   //IF COOKIES DOES NOT EXIST LOAD DEFAULT STYLESHEET
}
The first If Statement requests the cookie by name, not the cookie value but just the cookie named Theme. We need to make sure it is not null (meaning it exists), and then if it exists read the value. If the value is Dark, and it will load the second style sheet. Now if the cookie is not there it will load the default style sheet.

Deleting Cookies

Return to the Settings.cshtml file and add this to the If Statement:

C# Code:

else if (layout == "Default"){
         Response.Cookies["Theme"].Expires = DateTime.Now.AddYears(-1);
    }
Deleting a cookie is simple: on submit if the user selects the Default option the cookie is deleted. There is no direct command to delete the cookie, all we do is set the expiry date to a previous date. In this case it is set to the year before meaning it has expired and the browser will delete it.

Summary

Reading, writing and deleting cookies is quite simple. Cookies are very useful when you want to have full control overs users and provide extra functionality. Remember that cookies are client sided so users can disable them if they want to. Also remember that there are some laws (EU Law) regarding cookies, so make sure you inform users you are saving cookies.

HostForLIFE.eu ASP.NET WebPages 3.1 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

In this tutorial, I will explain how to fix unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime, Fatal error: cannot create ‘R_TempDir’. R is the most popular programming language for advanced analytics today. You can use it for statistical analysis, uncover patterns, identify trends or even build predictive models. R has a massive collection of free packages on CRAN created by an ever-growing worldwide developer community.

In the latest SQL Server 2016 CTP3 release, new native in-database support for open source R and multi-threaded RevoScaleR functions are now available via R Services. SQL Server R Services provides a platform for using the powerful R language and package portfolio with a simple Transact-SQL interface. To overcome open source R’s performance and scale limitations, Enterprise Edition includes RevoScaleR features.

Data scientists, BI developers and applications can make parameterized calls to the R runtime from SQL code or stored procedures to get R computed result sets or data visualizations. In SQL Server 2016 CTP3, support for ad-hoc execution of R scripts via a new system stored procedure, sp_execute_external_script, was added. It will currently support pushing data from a single SELECT statement with multiple input parameters to R and return a single data frame as output.

• Locate launcher config file (this is used by the MSSQL Launch pad service)
  
  • Its normally located in the Binn folder instance the which is available in the root directory in my case D: \ Program Files \ Microsoft SQL Server \ MSSQL 13.MSSQLSERVER \ MSSQL \ Binn
• View the contents of rlauncher and check the working directory of the which is where we are having issues creating temp dir.
  
  • R does not like spaces in the working directory
    

  

• Create a folder in C:\ called temp “C:\temp”
• Change the working directory to c:\temp
  

• Grant permissions for the service MSSQLaunchpad service account in my case its NT Service\MSSQLaunchpad
  



Restart the SQL server services and SQL Launch pad services.
We can also fix this by using short paths by replacing the working directory with a short path. Example below cmd will give us the short path for the location cmd /c for %A in (“D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData”) do @echo %~sA


 

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

Dependency Injection occurs when a software component (in this case, the class) given their dependencies through their constructors, methods, or directly into the fields. Simply put, Dependency Injection is passing or setting of dependencies into a software component (Dependency Injection Container). In other words, if a class can not do his job without a dependency, then there was a dependency injection. Classes are diinject, not only can build the object, but also its behavior.

Dependency injection can be done through three ways :

• Constructor Injection
• Setter Injection
• Interface Injection

ASP.NET 5 has dependcy injection available at framework level and ASP.NET 5 makes heavy use of it. Most of things surrounding controllers, views and other MVC components are implemented as services that web applications consume. This post is quick overview of dependency injection in ASP.NET 5 with some examples.

Registering services

Services are registered when application starts. It happens in Startup class. There is method called ConfigureServices() and in the end of this method I usually define service mappings.

public virtual void ConfigureServices(IServiceCollection services)
{
    // configure services
 
    var settings = new Settings();
 
    // initialize custom settings
 
    services.AddInstance(settings);
    services.AddScoped<IFileClient, AzureFileClient>();
    services.AddScoped<IMapperSession, MapperSession>();
    services.AddScoped<IProductService, ProductService>();
}

Available scopes for services are:

• Singleton – always return same instance.
• Transient – return new instance every time.
• Scoped – return same instance in current scope (it’s like singleton in current scope – think about requst scope by example).
• Instance – specific instance is returned every time and it’s up to you how it is actually ceated.
• Services registered during application start-up are available to all classes invoked through dependency injection.

Although we can inject services to whatever classes you need there are some things made very convenient for us. Let’s see now how injection works with controllers and views.

Injecting services to controller

We don’t need custom controller factories anymore if we don’t use some other dependency injection container. Also we don’t have to dig around in system variables and classes to find settings we need. We can do it all through dependency injection. Here is the example how to provide environment information and some custom services to controller.

public class HomeController : Controller
{
    private readonly IApplicationEnvironment _appEnvironment;
    private readonly ShopContext _shopContext;
    private readonly IProductService _productService;
 
    public HomeController(ShopContext shopContext,
                          IProductService productService,
                          IApplicationEnvironment appEnvironment)
    {
        _appEnvironment = appEnvironment;
        _shopContext = shopContext;
        _productService = productService;
    }
 
    public IActionResult Index()
    {
        return View();
    }

The code here uses controller injection – the only injecton mode supported by ASP.NET right now. We don’t have to do anything special for dependency injection to happen. We just make sure we register our custom services at application startup.

Injecting services to views

It’s also possible now to inject services to views. There’s new syntax for this.

@model ProductCategoryMenuModel
@inject ShopContext ShopContext

<h1>@ShopContext.PageTitle</h1>
 
<ul>
    <!-- write out categories here -->
</ul>

tells to view engine that we want instance of ShopContext to be injected to view and we name it as ShopContext. Perhaps it’s not a good practice to name variable as type in view but still it communicates the purpose of variable well.

Conclusion

Framework level dependency injection in ASP.NET 5 is very transparent and configuration is simple. First register type mappings in application start-up and then we use constructor injection to get instances to our classes. This way we can use classes by interfaces and we don’t have to create instances in our own code. On MVC side we can use dependency injection for controller, views and view components.

HostForLIFE.eu ASP.NET 5 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.

In this tutorial, I will explain optimize tempdb performance. Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down.

But before we start, there are things you should know :

• If your SQL Server instance doesn’t employ many of the activities just described, then tempdb performance may not be an issue for you.
• On the other hand, if your SQL Server instance uses many of these features, then tempdb could become a significant bottleneck for your SQL Server instance.
• Only by investigation will you know.
• Keep in mind that there is only one tempdb, and it is possible for one misbehaved application and database to indirectly affect the performance of all the other databaseson the same instance.

Tempdb Internals

• Tempdb is dropped and recreated every time the SQL Server service is stopped and restarted.
  
• When SQL Server is restarted, tempdb inherits many of the characteristics of model, and creates an MDF file of 8MB and an LDF file of 1MB.
  
• Autogrowth is set to grow by 10% with unrestricted growth.
• Each SQL Server instance may have only one tempdb, although tempdb may have multiple physical files.
• Tempdb often doesn’t act like other databases
• Tempdb only uses the simple recovery model.
• Many database options can’t be changed (e.g. Database Read-Only, Auto Close, Auto Shrink).
• Tempdb may not be dropped, detached, or attached.
• Tempdb may not be backed up, restored, be mirrored, have database snapshots made of it, or have many DBCC commands run against it.
• Tempdb logging works differently from regular logging. Operations are only minimally logged with enough information to roll back transactions, but not to be rolled forward. The log is truncated constantly, although it can grow with long-running transactions.

Execution Plans and tempdb

• When a query execution plan is cached, the tempdb work tables required by the plan, if any, are often cached.
• When a work table is cached, the table is truncated (from the previous execution of the code) and up to nine pages remain in the cache for reuse.
• This improves the performance of the next execution of the query.
• If the system is low on memory, the Database Engine removes the execution plan and drops the associated work tables.

Types of tempdb Problems

Generally, there are three major problems you run into with tempdb:

• Tempdb is experiencing an I/O bottleneck, hurting server performance.
• Tempdb is experiencing DDL and/or allocation contention on various global allocation structures (metadata pages) as temporary objects are being created, populated, and dropped. E.G. Any space-changing operation (such as INSERT) acquires a latch on PFS, SGAM or GAM pages to update space allocation metadata. A large number of such operations can cause excessive waits while latches are acquired, creating a bottleneck, and hurting performance.
• Tempdb has run out of space.

Ideally, you should be monitoring all these on a proactive basis.

Identifying tempdb I/O Problems

• Use Performance Monitor to determine how busy the disk is where your tempdb MDF and LDF files are located.
• LogicalDisk Object: Avg. Disk Sec/Read: The average time, in seconds, of a read of data from disk. Numbers below are a general guide only and may not apply to your hardware configuration.
• Less than 10 milliseconds (ms) = very good
• Between 10-20 ms = okay
• Between 20-50 ms = slow, needs attention
• Greater than 50 ms = serious IO bottleneck
• LogicalDisk Object: Avg. Disk Sec/Write: The average time, in seconds, of a write of data to the disk. See above guidelines.
• LogicalDisk: %Disk Time: The percentage of elapsed time that the selected disk drive is busy servicing read or write requests. A general guideline is that if this value > 50%, there is an I/O bottleneck.
• SQL Server Database: Log Bytes Flushed/sec: The total number of log bytes flushed. A large value indicates heavy log activity in tempdb.
• SQL Server Database: Log Flush Waits/sec: The number of commits that are waiting on log flush. Although transactions do not wait for the log to be flushed in tempdb, a high number in this performance counter indicates an I/O bottleneck on the disk associated with the log.

Identifying Contention on Allocation Structures

Use these performance counters to monitor allocation/deallocation contention in SQL Server:

• Access Methods:Worktables Created/sec: The number of work tables created per second. Work tables are temporary objects and are used to store results for query spool, LOB variables, and cursors. This number should generally be less than 200, but can vary based on your hardware.
• Access Methods:Workfiles Created/sec: The number of work files created per second. Work files are similar to work tables but are created by hashing operations. Work files are used to store temporary results for hash joins and hash aggregates.
• Temp Tables Creation Rate: The number of temporary tables or variables created/sec.
• Temp Tables For Destruction: The number of temporary tables or variables waiting to be destroyed by the cleanup system thread.

Add More RAM to Your Server

• Depending on the operation, SQL Server tries to perform the action in the buffer cache. (e.g. sorts, CTEs)
• If the buffer cache does not have enough available space, then the operation may have to spill to tempdb.
• This places additional overhead on tempdb.
• If your server is experiencing a memory bottleneck, then adding RAM can help reduce the load on tempdb.
• On the other hand, if your server has plenty of memory, adding more won’t help tempdb performance.

How to Pre-allocate tempdb Space

Use ALTER DATABASE, or SSMS, to increase the size of the tempdb database MDF and LDF files.

    USE master;
    GO
    ALTER DATABASE tempdb
    MODIFY FILE
    (NAME = tempdev,
    SIZE = 20MB);
    GO
    ALTER DATABASE tempdb MODIFY FILE
    (NAME = templog, SIZE = 10MB)
    GO

Locate tempdb on Fast I/O Subsystem

• Always locate tempdb on the fastest I/O subsystem you have available.
• Prefer RAID 1 or RAID 10. RAID 5 is slow for writes and should generally be avoided for tempdb, as tempdb is often write-intensive.
• If using a SAN, consult with a SAN engineer to ensure that tempdb won’t be affected by other disk I/O.
• Consider SSD drives for tempdb MDF and LDF files.

How to Move tempdb

• Determine the current location of the MDF and LDF files
• Run the ALTER DATABASE command to move the files

Avoid Using TDE

• SQL Server 2008 offers a new database-level encryption feature called Transparent Database Encryption (TDE).
• If this is turned on for one or more databases on a SQL Server instance, then all the activity in tempdb (whether it comes from a encrypted or non-encrypted database) will be encrypted.
• Encryption increases CPU usage and slows down tempdb performance.
• If you decide to use TDE, you will want to incorporate as many of the tempdb performance tuning tips that I have suggested in order to help overcome the additional burden added by TDE.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.

SQL Server Migration is a mixing process prior to SQL Server implementation newer version to a different location due to strategic decisions (such as SQL Server Consolidation). So in this article, I will explain about things to check before planning to migrate your SQL Server, from SQL Server 2008 to SQL Server 2014.

Before migrating your sql server, there are several things you need to know :

• Your Operating System must be at least 2008. Because SQL 2014 cannot be installed on Windows 2003.
• Your platform must be the same. It cannot go from 32 to 64 or 64 to 32.
• The edition cannot downgrade.
• Remember, you can only migrate from 2008.

Please note that these steps are specific for an upgrade to the database schema and data. They do not include anything regarding the upgrading or testing of an application that is going to be accessing the upgraded database. You will want to remember to test your application and not just assume it will work perfectly even after the database has been upgraded. I would also advise that you perform these steps in a non-production environment first because I often find that common sense isn’t so common after all.

• EXECUTING DBCC

Your upgrade tasks should be to run the following statement:

DBCC CHECKDB WITH DATA_PURITY;

This will check your data for values that are no longer valid for the column datatype. For databases created prior to SQL 2005, this step is rather important to take. For databases created in SQL 2005 and later, the DATA_PURITY check is supposed to be done automatically with a regular CHECKDB.

• EXECUTING DBCC UPDATEUSAGE COMMAND

This one has a place in any migration or upgrade process:

DBCC UPDATEUSAGE(db_name);

This command will help to fix any page count inaccuracies that are resulting in the sp_spaceused stored procedure returning wrong results. For SQL Server 2012, this check was recommended for databases created prior to SQL Server 2005. However, in SQL Server 2014, the BOL entry link lists this command as being applicable for databases created in SQL Server 2008 and later. That seems odd to me, since this command is valid for SQL Server 2005.

• UPDATING STATISTIC

This one is a MUST for any migration or upgrade checklist:

USE db_name;
GO
EXEC sp_updatestats;

This command will update the statistics for all the tables in your database. It issues the UPDATE STATISTICS command, which warrants mentioning because you may want to use that command with the FULLSCAN option. Don’t forget to update the statistics after an upgrade. Failure to do so could result in your queries running slowly as you start your testing and may end up wasting your time while you try to troubleshoot the possible bottlenecks. With SQL Server 2014 there is also a new Cardinality Estimator (CE).

• REFRESHING YOUR VIEWS USING SP_REFRESHVIEW

Someone will build a view that spans into another database on the same instance. And, in what may be a complete surprise to many, sometimes these views will go across a linked server as well. The point here is that your view may not be of data that is contained in just the database on that single instance. In what could be the most dramatic twist of all, sometimes these views are created using a SELECT * syntax.

When you have bad code on top of views that go to other databases, you are going to want to use sp_refreshview to refresh those views. So, if you are migrating a database in your environment to a new server then it would be a good idea to refresh your views using sp_refreshview. Most of the time it won’t do anything for you, just like a burger topped with veggie bacon. But there is that one chance where it will dramatically improve performance and your customer will be happy as a result. Using sp_refreshview is a lot like flossing: it doesn’t take much effort, and the end result is usually worth it.

• TAKING BACKUPS

You should have taken one prior to the start of any upgrade or migration, and you had better take one right before you turn that database over to your end users. Also, you should save any output from the items listed here, as it could prove helpful should something go awry later.

• UPGRADING YOUR HARDWARE

Microsoft lists the minimum requirements for installing SQL Server 2014. However, chances are if your servers don’t already meet those requirements then you aren’t looking to upgrade anytime soon. But if you are upgrading, then it might be time to upgrade your hardware as well. You may even consider going virtual (if you aren’t already), which will still require you to examine your hardware requirements.

• KNOWING THE RIGHT UPGRADE PATH

If you are running SQL Server 2000 instances. You are not able to upgrade directly to SQL Server 2014 without first upgrading to an intermediary version. You have two options to choose from when going from pre-SQL Server 2005 versions. The first option is to do an upgrade in place to SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. The second option is to do a backup (or even detach) your database and restore/attach to an instance running SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. At that point you will be able to complete the upgrade to SQL 2014.

• CHECK YOUR COMPATIBILITY LEVELS

If you have been going through SQL Server upgrades for the past ten years then you are likely to have noticed that the compatibility level does not get set to the newest version after the migration is complete. You need to manually set the compatibility level yourself. With SQL Server 2014 this becomes more important than in previous versions due to the new Cardinality Estimator (CE).

• READ THE RELEASE NOTES

Take a few minutes and read the release notes. They can be useful for you to review. It’s good to have as complete a picture as possible for the new version should something not work as expected, and there are details in the release notes you may not find elsewhere.

HostForLIFE.eu SQL 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.

Microsoft SQL Server is a relational database management system (RDBMS) products Microsoft. Primary query language is Transact-SQL is an implementation of SQL ANSI / ISO used by Microsoft and Sybase. SQL Server is generally used in the business world that has a data base of small to medium scale, but then developed with the use of SQL Server on large data bases.

Microsoft SQL Server and Sybase / ASE can communicate over the network by using a protocol TDS (Tabular Data Stream). Apart from that, Microsoft SQL Server also supports ODBC (Open Database Connectivity), and has a JDBC driver to the Java programming language. The other feature of SQL Server is the ability to create a database mirroring and clustering. In previous versions, MS SQL Server 2000 affected by the SQL Slammer computer worm which caused delays in access to the Internet on January 25, 2003.

SQL Server is the best solution of SQL that provides the capability for mission critical confidence, self-service Business Intelligence and can run on hybrid scenarios using cloud technology. SQL Server 2012 was launched with the simplification editions and licensing schemes. SQL Server is generally used in the business world that has a data base of small to medium scale, but what if at the time wanted to make turns SQL Server database can not connet to the server on the computer.

Cannot connect to XXXXXXX\SQLEXPRESS.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider).

• Go to the "SQL Server Configuration Manager".

• Then select the SQL Server Configuration Manager (local).

• Then click SQL Server Services, the right side will appear.

• Then right-click SQL Server (SQLEXPRESS) >> START. Then wait for the configuration is complete.

• Restart Server SQL Server Management Studio.
  

HostForLIFE.eu SQL Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.