SQLCMD Mode in Query Editor (!! for SQL CMD) Execute Operating System Command from SQL Server

By enabling the SQL CMD mode we can execute SQLCMD Scripts from the management studio along with T-SQL Statements.

We need to enable SQLCMD Mode to write sqlcmd scripts
SSMS -- Query Menu -- SQLCMD

After enabling the SQLCMD we can execute queries by using !!

select * from kalyandb..Items
!!dir
select count(*) from kalyandb..Images

We can execute complete set from the Query Editor. Here we should remember one thing, if you enable SQLCMD, intellisense will not work.

Tail Log Backups in SQL Server

What is Tail-Log Backup?

A tail-log backup captures the log records that have not been backed up since the recent last log backup to current point in time before restore. Generally we generate a tail log to make database into NoRecovery mode (Inaccessible). The tail log backup can be generated from FULL / BULK-LOGGED Recovery model databases.

Unlike other log backups, this tail log backup can be generated even if database is damaged. The tail log backup is also generated using backup log command only with two options a) NoRecovery and b) continue_after_error

NoRecovery – We need to use this option when database is online, After issuing this command it ensures Database is not changed after tail-log backup, it makes database in restoring state, if database is in restoring state, users requests will not processed.

Backup log database_name to disk =’path of tlog file’ with norecovery

No_Truncate / Continue_after_error – Using one of this options we can generate a tail-log backup on a damaged database.

a) Create a database and table in it
b) Insert few records into the table
c) Offline the database and rename data file
d) Try to bring the database online, it wont allow because, it wont find the data file

At that time we can use the below command to generate tail log file from a damaged database.

Backup log damaged_dbname to disk=’path of tlog file’ with no_truncate

Using No_Truncate option, the current active transaction log can be backedup when the data files are inaccessible or damaged or missing and log file is undamaged.

Backup log damaged_dbname to disk=’path of tlog file’ with continue_after_error

Full Text Index in SQL Server

If we have varbinary(max), or varchar(max) columns in the sql server table we cannot create indexes on those columns using the standard clustered or non-clustered indexes. The Full Text Index is the solution for this. Full Text Index can be achieved through full text search feature in SQL Server. It helps to run the full-text queries against character based data in sql server tables.


Steps

1. Create a full text catalog

Use DatabaseName
Create FullText Catalog MyFTCatalog
Select fulltext_catalog_id, name from sys.fulltext_catalogs

After creating full text catalog we need to create full text index on the required table. To create full text index, Table must be having unique index. If you don’t specify the catalog name while creating full text index then it will attach to default catalog.

2. Create full Text Index

Create FullText Index on Items(ItemDescription, ItemContent Type Column ItemExtention Language 1033)
Key Index Ix_Item_ID on MyFTCatalog

Select * from sys.fulltext_languages – Full Text Supported Languages.
Select * from sys.fulltext_document_types – Displays list of document types supported by full text search.

Below Query is to display list of catalog names and table name in the catalog

SELECT st.name AS TableName, sc.name AS FTCatalogName
FROM sys.tables st, sys.fulltext_indexes si, sys.fulltext_catalogs sc
where st.object_id = si.object_id and si.fulltext_catalog_id = sc.fulltext_catalog_id

Select * from Items
Where contains([ItemContent], '"*Microsoft*" OR "*SQL Server*"');

Select * from Items
Where FreeText([ItemContent], '"*Microsoft*" OR "*SQL Server*"');

Memory Management

SQL Server Buffer pool is an address space of SQL Server which is used to manage client requests.
Below are the data structures allocated in buffer pool. The buffer pool is furthur divided into different into multiple segments.


SQL Server Buffer Cache

SQL Server Buffer cache or data cache, is an area of the Buffer pool. (Memory Pool).
Where the data pages of database reside in the memory is known as Buffer cache.
Buffer cache hit ratio performance counter indicates the performance of Buffer cache. The value 95% indicates availability of the database in the memory, ie., 95% of time datapages are available in the memory. If it show <95% then there might be need of more physical memory for the server.

To determine buffer cache hit ratio

Start – Run – Perfmon – Select SQL Server Buffer Manager – Select Buffer Cache Hit Ratio Counter

