Windows 2012 Hosting - MVC 6 and SQL 2014 BLOG

Tutorial and Articles about Windows Hosting, SQL Hosting, MVC Hosting, and Silverlight Hosting

SQL SERVER 2014 Hosting - ASPHostPortal :: How to use Output Parameters in SQL Server and ASP.NET

clock May 4, 2015 05:57 by author Mark

The out parameters in SQL Server, when used in Stored Procedures, allow developers to pass a value in the database to the front-end controls like label. They are most commonly used in web application development.
Let us discuss how to create and use them in ASP.Net with a practical example. First we will design our database.

  • Create a database in SQL Server.
  • Let us create a table with three columns, say username, password and confirmation password.

Create table logintable(username varchar(max),password varchar(max),confirmpassword varchar(max))

  • Let us create our Stored Procedure.

Here we have created a Stored Procedure named usplogintable with username, password and confirmpassword as input parameters. The next variable that I have created is the @error variable of varchar type. You can see the keyword "out" near the varchar. Yes, your guess is correct, the keyword "out" stands for the output parameter in SQL Server.

We will execute this Stored Procedure as a batch so we have begins and ends. Then, "set nocount on" avoids returning the number of rows affected.
The if condition checks whether the username exists in a database and if the answer for it is yes, the @error variable is set with the username already taken or it inserts the values into the table and sets the @error variable as the username inserted.

Executing the Stored Procedure also requres a different style. First you need to declare a variable, you need to specify the output parameter on execution and you need to write a select query at the end to make it execute. Here is my sample for the preceding sp.

I have inserted the table with the values markus, mark and confirm password as mark.

  • I can guess what you are thinking. “How can I use it in my server-side code?”. Yeah, I am an ASP.Net developer and I have the solution for this. Here are the ways.

Additionally, you must also open your Visual Studio or press Ctrl+r and type devenv.
Create an ASP.Net web application with the framework being above 2.0. First create a form in ASP.Net with three labels and three textboxes with names as username.password and confirmpassword as shown in the screen below.

Okay. Let me take you through a tour of the server-side code on it. I will use ADO.Net here for the database connectivity. I will add my logic on my button click. Add using statements for the namespaces System.data and System.Data.SqlClient since these are not the default namespaces in .Net.

Add the following code by double-clicking the submit button.

  • Here I have created the connection string in my fashion and you can use your own style in your application as usual.
  • Thats it. We are done. Press Ctrl+F5.
  • If you provide the inserted usename.
  • And if you provide a new username, yuppy, it is inserted.

Best SQL Server 2014 Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in SQL Server 2014 . We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL SERVER 2014 Hosting, we should be your best choice.

 



Windows 2008 Hosting with Free ASP.NET :: 3 Tips Best Practices windows cloud server security

clock April 21, 2015 06:27 by author Mark

Windows Cloud Server Security: 3 Useful Best Practices

More and more we are helping people get set up on  Windows cloud servers here at windows2008hosting.asphostportal.com .  While dedicated server solutions are the right choice for certain situations, clients are finding the benefits of cloud servers or, in many cases, a hybrid hosting solution to be the perfect fit for their hosting needs. With all this cloud talk, there are several technologies supported on Windows cloud servers that you may not have realized. These options can make the difference when configuring a cloud server solution that will best optimize your site or application.

Firewalls, VPN & SSL in the Cloud

The best practices of leveraging Virtual Private Networks (VPN), Secure Sockets Layers (SSL) and Firewalls to protect sensitive information on a Windows Cloud Server are recommend and also supported just as they would be with a physical dedicated server. If you’re unsure as to what any of these are and why they are necessary, the following will serve as a summary of each and their benefits.

What is VPN?

