How to create table with filestream column and Insert data

How to create table with filestream column and Insert data?

Filestream data type in SQL Server  can be used to store images,documents,etc., in database.

In this blog I will explain how to create table with filestream data type.

Before creating table with filestream  column,we have to enable filestream feature in SQL Server configuration manager.Follow “How to enable and configure Filestream in SQL SERVER 2008 / 2012

Create a table and adding filestream data:

1.Create Filestream enabled database.

Syntax:

CREATE DATABASE test
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

2.After you have a filestream enabled database.we now need to create a table and add data to it.

Syntax:

CREATE TABLE [dbo].[test](
   [ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [Number] VARCHAR(20),
   [Description] VARCHAR(50),
   [Image] VARBINARY(MAX) FILESTREAM NULL
)

3.Insert values into table. In below example I am inserting image file in to the table. 

Syntax:

DECLARE @img AS VARBINARY(MAX)
— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(Bulk  ‘C:\image\jellyfish.jpg’, SINGLE_BLOB ) AS y
— Insert the data to the table         
INSERT INTO test (ID,Number,Description, Image)
SELECT NEWID(), ‘MS1001′,’jellyfish’, @img

clip_image001[8]

4.To view the table with filestream included column

image

Advertisements

Create script for all objects in database with data

You can create T-SQL scripts for database, objects in database and insert script for data in tables by using the Generate Scripts Wizard.

These scripts can be used to create database in secondary site with and with out data.

I this blog I will explain how to generate T-SQL script to create database, database objects and data.

Right click the database which you want to script—>Select tasks—>Generate scripts.

clip_image001

 Generate and Publish Scripts wizard screen appears.click Next to continue.

clip_image001[4]

In the Choose Objects wizard screen Select “Script entire database and all database objects” option if want to script all the objects in database or select specified objects which you would like to script and click Next to continue.

clip_image001[6]

In the Set Scripting Options wizard screen.Select the output type as “Save scripts to a specific location” and specify the path where you would like to save the database script file .Click the Advanced button  for specifying the schema and data scripting option.

clip_image001[8]

In Advanced Scripting Options screen; choose the option Schema and data for the Types of data to script option if you want to generate insert scripts for rows in tables  and click OK to save the changes and to return to Set Scripting Options wizard screen.

a) Data only – If this option is selected, it will only script out data within the tables
b) Schema and data – If this option is selected, it will script out Schema as well as the data within the selected objects
c) Schema only – If this option is selected, it will script out the Schema only

clip_image001[10]

In the Summary wizard screen; you will be able to see a quick summary of all the options which you have selected so far. Click Next to confirm and generate the scripts.

clip_image001[12]

In Save or Publish Scripts wizard screen; you will be able to see a Success or Failure message against each object for which the script was requested to be generated. Finally, click Finish to close the wizard.

clip_image001[14]

Once the script is generated you can view it like any other SQL Script. 

clip_image001[16]

HOW TO INSTALL SQL Server CLUSTER IN HYPER-V

To install SQL Server failover cluster we need to have windows cluster configured with a shared disk.

Step 1

If you do not have a windows cluster configured you can create using Hyper-v

Follow the steps in HOW TO CREATE CLUSTER USING HYPER-V to create Domain controller and two member servers which will act as nodes of the cluster.

Once the domain controller and two member servers are created proceed to step 2

Step 2

Once the domain controller and nodes are created install failover cluster feature and create cluster 

To install the failover cluster feature and create a cluster follow the steps in How to install cluster in windows 2008 and windows 2012

Step 3

Once the cluster is created we have to create shared Disk for installing failover aware clustered application like SQL Server.

Follow the steps in ”How to create shared disk using iSCSI Software Target” to create a shared disk using iSCSI target software.

 

Once shared disk is configured invoke Setup from  SQL server installation media .Make sure the node in which you perform the  installation has shared disk online.

image

Run the setup.

Start the installation by selecting New SQL Server Failover Cluster Installation option.

image

Setup support rules will analyze and identify the problems that might occur during setup installation.

clip_image001

After setup validation ,you will get details about operations completed,Click Next and continue

clip_image001[4]

On the Product key page, specify the Product ID key of the product. Click Next.

image

Then read and select the check box to accept terms and agreements.Click Next.

image

Select the developer features which you want and click Next.

clip_image001[6] 

Perform instance configuration.Give SQL server network name.Either you could have  default instance or named instance.But you could have only one default instance.

Click Next.

clip_image001[28]

Allocate required disk space for your installation.Click Next.

clip_image001[12]

Select the SQL Server cluster resource group name.Click Next and continue.

clip_image001[14]

Cluster disk selection will help us to view the disk we have selected for SQL Server failover cluster.

clip_image001[16]

Configure the network resource for your SQL Server failover cluster.Select DHCP check box if you are going to use DHCP network.Otherwise give the IP address for your network(IP address which you provide not be in use currently).

clip_image001[18]

Select the database engine domain group and SQL Server agent domain group. These groups have to be pre created in AD before the installation and startup account of SQL Server services will be added to these groups.  You can also use service SIDs option.