Procedure Cache
It contains all execution plans of T-SQL Statements that are executed on the Instance. Procedure Cache is used to cache the query plans. It allows reusing of query plans. We can find more information like size and activity using a DBCC command called DBCC PROCCACHE.
To Clear the procedure cache DBCC FREEPROCCACHE

Log Cache – Reserved for reading and writing log pages.

Connection Context – Each connection to the instance has a small memory area to record current state of the connection. It includes parameters of stored procedures, cursor positioning etc.,

Dirty Page
Any Uncommited information resides in the buffer cache is known as dirty page. In other words the modified data in buffer cache which is not flushed into disk.

Lazywriter
DML Queries like Insert, Update and Delete will always make modifications to data pages in memory.
When a modification happened to a page in memory then that page will marked as “dirty” indicates that there are some changes happened. There are two internal processes of memory management are Lazywriter and CheckPoint. Both are used to scan the buffer cache, The job of lazywriter is to identify dirty page in the disk and writes into disk and drop from the buffer cache. Its responsibility is to keep certain amount of free space in the buffer pool to be used by server.

Checkpoint
The Checkpoint process is scans the buffer cache periodically and writes dirty pages into disk. The difference between lazywriter and checkpoint is it doesn’t keep available buffers in buffer pool in memory pressure. The job of checkpoint is to minimize the number of dirty pages to reduce the length of recovery time if the server crash. A checkpoint will occur after many database actions like detach, attach, or after any DDL statement, or we can issue manually checkpoint.

The frequency of checkpoint process is configure using a server setting “recovery interval” This setting specifies how long a recovery shoult take.

BCP Examples

[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file] [-d database name]

*** Run the below command from command prompt ***

bcp "SELECT * FROM Items" queryout Items.csv -SSQL2008 -dkalyandb -T -t" " -c

The above query generates a csv file in the current location

-S Indicates ServerName (SQL2008)

-d kalyandb (Database Name)

-T Trusted Connection (Windows Authentication)

-t Field Terminator (" " -- Here we are specifying space)

-c Indicates Character Type File

Logshipping Tables

The below are few tables provides the essential information about the logshipping databases.
For more information Browse BOL for "Log Shipping Tables and Stored Procedures"
All these tables are stored in MSDB database.

log_shipping_primary_databases -- Displays configuration information for primary databases on the server.

log_shipping_primary_secondaries -- Displays mapping information between primary databases to secondary databases.

log_shipping_secondary -- Displays secondary server database details along with Primary server database information.

log_shipping_secondary_databases -- Displays configuration information for secondary databases on the server.(Run command from Logshipping Secondary Server)

Using the above logshipping tables we can get the last restored log file, last backup file, last copied file, backup source directory, backup destination directory etc.,

Script to display indexes

Select so.name as Table_Name, sc.name, si.name as Index_Name, si.type_desc
from sys.objects so, sys.indexes si, sys.columns sc, sys.index_columns sic
where so.object_id = si.object_id and sc.object_id = so.object_id and sic.object_id = so.object_id
and sic.column_id = sc.column_id and sic.index_id = si.index_id
and so.type_desc = 'USER_TABLE' and so.name = 'Address'

FileStream In SQL Server 2008

Overview of FileStream

“FileStream” is a new feature introduced in SQL Server 2008 to store unstructured binary files into the database. FileStream Storage implemented as a varbinary(max) column in which data is stored as BLOB in the filesystem. To Specify the column should store data on the filesystem, specify the FILESTREAM attribute on varbinary(max) column. This causes database engine to store all data in the filesystem but not in the databaes file.

a) VarBinary(MAX) datatype to store unstructured information, whereas it has a limitation of 2 GB file. The limitation is not applicable for FileStream.
b) FileStream datatype can be queried through T-SQL
c) Binary data stored as individual files outside the database.
d) These individual files accessed through Win32 API file operations.

How we stored unstructured information before SQL2008?

Earlier we follow different methodologies to store unstructured information like word documents, images, etc into database like, we will keep all those unstructured files in a folder on the operating system and will provide path of the file to a database field with varchar datatype.

