Friday, October 19, 2012

SQL SERVER - How you maintain production database in daily basic

 How to maintain production database in daily basic. There are some basic steps, we can check/ consider / follow to it, To maintain the SQL database-

  • Is all database is online or not
  • Typically, I check and verify scheduled to database backup.
  • All jobs run successfully and completed.
  • See to Job History / Log file Viewer
  • See to Job Activity Monitor
  • Check the database size.
  • Current transaction
  • Error log and Agent error log.

Like and Share to SQL Integrity Blog

Saturday, October 13, 2012

SQL SERVER - Step by Step deployment of package in SQL Server.

 Here I am sharing very basic and simple step of deployment of package in SQL Server which are as follows:-

1.      Create package-using BIDS.

2.      Right click on the solution in BIDS and select property.

3.      Set true second option that is CearteDeploymentUtility in property window.

4.      You can also change the path of package to change the 3rd property in property windows.

5.      Go the stored location of package, and open the Bin folder, and into open the deployment folder (C:\ssisdeployement\ssisdeployement\bin\Deployment) where you will get the two files one is .dtsx and second is .manifest extension files.

6.      Run the manifest file and select the storage type of file system; choose the path of package (C:\Program Files\Microsoft SQL Server\100\DTS\Packages\ssisdeployement). We can select the file system or SQL database itself. Next and finish it.

7.      Now open the deployed location of the package (C:\Program Files\Microsoft SQL Server\100\DTS\Packages\ssisdeployement) and run it, and set the source server connection and target server connection.

8.      Open SSMS and SSIS in SQL Server.

9.      Open stored package navigate it and right click on the package and select configuration manager, select  the used connection and

10.  Run the package. And see the result in SSMS

11.  If you want to run package in scheduled time, we need to create a job and can schedule the package in SQL Agent.

Like and Share to SQL Integrity Blog

SQL SERVER - How to create Full Text Search in SQL Server

Just follow this simple steps for Full Text Search in SQL Server
1.      Open the Solution Explorer in SSMS and navigate to database
2.      Into the database navigate to Storage Folder
3.      Rigth click on the full text Catalog and provide the Catalog name and press OK button
4.      Now open to database table, and right click on the table and go into the   Full Text Index option and select the ‘Define Full Text Index’ option.
5.      Then Full Text search wizard will open and click on next and choose the Indexed column or Identity column (i.e. EmployeeID) from drop down list, and when will select query will fire this indexed column must be in a query (See Example – EmployeeID).
6.      Next, then select the Column name (i.e. Title) for full text searching and select the language. Then click Next
7.      select any one the change track that are automatic(for default), manual and Do not change track, then Click Next
8.      Select the Catalog name and next and Finish.
9.      Then run the below query on SSMS.

Examples :-
   -- This query will search every Column of the table

SELECT EmployeeID , [Title]   FROM [Employee]
         where freetext (*, 'Accountant or Manager')

   -- or it will search only Title Column

SELECT EmployeeID , [Title]   FROM [Employee]
where  contains (Title , 'Accountant AND Marketing')

Like and Share to SQL Integrity Blog