clip_image001[20]

Specify the  account name and password for SQL server agent and SQL server database engine.Click Next.

clip_image001[24]

Specify the authentication mode for your SQL Server failover cluster.Click Next and continue your installation.Verify the cluster installation rules.

clip_image001[26]

  Enable error reporting service if you want or else,click Next and continue.

image

Cluster installation rules will run the setup to determine whether any operations will block the installation.After operation completes.Click Next and continue. 

image

Ready to install page will display the summary of options which we have selected during the setup process.If you want to make any changes,go back and make the required changes or else Click Install and start the installation.

image

Click Close and Complete the installation process.

clip_image001[1]

Successfully we have installed the SQL server failover cluster in first node.

clip_image001[30]

We can install the SQL server failover cluster in remaining nodes by selecting Add node to a SQL Server failover cluster in  installation centre window. To add node to existing SQL Server failover cluster follow the steps in How to add node to SQL Server cluster

 

How to add node to SQL Server cluster

We can add nodes to the existing SQL server failover cluster by following below steps. (Below method can be followed to add node in SQL Server 2008, SQL Server 2008r2 and SQL Server2012)

1. To add new node to existing SQL Server failover cluster, from new node invoke Setup.exe installation media . 

2.The Installation Wizard will show the SQL Server Installation Center as shown in below image.Click Installation an then, select Add node to a SQL Server failover cluster.

image

3.Install the setup support files.

clip_image001[36]_thumb[1]

4.After setup validation ,you will get details about operations completed,Click Next and continue.

clip_image001[38]_thumb[1]

5.On the Product key page, specify the Product ID key of the product. Click Next.

clip_image001

 

6.Then read and select the check box to accept terms and agreements.Click Next.

image

 

7.Then in cluster Node configuration choose the SQL Server instance name and Current Node which we are adding would automatically appear in “Name of this node” . Click Next and continue.

clip_image001[40]_thumb[1]

8.On the Service Accounts page, specify login accounts for SQL Server services.Specify strong password for each account.Click Next after finishing.

image

9.On the Reporting page by default  the option for error reporting is enabled.If you don’t want, disable it and continue.

10.Add Node rules page provides  the information about operation completed.

image

11.Ready to Add Node page displays the options that were specified during Setup. Click Install.

image

12.Add Node Progress page provides the status of the installation process.

13.After installation, the  page provides the summary for the installation.

image

To complete the SQL Server installation process, click Close.

Move services or application between nodes to verify if SQL Server resources are able to failover successfully:

1.After installation you could move the service between all nodes.

2.Open failover cluster manager->expand SQL Cluster->expand services and applications->select the SQL Server group –> Move this service or application to another node –>Choose the newly added node.

clip_image001[44]_thumb[1]

3.After moving the resources to newly added Node check if all the resources are able to come online on newly added node. 

clip_image001[46]_thumb[1]

HOW TO CREATE CLUSTER USING HYPER-V

Hyper –V can be used to create virtualized computers to improve the hardware efficiency. In this blog post I will try to cover how to create windows failover cluster using Hyper-V with out using multiple physical computers. Cluster we are building can be used to install Clustered SQL Server and similar cluster aware applications.

1. We will create a Domain controller

2. We will create two member servers which will act as two nodes of the cluster.

We will start the installation by  Turning on window feature Hyper-V.

To enable Hyper-V Open control panel—>Programs –>Get Programs->Turn windows features on or off –>Enable Hyper-V  as shown in picture below

image

Once the Hyper-v feature is enabled open the hyper v manager and follow the below steps one by one.

Before creating virtual machine create virtual switch to configure network access between the computers which will create and name it.

clip_image001[1]

Select the type of virtual switch for your virtual machines.

clip_image001[3]

Give the name for your Virtual switch and click ok.

clip_image001[5]

Now create New virtual machine by Right clicking SQLPC –>New->Virtual machine.

image

2.New virtual machine wizard will appear.Click Next to continue.

 

clip_image001

Specify the name for your virtual machine.Let us Create domain controller  as DC.

image

Create a new folder or use the existing folder to store the virtual machine.Specify that location in location tab.Click Next 

image

Specify the amount of memory you want to allocate for this machine.Then,click Next.

image

 

Inorder to do OS installation ,Connect to virtual hard disk and specify the size for it.Click Next

image

Then install an Operating system from CD or as image file which you have in your system.

image

Browse and select the file for installation.Click open.

 

image

Now,click finish and complete the virtual machine wizard.

image

Similarly,Follow the above steps and create two cluster nodes as clusternode1 and clusternode2 .

Now Hyper –V manager will have three virtual machines which we created one as DC and others as clusternodes.

Right click DC->Start->connect.(similarly do for all)

image

Cluster nodes will start running.

image

DC virtual machine connection window will appear,click next.

image

Installation window will appear,click install now to start installation.

image

Select the operating system you want to install.Select  windows server 2008 R2 Enterprise(Full installation) for DC(domain controller).Click Next.