After that using LOB (Large Object Data), we can store that unstructured information within the database itself, but it has large overhead while querying LOB tables. LOB Datatypes are TEXT, NTEXT, VARCHAR(MAX), XML, VARBINARY, IMAGE Etc., And it has limitation to manage only 2 GB data.

Usage

1. SQL Server 2008 or later versions are required to make use of FileStream
2. Filegroups created for FILESTREAM must be on shared resources for fail-over clustering.
3. Storage volume must be greater than 1 MB to use FileStream
4. It has faster read access than LOB
5. Each Row data should be maintained by unique row Id.

Limitations

1. Database Mirroring is not supported.
2. TDE is not supported.
3. Database Snapshots are not supported.

Example:

Enable FileStream

1) Open Configuration Manager
2) Select SQL Server DB Engine – Properties – Click on FileStream Tab
3) Check Enable FileStream for Transact-SQL
4) If you want to read and write Filestream data from windows, Click Enable FileStream for file I/O Streaming Access.

From SSMS

Sp_configure ‘filestream access level’, 2

0 – Disable FileStream Support
1 — Enable FileStream for T-SQL
2 —Enable Filestream for T-SQL and Win32 Streaming access.


Step1
Sp_configure ‘filestream access level’, 2
Reconfigure

CREATE DATABASE TestFileStream On Primary