Virtual Private Networking is a secure and controlled method of connecting remote networks and users for the purpose of dealing with sensitive information. When using a VPN, all data is encrypted at the source, sent securely to the destination, then decrypted at the destination – assuring that only the previously approved source and destination people or systems can access the data. This is often a necessary element to incorporate in your hosting solution if you are an eCommerce company accessing sensitive back-end data like credit cards or orders, require PCI compliance, or simply need to securely work with remote clients or coworkers regarding sensitive information. A VPN connection allows you to guarantee identity through point-to-point connections and user authentication. While Windows cloud servers are virtual, they retain all the same VPN encrypting capabilities as a dedicated server, giving you complete control over security and privacy of your data. (For more information on Virtual Private Networking, get our free download: Introduction to Virtual Private Networking.

What is an SSL Connection?

The Secure Socket Layer protocol (SSL) ensures secure transactions between Windows cloud servers and browsers. This is particularly important anytime sensitive information must be transmitted over the open Internet. For example, any sign-up process where personal information is needed, (credit card information, personal data, etc.) would justify use of an SSL connection. You can recognize the use of an SSL connection when you see an “S” included in the URL (https://).
While the technical breakdown of an SSL can be somewhat complicated, it’s important to understand the basic concept of an SSL connection and how important it is in
maintaining security with sensitive information. 

What is a Firewall?

A firewall is a security solution designed to only allow safe and trusted connections to whatever it is set to protect. It does this by identifying several pieces of information for all incoming connections, thus blocking access to any unfamiliar or unauthorized sources looking to access that data. The difference between a firewall and an SSL connection is that SSL is used to ensure your sensitive information can be securely sent from a webpage to a server, while a firewall is designed to control direct access to the cloud server itself. A firewall can be used to lock down ports and allow only certain information to be accessible, while keeping other information secure.

Firewall in a Windows Cloud Server

In order to set up a firewall in a Windows cloud server configuration, various steps must be taken to optimize the firewall for the cloud – but depending on your hosting provider, these steps will be covered for you. Once these steps are taken, it is no different than a firewall configured for a dedicated physical server.
Note: In addition to base intrusion detection and firewall protection offered at our network core, our Windows Cloud Servers come with free built-in Windows Firewall service that can be used to lock down ports and restrict access with a focus on the specific needs of your server – assuring that only the ports needed are opened and only available to the users or systems that need the sensitive access.

Cloud Server Security Best Practices

The above security steps are important in and of themselves, but they also serve as a baseline for achieving PCI Compliance, something in which we at ASPHostPortal.com are well versed. So, whether you’re looking to sure up your security as a best practice, or find yourself needing to implement the above steps in accordance with the PCI Security Standard Council, Windows cloud server hosting offers all the tools available on a dedicated server configuration, but often with several added benefits.

Best Windows Server 2008 Hosting Recommendation

ASPHostPortal.com

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server 2008. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows server 2008 Hosting, we should be your best choice.



Windows 2012 Hosting with Free ASP.NET :: Simple Tips to Help Diagnose and Prevent Windows Server Crashes

clock April 16, 2015 06:04 by author Mark

Simple Tips to Help Diagnose and Prevent Windows Server Crashes

In an enterprise setting, server failures are never an issue to be taken lightly. Since servers are the backbone of most modern operations, being able to pinpoint the cause of errors rapidly is an essential skill for any Windows server administrator. Fortunately by following a few simple steps you can easily pinpoint the cause of most Windows Server errors.

Best Practice Analysis Automation

In Windows Server 2008 R2, Windows Server 2012 and Windows Server 2012 R2, Microsoft has included a tool known as the Best Practices Analyzer (BPA). BPA is an automated scanner which when run will check your server to ensure that it is configured for optimal performance, reliability and maximum security. While this tool is not a substitute for keeping atop of Windows Server trends, BPA helps to ensure that the most commonly exploited violations are blocked on your servers.

Proactive Actions to Simplify Troubleshooting

Enable Kernel Crash Dumps

One of the most important debugging tools within Windows is the kernel crash dump file. This file typically contains all the information necessary to pinpoint the cause of a server issue. Unfortunately this feature isn’t configured by default, making it crucial that you enable this feature before experiencing issues.
To enable this feature, follow the steps below:

  • Go into:  Control Panel > System and Security > System.
  • Click Advanced system settings.
  • Under Startup and Recovery, click Settings
  • In the box that pops up, under Write Debugging Information you can specify the location for the crash dump file
    • Additionally you can have crashes added to the Windows Event Log, and you can also trigger a restart automatically upon a crash

Enable Keyboard Crash Dump Commands

After configuring the Windows crash dump settings, you will need to setup Windows to enable your keyboard to cause a system crash.
With PS/2 keyboards, you must take the following steps:

  • In the registry key HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\i8042prt\Parameters, create a value named CrashOnCtrlScroll
  • Set it equal to a REG_DWORD value of 0x01.
  • Restart your system

With USB keyboards, you must take the following steps:

  • In the registry key HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\kbdhid\Parameters, create a value named CrashOnCtrlScroll
  • Set it equal to a REG_DWORD value of 0x01
  • Restart your system

Windows Debugging Tools

To help with common issues that might come up as a server administrator Microsoft has released multiple debugging tools to make the job easier.

Sysinternals Suite

The Sysinternals suite is like a Swiss Army knife for your troubleshooting efforts. This collection of seventy tools maintained by Microsoft ensures that you can easily pinpoint the source of virtually any error and take action accordingly. While this suite is best installed on a thumb drive to be used when errors occur, you can also load this suite on your servers early on as a proactive measure.

While there are many tools within the suite, there are a couple which you should keep on mind;

  • Disckmon – this tool monitors, logs and displays all hard disk activity on a Windows system. By using this tool, you can better pinpoint errors which might be caused by pending hard disk failures.
  • Procdump – this tool allows you to monitor applications for CPU spikes and generating crash dumps during a spike. Additionally the tool can serve as a general dump utility which can be included in other scripts. By using this tool, you can greatly expedite the process of diagnosing troublesome applications and figuring out where to start debugging efforts.
  • TCPView – this tool allows you to view detailed listings of all TCP and UDP endpoints on your system. By using TCPView, you can pinpoint server issues to specific ports, allowing you to more in-depth action accordingly.

Windows Memory Diagnostics

Since RAM is one of the most used components of any server, many system failures often are due to memory-related matters. While typical memory issues involve applications using too much resources, or a process running out of control, failing hardware also can be responsible for system crashes. Fortunately you can easily diagnose potential memory trouble spots by using the Windows Memory Diagnostics tool built into most modern Windows Server editions.

  • To use this tool, simply open the command prompt and use the following command C:\mdsched
  • From there, you should follow the prompts to complete the diagnostic process.

The Importance of Backups

Even with the best tools and skills, occasionally a professional will encounter an issue which is beyond repair. When situations such as these occur, having a solid backup system always pays off. Although there are many backup tools on the market, Windows has a backup tool built in which can be used to protect yourself from even the worst disasters.
For details on implementing Windows Backup in windows Server 2008 and Windows Server 2012, Microsoft has documentation here.

Clustering as a way to Mitigate Risks

Server clustering is the process of having a group of independent servers working together as a single system to deliver more reliability to your users. Such arrangements are designed to protect against the following types of failure:

  • Applications and service failures which can affect software and essential services
  • System and hardware failures which can affect low level server components
  • Site failures in multisite organizations  which can be caused by natural disasters and other significant situations
  • By using clustering, you can ensure that when disaster strikes, your systems can continue operating instantly by shifting responsibility from the failed systems to the functional ones.

Conclusion

Although there are many variables which impact the reliability of your Windows servers, by focusing on proactive actions to mitigate damage you can avoid the difficulties of troubleshooting server issues. Even with the best precautions however, failures still occur, which is why you should familiarize yourself in advance with the technologies Windows provides to make the debugging process much easier.

Best Windows Server 2012 Hosting Recommendation

ASPHostPortal.com

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server 2012. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows server 2012 Hosting, we should be your best choice.



Windows 2012 Hosting with ASPHostPortal.com :: The Best New Feature in Windows 10

clock April 9, 2015 06:30 by author Mark

Microsoft just announced that the upcoming Windows 9 will, in fact, be called Windows 10. Crazy days. So let's pull out 10 of the very best new features from Microsoft's upcoming Windows XP killer.
The Windows 10 release date is slated for "mid-next year" as we previously estimated. There was no confirmation as per the rumour that Windows 9 might end up being free. Starting tomorrow Microsoft will start distributing a Technical Preview build of Windows 10 via the Windows Insider Program.

1. There's loads of stuff for touch, but it's different stuff


Designed to appeal to experts and novices alike, Microsoft was at pains to point out that it hasn't given up on touch with the new OS. The Charms bar remains for the moment although we expect it to die if you're using a non-touch PC (check out Continum below).
Microsoft says it wants to support touch users who have persevered with Windows 8 and "evolve" the touch UI. (Translated: That means it is changing quite a bit.) The task switching will no longer work on the left. So with that and the charms going, that's goodbye to most of the problem with Windows 8 - and a whole philosophy down the pan.
Microsoft's Joe Belfiore said at the preview: "We want Windows 7 users to feel like they upgraded from a [Toyota] Prius to a Tesla, but they don't need to learn a new way to drive."

2. It's BACK!


As we've covered before, the e most noticeable change is the new Start Menu, which looks somewhat like the old start menu. It brings some features from the Windows 8.1 Start Screen, such as live tiles, and can be resized.

3. Another new Task View (the Windows 8 flippy thing has gone)


 

Now the Windows 8 task switcher has been unceremoniously dumped, there's a new Task View in Windows 10, too, so users can switch between virtual desktops.That'sause Microsoft now recognises that novice users use the taskbar rather than switching in other, cleverer, ways such as Alt+Tab (which also now switches between desktops).

4. Snap Assist helps you snap windows


A new Snap Assist feature also helps users work out which way is best to snap apps to. You can snap windows into new screens and tile Windows - just as you've been able to since Windows 2.0 or maybe 3.0.

5. The Command Prompt enters the 21st Century

Another quite amazing feature for those of us that use it is that the command prompt is now getting keyboard shortcuts! So you will be able to paste in your commands! Hardly groundbreaking, but actually pretty exciting.

6. Improvements to Windows Explorer

A new Home location is the new default view in Windows Explorer. There's also a Share button on the Windows Explorer taskbar (we really hope this is in the context menu, too).

7. Continum - the special one


This is the best new thing we found out today. Continum is an on-the-fly mode for 2 in 1 devices that can automatically change mode if it detects there is suddenly no keyboard attached. So, for example, a back button appears to help you navigate the Desktop with touch if the keyboard is removed. We reckon the charms will also disappear in non-touch mode, although we've heard conflicting news about that.

8. Windows 10 Universal apps


Windows 10 will also usher in a new app model - Universal Windows apps. Windows Universal apps are the new name for Metro apps/Modern apps/Windows Store apps. Take your pick on those old monikers, they've got a new one! Presumably (although we don't know as yet) that these will also work on Windows Phone. Windows 10 will be able to run on all devices from phones to servers and there will be a single app store across the lot.
All older Windows Store apps will work with Windows 10.

9. Modern (sorry, Universal) apps now float on the Desktop

The new Universal apps also work on the desktop and 'float' in their own Windows. Microsoft wants to banish the separation between the Modern UI and the Desktop.
These 'modern' apps on the desktop have a icon for more options - replacing the commands that used to be in the charms on the right-hand side of the screen.

10. Windows 10 has lots for Business and Enterprise

Microsoft says it hopes to appeal to business with this release of Windows, more so than Windows 8 ever did. Belfiore said they were "starting the dialogue with businesses today".
To be honest, it needs to - Windows XP is now ancient and unsupported, while precious other businesses want to make the leap away from Windows 7. But part of the reason why Microsoft is demonstrating the OS without all the consumer features is to show enterprises what the OS is capable of at this stage.
This version of Windows will have plenty of other features for enterprise, including a customised store and protection for corporate data. Mobile Device Management will be able to be used for all devices.

Best Windows Server 2012 Hosting Recommendation

ASPHostPortal.com

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server 2012. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows server 2012 Hosting, we should be your best choice.



Windows 2012 Hosting with ASPHostPortal.com :: How to Upgrade From Windows 8.1 to Windows 10

clock March 25, 2015 05:36 by author Mark

Hi guys today I will explains How to Upgrade From Windows 8.1 to Windows 10. Ok let me to show you.

As we all know, Microsoft has introduced Windows 10 for its user, but it is still not finished and does not yet contain all the features that will make it more reliable and powerful for users. There is a question in everyone's mind of whether Windows 10 will be a free upgrade for Microsoft users or will there be a charge. So people are waiting for it eagerly. We know that Windows 10 will run on a set of devices, these devices have various sizes of screens. I think the Windows 10 update might be free because Microsoft wants to make it the best platform or operating system for its users.

Upgrade Windows 8.1 into Windows 10

Step 1

The first step is to download Windows 10 from the link Windows 10 Setup. When you click on this link you will see a window with an option to get started as in the following picture.

Step 2

Here you will see a button Get started. When you click on this button then another window is opened just as in the picture. In this window you have a button named join now.

Step 3

When you have clicked on this join now button a window is opened. Here this window prompts for your Microsoft account to download Windows 10. So please enter your valid email id and the password to proceed further.

Step 4

After a successful login you will connect to a download link for Windows 10. Now download Windows 10 from here. When your download is complete save this setup in to the your DVD or in the USB. Insert your USB or DVD in which your setup is available in your system, when you insert it you will see a window like this. Now click on the run to do the setup.

When you have clicked on the run setup, your setup will be run and you will see a window like this.

Step 5

This preparing window will run from one percent to a hundred percent, it requires some time to complete. You will have a new window, that window is the license window, here are some rules and regulations related to Windows 10, simply click on the I accept the license term and click on the accept button, the window look like this:

When you click on the accept button you will see a new window. The name of this window is Getting Updates, it is just like the given picture. Getting Updates checks all the updates that are available in Windows 10 then proceeds further.

Step 6

When all the updates have been found successfully using Getting Updates, you will get a window with a message saying we're getting a few things ready. The window requires some time but then you will get a new window with a new message saying Making sure you're ready to install. This window will look like this.

When this window completes it will automatically show a new window. Here you will see a message saying What needs you attention. In that window you have an OK button. Just click on this button to proceed to the next steps.

Step 7

After performing the preceding procedure we will get a new window, in this window we will see a message ready to install, here click on the install button. This window is like the picture given below.

When you click on the install button your window will install and you will get a message as in following picture. This will also run from zero percent to a hundred percent so it also might require some time. One thing to note there is that during this process your PC will we restarted many times.

Step 8

When this installation goes to one hundred percent, your system will be restarted and then you will have new window with a Windows icon that will sho the setting up percentage, this percentage is also running from one to one hundred percent, this new window looks as in the following picture.

After completing the setting up process, a new process will start. Here the process name is Getting devices ready, the getting devices ready step checks the availability of hardware. This window looks as in the following picture. This process is also going on from one to one hundred percent.

After completing the getting device ready process a new window appears, Apply PC setting. This is also a percentage based process going from one percent to one hundred percent. After completing set up a few more things also must be done.

Step 9

Now your system will be restarted and then you will see a window. That window shows the settings. Here there are two options, one is to use express settings and another is to customize settings, here I use the express settings, the window looks like this.

After clicking on the use express settings we will see a window that asks for a Microsoft account. Here you have the option to use an old account or you can create a new account. Here I go with my old Microsoft account.

When we click on the create a new Microsoft account we will see this window.

Step 10

Now nearly everyting is done. After this your system will be restarted and you will see a window like this automatically. This window changes its color many times during the process. Here some apps are installed automatically that are available with Windows 10.

After completing this you will see the message Let's Start.

Step 11

This is the final step. Here you will see your Windows 10 Desktop. As you know, one new thing in Windows 10 is that now must use the Start menu in Windows 10. So here the Start menu is available for you. So finally you get the window like this.

Now you can enjoy Windows 10 on your PC.

Summary

As we know, Microsoft users have been waiting for Windows 10 very eagerly. In this article I provided the procedure for upgrading your Windows 8 to Windows 10. I hope this procedure will be helpful for those that want to upgrade their Windows 8 to Windows 10. One thing to note is that the upgrade is available only for one year after the final release of the the Windows 10. I hope this article will be useful for the readers.

Best Windows Server 2012 Hosting Recommendation

ASPHostPortal.com

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server 2012. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows server 2012 Hosting, we should be your best choice.



SQL SERVER 2014 Hosting - ASPHostPortal :: Automatic SQL Server Backup Utility using Sqlserver Agent

clock March 23, 2015 08:22 by author Mark

Automatic SQL Server Backup Utility using Sqlserver Agent

It is a sample C# (Visual Studio) application for Automatic Sql server Backup Utility using sqlserveragent. I have used SQL-DMO dll. This article will show you how to create a automatic backup in Sql server.
This code should work on any PC use VB.NET and installed SQL Server(any edition or Client Components for SQL Server.
SQLDMO (Which installed always bt MS SQL Serveror MS SQL Server Client Tools
To do:

  • First enter your SQL Server username and password on corresponding Text Box.
  • Set backup Start date & Backup Time
  • After Finishing this then please check manually it will working or not
  • Manual working procedure:
    • Run Sql sever enterprise Manager
    • Select management Option
    • Open Sql server agent
    • Open Jobs window
    • Check whether job item exist or not
    • Right click on newly created job item then, we will get one
    • Popup menu, then select start job
    • After finish the job then check folder "D:\backup" bkp file created or not

Add reference to SQL-DMO dll

You can do this by right clicking the project in Solution Explorer, then selecting 'Add Reference', COM components and the latest version of "Microsoft SQLDMO Object Library".

Available Server

public void dIsplayServerList(ComboBox cboListName)
{
    try
    {
        SQLDMO.Application oSQLServerDMOApp = new SQLDMO.Application();
        Info.informationLayer info = new Info.informationLayer();           
        SQLDMO.NameList oNameList;
        oNameList = oSQLServerDMOApp.ListAvailableSQLServers();
        for (int intIndex = 0; intIndex <= oNameList.Count - 1; intIndex++)
        {
            if (oNameList.Item(intIndex as object) != null)
            {
                cboListName.Items.Add(oNameList.Item(intIndex).ToString());
            }
        }
        if (cboListName.Items.Count > 0) cboListName.SelectedIndex = 0;
        else cboListName.Text = "(Local)";
        }
    catch
    {
}
}

Available databases

public void dIsplayDatabases(ComboBox cboDatabase,Info.informationLayer info)
{
    try
    {
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
        cboDatabase.Items.Clear();
        SQLServer.Connect(info.strServerName,info.strLoginName,info.strPwd);
        foreach (SQLDMO.Database db in SQLServer.Databases)
        {
            if (db.Name != null)
                cboDatabase.Items.Add(db.Name);
        }
        cboDatabase.Sorted = true;
        if (cboDatabase.Items.Count == 0)cboDatabase.Text = "<No databases found>";
    }
    catch (Exception err)
    {
       info.ErrorMessageDataLayer = err.Message;
    }
}
Create Job on Server Agent:
public void CreateJob_Sql(Info.informationLayer info)
{
    try
    {
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
        SQLDMO.Job SQLJob = new SQLDMO.Job();
        SQLDMO.JobSchedule SQLSchedule = new SQLDMO.JobSchedule();
        SQLServer.Connect(info.strServerName, info.strLoginName, info.strPwd);
        switch (SQLServer.JobServer.Status)
        {
            case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped:
            SQLServer.JobServer.Start();
            SQLServer.JobServer.AutoStart = true;
            break;
        }
        SQLJob.Name = info.strDatabaseName;
        SQLJob.Description = "Check and Backup" + info.strDatabaseName;
        SQLServer.JobServer.Jobs.Add(SQLJob);
        SQLJob.Category = "Database Maintenance";
        SQLDMO.JobStep aJobStep = new SQLDMO.JobStep();
        aJobStep.Name = "Step 2: Backup the Database";
        aJobStep.StepID = 1;
        aJobStep.DatabaseName = info.strDatabaseName;
        aJobStep.SubSystem = "TSQL";
        //------>>> If BackUp Folder is Not Found then create BackUp Folder                
        string   DirectoryName = "D:\\BackUp";
        if (Directory.Exists(DirectoryName)==false)
        {
            System.IO.Directory.CreateDirectory(DirectoryName);
        }
        //------>>>
        string sExt;
        sExt="EXEC master.dbo.xp_sqlmaint '-S " + info.strServerName + " -U " + info.strLoginName + " -P " + info.strPwd + "  -D " + info.strDatabaseName + " -CkDB -CkAl -CkCat -BkUpMedia DISK -BkUpDB D:\\Backup  -BkExt BAK -DelBkUps 2weeks -BkUpOnlyIfClean -Rpt D:\\Backup\\BackDB_Checks.txt'";
        aJobStep.Command = sExt;
        aJobStep.OnSuccessAction = SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithSuccess;
        aJobStep.OnFailAction = SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithFailure;
        SQLJob.JobSteps.Add(aJobStep);
        SQLJob.ApplyToTargetServer(info.strServerName);
        aJobStep.DoAlter();
        SQLJob.Refresh();
        aJobStep.Refresh();
    }
    catch (Exception Err)
    {
        info.ErrorMessageDataLayer = Err.Message;
    }
}

Create Job shedule on  server Agent:

public void CreateShedule_Sql(Info.informationLayer info)
{
    try
    {
        //it will take bkp every week 2 day
        SQLDMO.Job SQLJob = new SQLDMO.Job();
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
        SQLDMO.JobSchedule SQLSchedule = new SQLDMO.JobSchedule();
        SQLServer.Connect(info.strServerName, info.strLoginName, info.strPwd);
        SQLJob = SQLServer.JobServer.Jobs.Item(info.strDatabaseName);
        // create a new JobSchedule object
        SQLSchedule.Name = "Weekly Backup";
        SQLSchedule.Schedule.FrequencyType = SQLDMO.SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_Weekly;
        SQLSchedule.Schedule.FrequencyInterval = 2;
        SQLSchedule.Schedule.FrequencyRecurrenceFactor = 2;
        // // start on Mar22, 2015 - at 12.55
        SQLSchedule.Schedule.ActiveStartDate = info.intStartDate;
        SQLSchedule.Schedule.ActiveStartTimeOfDay = info.intStartTime;
        ////  this schedule has no end time or end date
        SQLSchedule.Schedule.ActiveEndDate = 99991231;
        SQLSchedule.Schedule.ActiveEndTimeOfDay = 235959;
        ////  add the schedule to the Job
        SQLJob.BeginAlter();
        SQLJob.JobSchedules.Add(SQLSchedule);
        SQLJob.DoAlter();
        //SQLJob.JobSchedules.Refresh();
        info.ErrorMessageDataLayer = "New Sql Job [Databasename= " + info.strDatabaseName + " ]Sucessfully Created.  ";
    }
    catch (Exception err)
    {
        info.ErrorMessageDataLayer = err.Message;
    }
}

Syntax (SQL Server)

xp_sqlmaint 'switch_string'
[
    [-S server_name[\instance_name]]
    [-U login_ID [-P password]]
    {
        [ -D database_name | -PlanName name | -PlanID guid ]
        [-Rpt text_file]
        [-To operator_name]
        [-HtmlRpt html_file [-DelHtmlRpt <time_period>] ]
        [-RmUnusedSpace threshold_percent free_percent]
        [-CkDB | -CkDBNoIdx]
        [-CkAl | -CkAlNoIdx]
        [-CkCat]
        [-UpdOptiStats sample_percent]
        [-RebldIdx free_space]
        [-WriteHistory]
        [
            {-BkUpDB [backup_path] | -BkUpLog [backup_path] }
            {-BkUpMedia
                {DISK [    [-DelBkUps <time_period>]
                            [-CrBkSubDir ] [ -UseDefDir ]
                         ]
                | TAPE
                }
            }
            [-BkUpOnlyIfClean]
            [-VrfyBackup]
        ]
    }
]
time_period
number[minutes | hours | days | weeks | months]

Syntax (SQL Server)

sqlmaint
[-?] |
[
    [-S server]
    [-U login_ID [-P password]]
    {
        [ -D database_name | -PlanName name | -PlanID guid ]
        [-Rpt text_file [-DelTxtRpt <time_period>] ]
        [-To operator_name]
        [-HtmlRpt html_file [-DelHtmlRpt <time_period>] ]
        [-RmUnusedSpace threshold_percent free_percent]
        [-CkDB | -CkDBNoIdx]
        [-CkAl | -CkAlNoIdx]
        [-CkTxtAl]
        [-CkCat]
        [-UpdSts]
        [-UpdOptiStats sample_percent]
        [-RebldIdx free_space]
        [-WriteHistory]
        [
            {-BkUpDB [backup_path] | -BkUpLog [backup_path] }
            {-BkUpMedia
                {DISK [    [-DelBkUps <time_period>]
                            [-CrBkSubDir ] [ -UseDefDir ]
                         ]
                | TAPE
                }
            }
            [-BkUpOnlyIfClean]
            [-VrfyBackup]
        ]
    }
]

Best SQL 2014 Hosting Recommendation

ASPHostPortal.com

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server. We offers SQL 2014 hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL 2014 Hosting, we should be your best choice.



SQL 2014 Hosting - ASPHostPortal.com :: Recovering SQL Server Database from Suspect Mode

clock November 13, 2014 06:49 by author Mark

How Recovering SQL Server Database from Suspect Mode ??

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server

Reason for database to go into suspect mode:

  • Data files or log files are corrupt.
  • Database server was shut down improperly
  • Lack of Disk Space
  • SQL cannot complete a rollback or roll forward operation

How to recover database from suspect mode:

  • Change the status of your database. Suppose database name is “BluechipDB”?

EXEC sp_resetstatus '';
Example:
EXEC sp_resetstatus 'BlueChipDB'

  • Set the database in “Emergency” mode

ALTER DATABASE  SET EMERGENCY;
Example:
ALTER DATABASE BlueChipDB SET EMERGENCY

  • Check the database for any inconsistency

DBCC CHECKDB('');
Example:
DBCC checkdb('BlueChipDB')

If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Example:
ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  • For safety, take the backup of the database.
  • Run the following query as next step.Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone.
  • There is no way to go back to the previous state of the database.
  • So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);
Example:
DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

  • Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER

  •  Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

 



SQL 2014 Hosting Tutorial - ASPHostPortal.com :: SQL Server 2014 Analysis, Migrate, and Report Tool

clock November 10, 2014 11:28 by author Mark

Determine which tables and stored procedures would benefit from In-Memory OLTP

With SQL Server 2014 new In-Memory OLTP engine, you can load tables and stored procedures in memory, which provides very fast response times. The goal isn't to load all the database tables and stored procedures in memory, but rather just those tables that are crucial to performance and those stored procedures that have complex logical calculations.

To help you identify which tables and stored procedures will give you the best performance gain after being migrated to In-Memory OLTP, SQL Server 2014 provides the new Analysis, Migrate, and Report (AMR) tool. Built into SQL Server Management Studio (SSMS), the AMR tool consists of the:

  • Transaction performance collector (which collects data about existing tables and stored procedures in order to analyze workloads) and transaction performance analysis reports (which gives recommendations about the tables and stored procedures to migrate to In-Memory OLTP based on the collected data)
  • Memory Optimization Advisor (which guides you through the process of migrating a table to a memory-optimized table)
  • Native Compilation Advisor (which helps you identify T-SQL elements that need to be changed before migrating a stored procedure to a natively compiled stored procedure)

The AMR tool leverages the new Transaction Performance Collection Sets for gathering information about workloads and the Management Data Warehouse (a relational database) to store the collected data. The Transaction Performance Collection Sets includes the:

  • Stored Procedure Usage Analysis collection set (which captures information about stored procedures for a future migration to natively compiled stored procedures)
  • Table Usage Analysis collection set (which captures information about disk-based tables for a future migration to memory optimized tables)

Before you can use the AMR tool, you need to configure the Management Data Warehouse and the data collection process. After showing you how to do so, I'll demonstrate how to run the transaction performance analysis reports and how to use the two advisors.

Configuring the Management Data Warehouse

To configure the Management Data Warehouse, go to Object Explorer in SSMS. Expand the Management folder, right-click Data Collection, select Tasks, and click Configure Management Data Warehouse. This will launch the Configure Management Data Warehouse Wizard.

After the Welcome page, you'll find yourself on the Select Configuration Task page. On this page, select the option to configure a Management Data Warehouse.

  • On the Configure Management Data Warehouse Storage page, you need to specify the name of database that will host the Management Data Warehouse and the name of the server on which that database resides. If you need to create the database, click the New button to create one.
  • On the Map Logins and Users page, you'll find the existing logins allowed for the server that will host the Management Data Warehouse. If needed, you can edit the logins or map users to the administrator, reader, and writer roles for the Management Data Warehouse.
  • On the Complete the Wizard page, you need to verify the Management Data Warehouse configuration. If it's OK, click Finish. When the configuration of the Management Data Warehouse has successfully completed, you should see a page like that in Figure 1.

Figure 1: Verifying Configuration of the Management Data Warehouse

The Management Data Warehouse setup is now finished.

Configuring the Data Collection Process

To configure the data collection process, go to Object Explorer in SSMS. Expand the Management folder, right-click Data Collection, select Tasks, and click Configure Data Collection. This will launch the Configure Data Collection Wizard.

After the Welcome page, you'll find the Setup Data Collection Sets page shown in Figure 2. Besides needing to specify the server and database that will host the Management Data Warehouse, you need to specify the data collector sets. In the list of collection sets, select the Transaction Performance Collection Sets check box so that the data collector will collect statistics for transaction performance issues.

Figure 2: Specifying the Data Collector Sets

If the Management Data Warehouse is located on a different SQL Server instance from the data collector and if SQL Server Agent isn't running under a domain account that has dc_admin permissions on the remote instance, you have to use a SQL Server Agent proxy. If that's the case, be sure to select the Use a SQL Server Agent proxy for remote uploads check box.

Once you're done configuring the Setup Data Collection Sets page, click Finish. When the wizard completes the configuration, you'll have an enabled data collection process that will collect information about all user databases. Note that SQL Server Agent must be started on the instance that will collect the data.

In the SQL Server Agent's Jobs folder, you'll see the jobs used to collect data from your workloads and the jobs used to upload the collected data into the Management Data Warehouse. The data collection jobs use the naming convention collection_set_N_collection, where N is a number. The upload jobs use the naming convention collection_set_N_upload, where N is a number.

By default, the AMR tool collects data from three dynamic management views every 15 minutes for both the Stored Procedure Usage Analysis and Table Usage Analysis collection sets. The upload job runs every 30 minutes for the Stored Procedure Usage Analysis collection set and every 15 minutes for the Table Usage Analysis collection set. If you want to speed your upload, you can execute these jobs manually. Uploading the data has a minimal impact on performance.

Running the Transaction Performance Analysis Reports

To access the recommendations based on the information collected about all your user databases on the workload server, you need to run the transaction performance analysis reports. To access them, right-click your Management Data Warehouse database, select Reports, choose Management Data Warehouse, and click Transaction Performance Analysis. From the Transaction Performance Analysis Overview page, you can choose to run three reports, depending on what type of information you need:

  • Recommended Tables Based on Usage
  • Recommended Tables Based on Contention
  • Recommended Stored Procedures Based on Usage

Recommended Tables Based on Usage. This report tells you which tables are the best candidates for migration to In-Memory OLTP based on their usage. Figure 3 shows a sample report. On the left side, you can select the database and how many tables you'd like to see from that database. The chart will then show the selected tables. The horizontal axis represents the amount of work needed to migrate a table to In-Memory OLTP. The vertical axis represents the gains you'll achieve after migrating the table. The best candidates for In-Memory OLTP are located in the top right corner. As you can see, they can be easily migrated and will give you the best performance gain.

Figure 3: Determining Which Tables Are the Best Candidates for Migration Based on Usage

You can access a detailed report for a table by clicking its name in the chart. As Figure 4 shows, this report provides the table's access statistics (e.g., lookups, range scan) and contention statistics (e.g., latches, locks), as well as when this information was captured.

Figure 4: Reviewing the Detailed Performance Statistics for a Table

Recommended Tables Based on Contention. This report tells you which tables are the best candidates for migration to In-Memory OLTP based on their contention. If you compare the contention analysis report in Figure 5 with the usage analysis report in Figure 3, you'll see that they're very similar.

Figure 5: Determining Which Tables Are the Best Candidates for Migration Based on Contention

You can select the database and how many tables you'd like to see from that database. The resulting chart shows the amount of work needed to migrate the tables (horizontal axis) and the gains you'll achieve after migrating them (vertical axis). In the top right corner, you'll find the best candidates for migration based on contention. You can click a table name in the chart to access a detailed report showing the table's statistics. This report provides the table's access and contention statistics.

Recommended Stored Procedures Based on Usage. This report shows you which stored procedures are the top candidates for an In-Memory OLTP migration based on their usage (i.e., total CPU time). After selecting the database and how many stored procedures you'd like to see from that database, the resulting chart shows the top candidates for migration, as Figure 6 shows.

Figure 6: Seeing Which Stored Procedures Are the Top Candidates for Migration Based on Usage

If you want to see the detailed usage statistics for a specific stored procedure, you can click its blue bar. Figure 7 shows an example of the report you'll receive.

Figure 7: Reviewing the Detailed Usage Statistics for a Stored Procedure

Using the Memory Optimization Advisor

  • After you know which tables you want to migrate to In-Memory OLTP, you can use the AMR tool's Memory Optimization Advisor to help you with the migration process. To access this advisor, open Object Explorer in SSMS and navigate to the table you want to migrate. Right-click the table and choose Memory Optimization Advisor.
  • The advisor will launch with the Introduction page, which you can read or skip. Clicking Next brings you to the Migration Optimization Checklist page, where the advisor will check to see if your table can be migrated. If one or more validation items fail, the migration process will stop. If needed, you can generate a report for this analysis. If all you see are green checkmarks, your table doesn't have any features that could prevent the migration process, in which case you can proceed to the next page.
  • On the Migration Optimization Warnings page, you'll find important information about what isn't supported in memory-optimized tables and other types of issues. The issues listed won't prevent the table from being migrated, but they might cause other objects to fail or behave in an unexpected manner.

If a warning applies to the table you selected for migration, an exclamation point in a yellow triangle will appear next to the warning, as shown in Figure 8.

Figure 8: Reviewing the Migration Optimization Warnings

In this case, the selected table has an unsupported French_CI_AS collation on the indexed column named Person_OnDisk_Name. (Only BIN2 collations are supported for indexes in memory-optimized tables.) Thus, the index collation will need to be changed later in the migration process.

Figure 9: Reviewing the Optimization Options

On the Review Optimization Options page, which Figure 9 shows, you have the option to change the defaults listed for the:

  • Name of memory-optimized file group (only one memory-optimized file group is allowed per instance)
  • Logical filename
  • Path where the logical file will be saved
  • New name given to the original table (the original table is renamed to prevent naming conflicts)

You can also choose to copy data from the original table to the new memory-optimized table during the migration process, and you can change the durability of the memory-optimized table. By default, its DURABILITY option will be set to schema_and_data, but you can change it to schema_only by selecting the Check this box to migrate this table to a memory-optimized table with no data durability option. If you do so, the data will be lost after the SQL Server service is restarted. In other words, just the table's schema is persistent. Finally, the Review Optimization Options page shows the estimated current memory cost for the memory-optimized table. If there isn't sufficient memory, the migration process might fail.

Once you're done with the Review Optimization Options page, you can click Next to go to the Review Primary Key Conversion page. When the migration process begins, it will start by converting the primary key. You can convert it to:

  • A nonclustered hash index, which gives the best performance for point lookups. If you select this option, you also need to specify the bucket count, which should be twice the expected number of rows.
  • A nonclustered index, which gives the best performance for range predicates.

For each index you have in the table being migrated, you'll be presented with a Review Index Conversion page that has been populated with the columns and data types for that index. The options you can configure in the Review Index Conversion page are similar to those in the Review Primary Key Conversion page. In this case, for the indexed column Person_OnDisk_Name with the unsupported French_CI_AS collation, you'd have to select BIN2 collation as the Char data type.

On the Verify Migration Actions page, you'll see all operations that will be performed to migrate your table to In-Memory OLTP. You have the option to script those operations by clicking the Script button. After verifying all the options, you can click the Migrate button to start the migration process.

Figure 10 shows how the new memory-optimized table appears in SSMS. If you view its properties, you'll see that the Memory optimized property is set to True and that the schema and data are durable for this table.

Figure 10: New Memory-Optimized Table in SSMS

In Figure 10, you can also see how the original table has been renamed.

Using the Native Compilation Advisor

After you know which stored procedures you want to migrate to In-Memory OLTP, you can use the AMR tool's Native Compilation Advisor to help you with their migration. To access this advisor, open Object Explorer in SSMS and navigate to the stored procedure you want to migrate. Right-click the stored procedure and choose Native Compilation Advisor.

After clicking through the Welcome page, you'll be presented with the Stored Procedure Validation page, which will give you warnings if your stored procedure contains some T-SQL elements that aren't supported by native compilation. If the stored procedure is valid, it can become a natively compiled stored procedure without modification. However, the Native Compilation Advisor doesn't migrate stored procedures like the Memory Optimization Advisor migrates tables. You have to do the migration on your own.

If the stored procedure has unsupported T-SQL elements, the validation will fail. To see the details about the unsupported elements, you need to click Next to go to the Stored Procedure Validation Result page, which Figure 11 shows.

Figure 11: Reviewing the Unsupported T-SQL Elements

You have to modify the unsupported elements before you can migrate your stored procedure to a natively compiled stored procedure.

Eliminate the Guesswork

The AMR tool is useful because it eliminates the guesswork in determining which tables and stored procedures would benefit from In-Memory OLTP. After identifying which tables to migrate, you can use the Memory Optimization Advisor to quickly migrate them. Although the Native Compilation Advisor can help you identify the T-SQL elements you need to change before migrating your stored procedure to a natively compiled one, it unfortunately doesn't guide you through the migration process.

 



About ASPHostPortal.com

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Sign in