image

Accept the license term and click next

 

image

Select the type of installation as Custom(advanced)

clip_image001[5]

Click Next and continue

image

 

image

After installation restart your virtual machine to update with new setting

image

Give the password for login.

image

Domain controller is installed with OS.

image

Perform the task  which you want or else close the window.

image

Configure TCP/IP address and enable LAN connection.

To configure TCP/IP, open control panel->Network and internet->Network connections->click IPV4

clip_image001[7]

Give IP address ,subnet mask and default gateway ,click ok.

clip_image001[9]

Open  dcpromo .

image

Active directory domain service wizard will appear.Click Next and continue

image

Select create new domain in a new forest

clip_image001[11]

 

Give forest root domain which acts as domain.

clip_image001[13]

 

Set forest functional level .Default Functional level is windows sever 2003.

clip_image001[15]

 

Check DNS server and click Next.

clip_image001[17]

 

Click Yes and continue.

clip_image001[21]

 

Select the folder to store databases,log files and click Next.

clip_image001[23]

Give the administrator password and click Next.

clip_image001[25]

Summary of your selections will appear,review your selection and click next.

 

clip_image001[27]

 

DNS installation will be completed.

clip_image001[29]

 

Click Finish and complete domain services installation wizard.

clip_image001[31]

Restart your computer after installation.

 

clip_image001[33]

Now do the similar things for cluster nodes 1&2.

Select computer->properties->computer name.Change the computer name and give domain name  which you gave in DC.

clip_image001[35]

 

Configure IPV4 properties for cluster nodes.

clip_image001[37]

 

clip_image001[39]

Click OK.

clip_image001[41]

Restart your computer to apply the changes.

clip_image001[43]

Click Ok,to restart.

clip_image001[45]

Now we have domain controller and member servers. We have to install failover cluster feature and make this members servers as node of cluster which we are going to create.

To install the failover cluster feature and create a cluster follow the steps in https://sqlserverscribbles.com/2013/06/17/how-to-install-cluster-in-windows-2008-and-windows-2012/

Once the cluster is created we have to create  shared Disk for installing failover aware clustered application like SQL Server. Follow the steps in  https://sqlserverscribbles.com/2013/06/16/how-to-create-shared-disk-using-iscsi-software-target/  to create a shared disk using iSCSI target software.

How to create SQL Server Agent Jobs

SQL Server agent can be used to create JOBS which can be scheduled to execute automatically. SQL Server Agent uses SQL Server to store job information. Jobs can have one or more job steps. Each step contains its own task.Each action in a job is a job step.We can schedule the job based on need as daily, weekly, monthly, hourly etc

For example, Let us create a job to back up a database automatically.

Steps to create Job in SQL Server Agent

1. Expand SQL Server Agent in Object Explorer.(If SQL Server Agent is stopped ,right click and start it)

image

2.Right click Job->New job.

image

3.Fill  the Name field with a  name for your job,Give description.

image

4. On the left side of the New Job window, you’ll see a Steps icon under the Select a page.Next add the individual steps for your job. Click the New button to create a new job step and you will see the New Job Step window.

image

  • Use the Step Name textbox to provide a name for the Step. select the database where the job has to be applied.
  • Use the Command textbox to give the Transact-SQL syntax corresponding to the desired action for this job step. Once you have completed entering the command, click the Parse button to verify the syntax.
  • Validate the syntax, click OK to create the step.

image

5.Schedule the job by clicking the Schedule icon in the Select a Page.Then schedule new job.

image

6.Create alerts for your job by Giving alert name and also select the database.Also select the type of alert and severity of your alert

image

7.Specify the actions to be performed when the job completes or when job fails.

image

Note :All the above steps can be performed by TSQL (with out the GUI) using follow stored procedures

sp_add_job to create a job.
sp_add_jobstep to create one or more job steps.
sp_add_schedule to create a schedule.
sp_attach_schedule to attach a schedule to the job.

Trace waits in SQLServer (SQLTRACE_BUFFER_FLUSH,TRACEWRITE,SQLTRACE_WAIT_ENTRIES,SQLTRACE_LOCK)

When you run Profiler trace from client systems or on server with large number of events you will see below wait types.

SQLTRACE_WAIT_ENTRIES
SQLTRACE_LOCK
SQLTRACE_BUFFER_FLUSH
TRACEWRITE

There is no way to completely avoid this wait type without stopping all the traces. We can reduce this waitypes by configuring server side trace instead of client side trace.

select * from sys.traces will give you information about all the traces. ( Status column 0 stopped and 1 active)

For the traces collected using profiler you will find a NULL Path. Profiler traces can cause large number of above waittypes.

Thread which raises the trace event is responsible to Get buffers to write event and write event. So collecting the trace on network share or on slow disk or using profiler can slow down the trace write and make the threads wait, sometimes we may also end with dead locked schedulers.

So ideally you have to avoid running profiler when you see below waits and use sp_trace_create if you like to capture the trace and pass the local path for tracefile parameter.