(Name = TestFileStream_Data,

 FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\TestFileStream.mdf'),

 FileGroup FileStreamGroup Contains FILESTREAM(Name = FileManagement_Group, FileName = 'C:\Temp\Images_Data')

 Log On (Name = TestFileStream_Log,

 FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\TestFileStream.ldf')

 Use TestFileStream 

 create table ImageInfo (ImageId UniqueIdentifier

                                       Not Null ROWGUIDCOL PRIMARY KEY,

                         ImageDescription Varchar(200) Null,

                         ImageData Varbinary(Max) FileStream Default Null)

-- Variable to Store ImageData

DECLARE @img AS VARBINARY(MAX)

SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK 'C:\ShadesOfBlue.jpg', SINGLE_BLOB ) AS x

-- Inserting Images Into Table

INSERT INTO ImageInfo (ImageId, ImageDescription, ImageData)

SELECT NEWID(), 'Green Shadow', @img

-- To Verify the FileStream Path From T-SQL

Select Name, Physical_name, type_desc from TestFileStream.sys.database_files

-- Backup FileStream Database will automatically backup the contents of the Image information

BACKUP DATABASE TestFileStream TO DISK ='C:\TEMP\TestFileStream.BAK'

-- Restoing of a FileStream Enabled Database will helps to restore the image folder to File System, Before Restore Run the below command to verify the information about the files

RESTORE FILELISTONLY FROM DISK ='C:\TEMP\TestFileStream.BAK'

-- Drop Database will drop the attached target folder at the file system.

DROP DATABASE TestFileStream

Moving Tables Between File Groups

CREATE DATABASE TESTDB

USE TESTDB

Create table TestTable (Id Int identity primary key, Name Varchar(20))
Insert into TestTable (Name) Values ('SQL Server 2008')
go 2000

The below query displays file group name and data file associated to it. Initially it displays only one record  because there wont be any filegroup for LDF file

select fg.name as filegroup, (case when fg.is_read_only =0 then 'Write Mode' else 'Read Mode' end),
         f.name, f.physical_name
from sys.filegroups fg, sys.database_files f
where f.data_space_id = fg.data_space_id

ALTER DATABASE TESTDB ADD FILEGROUP SECONDARY_FG

ALTER DATABASE TESTDB
 ADD FILE (NAME = 'Second_File',
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\SecondaryFG.ndf')
     TO FILEGROUP SECONDARY_FG

ALTER DATABASE TESTDB ADD FILEGROUP THIRD_FG

ALTER DATABASE TESTDB
 ADD FILE (NAME='Third_File',
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\ThirdFG.ndf')
     TO FILEGROUP THIRD_FG
Create a NonClustered Index on Name Column

CREATE NONCLUSTERED INDEX IX_TestTable ON TestTable (Name)

-- Based on the below query we can identify the table and index stored along with file group information
Select so.name as Table_Name, sfg.name as FileGroupName, sc.name as ColumnName, si.name as Index_Name, si.type_desc as Index_Type
from sys.objects so, sys.indexes si, sys.columns sc, sys.index_columns sic, sys.filegroups sfg
where so.object_id = si.object_id and sc.object_id = so.object_id and sic.object_id = so.object_id
 and sic.column_id = sc.column_id and sic.index_id = si.index_id and si.data_space_id = sfg.data_space_id
and so.type_desc = 'USER_TABLE'

To Move Primary Key / Unique Clustered Index

When there is primary defined on the column automatically it creates a unique clustered index
Hence the base table is stored with primary key clusterd index, so moving the clustered index will moves the base table

CREATE UNIQUE CLUSTERED INDEX PX_TestTable ON TESTTABLE (ID) WITH (DROP_EXISTING = ON) ON [SECONDARY_FG]

Eventhough if you move Unique Clustered Index, Non-Clustered Indexes will not move automatically.
We need to move those manually using the below syntax.

CREATE NONCLUSTERED INDEX IX_TestTable ON TESTTABLE(ID) WITH (DROP_EXISTING = ON) ON [SECONDARY_FG];

Suppose if you have a Clustered Index then use the below syntax to move.

ALTER TABLE [dbo].[TestTable] DROP CONSTRAINT [PX_TestTable]

CREATE CLUSTERED INDEX PX_TestTable ON TESTTABLE (ID) ON [SECONDARY_FG]

What is Server Core

The server core is a minimal installation avaiable at Windows Server 2008. A core installation provides a minimal environment for running specific server roles which reduces maintenance and management. No GUI available after server core installation, ie., No Windows Explorer, No Start Menu, No Internet explorer, No .NET Framework, No Powershell etc.,

When there is nothing available why we need to go for server core

Uses less disk space, less memory

Less need for maintenance

Less need for patching

System is more stable and security

Decreased attacking surface

Msg 3723, Level 16, State 5, Line 1 An explicit DROP INDEX is not allowed on index

We may receive the below error message while remvoing index from a table.

An explicit DROP INDEX is not allowed on index 'TestTable.PX_TestTable'. It is being used for UNIQUE KEY constraint enforcement.

This cannot be dont using Drop Index, we need to use Alter Index statement to remove index.

The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. To remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause.”


ALTER TABLE [dbo].[TestTable] DROP CONSTRAINT [PX_TestTable]

HEAP Table / Clustered Table

A Table without clustered index is called HEAP Table.

When you define a clustered Index on heap table, then it gets a structure then that table can be called as Clustered Table.

Clustered Index - 

SQL Server 2012 : Format, Concat Function

Microsoft Introduced two new string functions Format, Concat

Format Function is used to change the format of the string or date or currency into specified format along with Culture.

Format ( Value, Format, [Culture])

Examples

select getdate() -- 2012-05-17 19:26:13.047
select format(getdate(),'d') -- 5/17/2012
select format(getdate(), 'd', 'en-US') -- 5/17/2012
select format(getdate(), 'd', 'en-IN') -- 17-05-2012

declare @amt money
set @amt = 120165.77
select @amt -- 120165.77
select format(@amt, 'c', 'en-US') -- $120,165.77
select format(@amt, 'c', 'en-IN') -- Rs. 1,20,165.77
select format(@amt, 'c', 'ru-RU') -- 120 165,77р.

Format Function Can be used for customize date format
select format(getdate(), 'dd-MM-yy') -- 170512
select format(getdate(), 'ddd') -- Fri
select format(getdate(), 'MMM') -- May
select format(getdate(), 'yyyy') --2012
select format(getdate(), 'dd-MMM-yy') -- 17-May-12
select format(getdate(), 'hh:mm:ss') -- 07:19:02
select format(getdate(), 'hh:mm:ss tt') --07:19:12 PM


select format(getdate(),'d') -- 5/17/2012 (Short Date)
select format(getdate(),'D') -- Thursday, May 17, 2012 (Long Date)
select format(getdate(),'t') -- 7:22 PM (Short Time)
select format(getdate(),'T') -- 7:22:40 PM (Long Time)

Concat  -- It is used for concatenating two strings.

Concat (string1, string2 .....)

select 'Kalyan ' + 'Akula'
select 'Kalyan - ID' + 32650   -- It returns error because of an integer.

select concat('Kalyan ','Akula')
select concat('Kalyan - ID ',32650)  -- Here it is possible.

SQL Server 2012 : Choose Function, IIF Function

Microsoft introduced two new logical functions in SQL Server 2012.
1. Choose
2. IIF

The Choose function returns the item based at specified index from a given list.

The IIF Function returns one or more values based on a given logical expression.

Choose

Choose ( index, value1, value2, value3......)

select choose (2, 'kalyan','kumar','akula')   -- Returns "kumar"

Select Choose(Product_Number, 'SQL 2000', 'SQL 2005', 'SQL 2008', 'SQL 2008 R2', 'SQL 2012')
from product

IIF

IIF ( boolean, true, false)

Select IIF(datename(dw,getdate())='sunday','Today is Sunday', Not A Holiday')

SELECT IIF((datename(dw,getdate()) in ('Saturday','Sunday')), 'Oncall',
               IIF((datename(dw,getdate()) in ('Monday')), 'Incident Management',
               IIF((datename(dw,getdate()) in ('Tuesday')), 'Release Management',
               IIF((datename(dw,getdate()) in ('Wednesday')), 'Change Management',
               IIF((datename(dw,getdate()) in ('Thursday')), 'Problem Managemnt',
               IIF((datename(dw,getdate()) in ('Firday')), 'Documentation', 'Enjoy Holiday'))))))

Saving Changes is not permitted, the changed you have made require the following tables to be dropped and re-created

Saving Changes is not permitted, the changed you have made require the following tables to be dropped and re-created. You have either made changed to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created

You may get the above error while modifying the table design in SQL 2008 or SQL 2012.

Solution
1) Open SSMS
2) Tools - Options
3) Designers -- Table and Database Designers
4) Uncheck Prevent Saving changes that require table re-creation

