Windows 2012 Hosting - MVC 4 and SQL 2012 BLOG

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

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 :: Handling Divide BY Zero Exception

clock March 16, 2015 07:24 by author Ben

Some instances whilst undertaking Calculation within your query you got an error or exception "Divide by Zero" and some time you will have Output value Like NULL So how you can manage these Problem and Exception.

Use NULLIF -To Manage Divide by zero Exception
Use ISNULL -To Show some worth rather of NULL in your output

Below will be the total explanation
select 10/0

in case you run the above query it is going to throw an error

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Now to be able to Resolve this we'll use Nullif function

 select nullif (10/ nullif (0,0),0)

Output NULL

Now it's going to not throw an error and your output will likely be null. Explanation: NULLIF function takes two arguments verify wheather they're equal or not if they are equal it'll return NULL as output
IF both expressions are not equal it is going to return the very first expression with identical data kind. Now inside the denominator it checked wheather 0 is equal to 0 The conditon was true it returned Null in denominator. now the expression was like

select Nullif(10/null,0)

now again it will check wheather 10/Null is equal to zero condition fails and you will get.

Output  NULL

Note : AnyNumber divide multiplied,added subtracted with NULL will Resultant to NULL only. Now how to use NULLIF and ISNULL in your Code or in TSQL to avoid exception and NULL Values. In the below code i have used nullif with ISNULL function. For this we will  Create a table

CREATE TABLE Test_NULLIF1
(
   col1            int   IDENTITY,
   col2      int   NULL,
   col3   int   NULL
);
INSERT Test_NULLIF1  VALUES(10, 20);
INSERT Test_NULLIF1 VALUES(NULL, 23);
INSERT Test_NULLIF1 VALUES(0, 18);
INSERT Test_NULLIF1 VALUES(NULL,75);
INSERT Test_NULLIF1 VALUES(300000, 21);
Go

If you will divide column 3 by column 2 for Row 3 it will throw Divide By zero Exception Run this query to generate error.. select col3/col2  from Test_NULLIF1  where col1 =3

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

In order to handle this Use nullif function as explained in above example

select nullif(col3/nullif(col2,0),0)  from Test_NULLIF1  where col1 =3

Output NULL

Now You can Use ISNULL function after handling divide by zero exception to give a value when you have output like NULL
for eg like instead of NULL you want to show value like 0 or 1 or anything

select ISNULL(col3/nullif(col2,0),1)  from Test_NULLIF1  where col1 =3

Output 1

Explanation: ISNULL Function takes two argument check wheather first expression is NULL if null provide a replacement for that NULL in second expression it can be any value.
IN query ISNULL checked the first expression it was NULL so it replaced the NULL Value with 1,
and return output  as 1.

IN case if first expression is not null it willl return the first expression value only. .

Test IsNULL function with below queries

eg
Select ISNULL(null,2)

output 2

select isnull(3,1)--as first expression is not null so it returns first value i.e. 3

output 3

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.

 



SQL 2014 Hosting Tutorial - ASPHostPortal.com :: How to Add Data in SQL Server 2014

clock October 31, 2014 06:03 by author Ben

In the earlier publish, we all know about The Best Way to Create a Table in SQL Server 2014. We now possess a databases, and table, but no data.

There are lots of techniques of obtaining data into your database in SQL Server 2014. Listed here are the primary ones that come to mind:

  • Manually: Type data directly into your table rows.
  • Copy/Paste: Similar to the earlier alternative, but this 1 is in which you duplicate data from an additional source, then paste it into a table within your databases.
  • Import: You are able to utilize the Import and Export Wizard to import info from yet another source.
  • SQL Scripts: You can operate a SQL script which contains all info to insert.
  • Application/Website: Consumers update the database via an application or site.

Here's much more element on each of these methods.

Manually

We will make use of the Edit Top 200 Rows option to manually kind info directly in to the table rows.

Manually getting into data is Okay should you have only just a little little bit of data to enter. But it's a little bit clunky and might impractical if you have lots of information. Furthermore it doesn't actually match most company requirements, in which non-technical customers need to be capable of update the database.

In any case, here's how to manually enter data directly into the table :

  1. In the Object Explorer, right click on the table you wish to open, and select Edit Top 200 Rows:

  2. You can now start entering the data directly into your table.

Copy/Paste

You could use a similar method towards the above by copying from an additional datasource and pasting into your databases table. Of course, this may require which the resource table has the same columns since the destination table. Comparable towards the manual method above, this really is Okay to get a small quantity of information but not for any good deal of data.