Attach Adventure Works Sample Databases SQL 2008, SQL 2012

Microsoft provides few sample databases for all SQL versions to work with it.
Search with "adventureworks database download" keyword in google and open the codeplex site and download the required version of your chocie.

The download will provide only MDF file. To attach sample databases, follow the below process

1. Download the MDF to your required location (C:\temp\2012\)
2. Attaching database can be done in two ways
       a) Using T-SQL Syntax
       b) Using GUI

Using T-SQL

CREATE DATABASE [AdventureWorks2012] ON ( FILENAME =N'C:\Temp\2012\AdventureWorks2012_Data.mdf' ) FOR ATTACH
GO

Using GUI

1. Open SSMS
2. Right Click on Databases - Attach
3. Browse and select the MDF File
4. You will find two files in the database details window
            1) MDF File
            2) LDF File --- Not Found
5. You need to select the Not Found Row -- Remove
6. Click OK to attach the database with out LDF file

Fix - The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

I received the below error while login into instance,

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

I tried with SQL Authentication, it is working whereas windows authentication is not working.  Later I verified in the error log, if found lot of event - 17806  SSPI handshake failed  errors on the error log file.

This might be because of SPN Entry is missing on the server which you are looking for.

Work Around

Register SPN for the SQL Service

Setspn -A MSSQLSVC/MySQLServer:3443/SQLService

List SPN Entries

Setspn -L <serviceAccount> --Displays list of entries of hosts which is using this service account.

Paging Option in SQL Server 2012

Paging Result Using T-SQL

We need to use OFFSET, FETCH Next Functions inline with Orderby clause to get the pagination in T-SQL

select * from HydStock order by SlNo offset 10 Rows Fetch Next 20 ROWs only
The above query displays resultset starting from 10th row to 20th row

select * from HydStock order by SlNo offset 20 Rows Fetch Next 30 ROWs only
The above query displays resultset starting from 20th Row to 30th row.

In place of numbers we can pass variables also while doing programming.

The above syntax needs to be specified on a column and followed by range. Starting range will be started with Offset ‘N’ ROWS and ends with Fetch Next ‘N’ ROWS Only

Backup / Restore Progress

Select  command, percent_complete from sys.dm_exec_requests where command like 'Backup%'

Select  command, percent_complete from sys.dm_exec_requests where command like 'Restore%'


SELECT command,
            es.text,
            start_time,
            percent_complete
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) es
WHERE er.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')


SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Contained Databases - SQL Server 2012

SQL Server 2012 introduced a new feature called Contained Databases.

Database is a collection of data objects, views, sps and users also. If you move database from ServerA to ServerB then we might not able to access the database using ServerB Logins because there is a dependency between server and database, logins will be stored under server level and users are stored at database level.

Hence database is not a individual component, This contained database can break the barrier of dependency.

If we migrate the contained database into other server (from ServerA to ServerB) then we need not to createa any logins to make use of it.

Steps To Create Contained Database
I) sp_configure 'contained database authentication',1 
reconfigure

II) create database KalyanDB containment = partial  -- It is used to make our database into partial contained db. We can change this while creating new database also from the UI, In options tab under containment type, we can select whatever containment is requied.

III) create a database user (DatabaseName - Security - Users - New User)
Unlike previous editions, this wizard will have so many options in UserType list (SQL User With Login, SQL User with Password etc.,) -- Select SQL User with Password option
create user kalyantest with password = password
IV) Select Membership tab --> select whatever database role you want for that login

To Test this feature on the same server
I) Click on Connect -> Select SQL Server Authentication
Login - KalyanTest
Password - password
II) Click on Options
III) Under Connection Properties
         Connect to database -- Select name of your contained database -- ok
Iv) Now you can able to connect to the contained database, eventhough you dont have login created on the server.

SQL Server 2012 Startup Parameters

Unlike earlier versions from SQL Server 2012 onwards, we have a new window to add startup paramerts like trace flags and other parameters to SQL Engine. Below is the screenshot for reference.

Installating 32bit SQL Server on Windows 64bit Machine

Can we install SQL Server 32bit version on 64bit Windows Operating System, The Answer is YES.

Please select the option x86 at the time of installation from the SQL Server Installation Wizard.

Xlib: connect to ":0.0" refused by server not able to lauch Oracle GUI

When I am trying to install oracle from Oracle Enterprise Linux I got the below error while launching GUI Interface.

Xlib: connect to ":0.0" refused by server

Xlib: No protocol specified

xhost: unable to open display ":0.0"

I am able to execute xhost commands from root user, whereas when i am running it with oracle user i am getting the above error.

To resolve it

root > xhost +

output --- access control disabled, clients can connect from any host

login from oracle using other terminal, run the below command to verify whether xhost commands are working or not

oracle > xclock --> If it displays clock then you directly launch oracle Installation GUI using runInstaller

If not login into Lunix using oracle login then type startx, you will be able to work with xhost.



What is SQL Azure?

Microsoft SQL Azure is a cloud based version of SQL Server. It is based on Clustered SQL Servers and databases are replicated and load balanced.

It provides high Availability, Scalability, Security and Reliability.

Note - It works very well if it is inline with Windows Azure Services.


SQL Azure Topology

Moving System Databases (Change location of system databases)

How to move MSDB database into other drive?

use master
select * from msdb..sysfiles

use master
alter database msdb modify file (name=msdbdata, filename='c:\temp\msdbdata.mdf')
go
alter database msdb modify file (name=msdblog, filename='c:\temp\msdblog.ldf')

How to move MODEL database into other drive

use master
select * from model..sysfiles

use master
alter database model modify file (name=modeldev, filename='c:\temp\model.mdf')
go
alter database model modify file (name=modellog, filename='c:\temp\modellog.ldf')

Run select * from sysaltfiles query and verify whether path has been changed in the metadata or not, Once it is changed we need to perform the below steps to move databases.

1. Stop the services
2. Copy physical files into new path
3. Start the SQL Service

How to move Temp Db database into other drive?

use master
select * from tempdb..sysfiles

use master
alter database tempdb modify file (name=tempdev, filename = 'c:\temp\tempdb.mdf')
go
alter database tempdb modify file (name=templog, filename = 'c:\temp\templog.ldf')

Only difference with tempdb is we need not to copy this database into new location, because whenever we start the SQL Service tempdb database will automatically created by SQL Engine.