Here's how to copy/paste into your table:

  1. Select all required records from the datasource
  2. In the destination database (i.e. the one you want to populate with data), right-click on the destination table and select Edit Top 200 Rows
  3. Select an empty row by right-clicking in the left-most column (it's more of a button to the left of your left-most column that allows you to select the whole row) and select Paste from the contextual menu:


    If you need to paste more than 200 rows, click the Show SQL Pane icon from the toolbar to display the SQL statement behind the 200 rows being displayed. Simply change the 200 to a larger number of rows as required.



    Note that this will work up to a certain extent, but you may encounter times where have so much data to transfer that you need to use another method.

Import

You are able to import data from yet another datasource. The top end result is comparable towards the copy/paste method (i.e. information is copied across to the location databases), but importing the data is a lot more flexible and could be more appropriate on several occasions. For instance, you can choose info from several views and tables, and you can compose a question on the fly to import only the data you'll need.

To import data, right-click on the database and select Tasks > Import Data... and follow the Wizard from there.

The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. These include:

  • SQL Server
  • Flat files
  • Microsoft Office Access
  • Microsoft Office Excel

You may also commence the SQL Server Import and Export Wizard in the Windows Commence menu, from inside of SQL Server Data Tools (SSDT), and through the command prompt (by running DTSWizard.exe which you will locate in both C:\Program Files\Microsoft SQL Server\100\DTS\Binn or in C:\Program Files\Microsoft SQL Server\120\DTS\Binn or other location depending on your configuration and push letter).

SQL Scripts

In many circumstances, you'll find it much more efficient to run a SQL script which contains the data you need to insert. You'll be able to make use of the SQL INSERT assertion to insert just the info you specify inside the statement.

SQL scripts are excellent for inserting static/reference data (like say, countries/regions). They are able to be saved and run once more any time it really is required (for example on another database). Scripts aren't generally so excellent for data that continually changes (like customer particulars). You almost certainly would not be maintaining a duplicate of outdated data within a SQL script. But you'll find often exceptions. For example, you may use this kind of a script to populate a client table inside your testing/development atmosphere.

More about SQL scripts arising.

Application/Website

Most SQL Server databases are the backend data storage for any front-end application. Customers from the application are responsible for including data towards the databases (as well as editing it). As a result, many of the tables in your database will likely be up-to-date via the application. Within this situation, the applying is updating the database utilizing SQL scripts.

The main difference between these scripts and the kinds we mentioned over is the fact that, the scripts being used within the software will probably be dynamic. They'll accept data as a parameter that is handed to the script. Hence the user can enter say, an e-mail tackle in to the application, and unbeknownst to him, the application runs a SQL script that requires his e-mail tackle, validates it, provides it to the script, and when it passes each of the business/validation principles, inserts it in to the database.

These SQL scripts may be placed directly into your site code (PHP, ColdFusion and so forth), or they're able to be stored within the databases as Saved Processes or Views and operate only when the application says so.




SQL 2014 Hosting Tutorial - ASPHostPortal.com :: The Best Way to Create a Table in SQL Server 2014

clock October 24, 2014 07:04 by author Ben

Seeing as our database is actually a task-tracker database, let us get in touch with our initial table "Tasks". This desk will hold all duties - regardless of their status (eg. done, to do, in progress, and so on). Then we are able to develop yet another table known as "Status". Then when our tables contain information, we'll be capable of operate queries towards these tables to discover what jobs have to be carried out and which of them are in a offered position, and so on.

But let's not get forward of ourselves. Let's develop our first table.

They are the measures to make a table in a SQL Server 2014 databases using SQL Server Administration Studio (SSMS).

  1. Ensuring you have the correct database expanded (in our case, the TaskTracker database), right click on the Tables icon and select Table... from the contextual menu:

  2. A new table will open in Design view. While you have this screen open, do the following:
    • Utilizing the values in the screenshot, full the details inside the Column Name column, data Type column, and allow Nulls column.
    • Make the TaskId column a Primary Key discipline by right-clicking the button close to TaskId (i.e. exactly the same area exactly where the true secret seems in the subsequent screenshot) and picking Set Primary Key.
    • Make the TaskId column an identity column by placing Is identity to yes (you can discover this feature underneath the Id Specification area in the bottom pane). Be aware that to set values in the base pane, you should pick the column name within the leading pane initial. We are environment this column to be an auto-number column - it's going to immediately produce a brand new amount for every document that is created.
    • Set the Default Price from the DateCreated column to (getdate()). (This will automatically insert the present date into that field for each new file).



    What we are carrying out is creating the column names, specifying the sort of data that may be entered into them, and setting default values. Restricting the data sort for each column is extremely crucial and aids maintain data integrity. For example, it may stop us from accidentally entering a task title right into a field for storing the existing day. SQL Server will avoid information from getting into tables in which the data does not adhere towards the rules that we have established for each column.

  3. Save the table by selecting File > Save Table_1 or by right-clicking on the Table's tab and selecting Save Table_1 from the contextual menu:

  4. When prompted, name your table (we'll call it Tasks):


Your New Table

Now that you've created a new table, it will appear under your database in the Tables section. If you don't see it immediately, try refreshing the Object Explorer. You can do this by right-clicking on Tables and clicking Refresh from the contextual menu:

Here's a screenshot of the table with the Columns folder expanded:




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