WSS ships with a handy command-line utility named STSADM.EXE. This utility allows you to run interactive commands from the Windows command line and to script batch files that accomplish administrative tasks such as creating, backing up, and restoring site collections. When you run this utility from the command line or from a batch file, you must pass the –o parameter followed by one of the supported operations. Here’s an example of a command line instruction to create a new site collection at a specific URL.
STSADM.EXE –o CreateSite –url http://mshehadeh/sites/blog
-ownerlogin mshehadeh\admin
-owneremail mail@mshehadeh.com
-sitetemplate STS#0
Note that this example has introduced line breaks between the parameters to make things more readable. However, you cannot actually use line breaks between the parameters when running the STSADM utility from the command line or from a batch file.
Keep in mind that the installation of WSS adds the STSADM.EXE utility to a WSS system directory deep within the Windows Program Files directory. If you want to be able to call this utility directly from the command line on your development workstation, you should add the following path to your configured System path.
c:\program files\common files\microsoft shared\web server extensions\12\bin\
This System path is known as 12HIVE
When you write a batch file, you should also assume that it might be run on a machine that does not have the proper System path configured. Therefore, you should write batch files that explicitly specify the location of the STSADM.EXE utility.
@SET STSADM="c:\program files\common files\microsoft shared\
web server extensions\12\bin\stsadm"
%STSADM% –o CreateSite –url http://mshehadeh/sites/blog
-ownerlogin mshehadeh\admin
-owneremail mail@mshehadeh.com
-sitetemplate STS#0
Once again, the line breaks in the preceding example are only for readability. You will want to remove them when writing an actual batch file.
Monday, April 21, 2008
SharePoint Training Tutorials
Step by Step How To Instructions
In these SharePoint screencast tutorials, get step by step instructions on all the features included in SharePoint hosting and how to optimize your site. The tutorials cover topics from Alerts and Creating Web Parts to Creating Meetings Sites and Wikis.
Go ahead and try it at http://www.frontpages-web-hosting.net/sharepoint-hosting/free-sharepoint-tutorials.asp
In these SharePoint screencast tutorials, get step by step instructions on all the features included in SharePoint hosting and how to optimize your site. The tutorials cover topics from Alerts and Creating Web Parts to Creating Meetings Sites and Wikis.
Go ahead and try it at http://www.frontpages-web-hosting.net/sharepoint-hosting/free-sharepoint-tutorials.asp
Saturday, April 19, 2008
Optimization of Virtual Machine Size and Performance
While googling for a software that help me on optimizing my virtual machines size,i found a great software named vOptimizer.
vOptimizer is an advanced optimization solution that quickly and easily reduces a virtual machines virtual hard drive to the smallest size possible while optimizing Windows guest operating systems for speed and performance. vOptimizer 4.0 offers many new functions for enterprise level customers while improving the interface and ease of operation for smaller, desktop oriented users. vOptimizer Desktop Edition enables unlimited optimizations of local VMs while Network Edition enables unlimited optimizations of VMs located anywhere across the network as well as scheduled optimizations. vOptimizer FreeWare is essentially Desktop Edition with a number of features disabled and additional popup messages
Download vOptimizer
vOptimizer is an advanced optimization solution that quickly and easily reduces a virtual machines virtual hard drive to the smallest size possible while optimizing Windows guest operating systems for speed and performance. vOptimizer 4.0 offers many new functions for enterprise level customers while improving the interface and ease of operation for smaller, desktop oriented users. vOptimizer Desktop Edition enables unlimited optimizations of local VMs while Network Edition enables unlimited optimizations of VMs located anywhere across the network as well as scheduled optimizations. vOptimizer FreeWare is essentially Desktop Edition with a number of features disabled and additional popup messages
Download vOptimizer
How to Create a MOSS 2007 VPC Image
As SharePoint-y guys - virtualization is a *must* for us.
I found a great article about creating a sharepoint Virtual PC Image,this is a complete step by step article that shows you How to Create a MOSS 2007 VPC Image
Enjoy it
I found a great article about creating a sharepoint Virtual PC Image,this is a complete step by step article that shows you How to Create a MOSS 2007 VPC Image
Enjoy it
I am back
I am back to writing . i will be writing about Windows Sharepoint Services 3.0 (WSS 3.0), and Office Sharepoint 2007 (MOSS 2007).,
I will share with you some resources and articles about WSS 3.0 and MOSS 2007
I will share with you some resources and articles about WSS 3.0 and MOSS 2007
Thursday, September 27, 2007
Restoring SQL Server 2005 Suspect Database
I had a SQL Server 2005 database in suspect mode,so i couldn't work on transaction until i repaired,while i am searching on the internet to resolve this problem i found an new Database status called Emergency introduced in SQL Server 2005.
This mode can change the database from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode.
Please follow the following steps to change the database status from suspect to emergency than to it's normal state:
EXEC sp_resetstatus 'DBname'
ALTER DATABASE DBname SET EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBname SET MULTI_USER
I hope that will help you to get your database working
This mode can change the database from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode.
Please follow the following steps to change the database status from suspect to emergency than to it's normal state:
EXEC sp_resetstatus 'DBname'
ALTER DATABASE DBname SET EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBname SET MULTI_USER
I hope that will help you to get your database working
Tuesday, September 04, 2007
FTP Put file using t-sql
I have to use a stored procedure which will send/Receive text file
from/to a server by using FTP.
So i googled on the net and i found a very interesting stored procedure written by Nigel Rivett
that will upload a file from the database.
But before using this stored procedure on SQL Server 2005,XP_CMDSHELL must be enabled,
because it's disabled by default on SQL Server 2005 for security reasons.
There are two ways to enable this option: Surface Area Configuration tool or sp_configure.
Enabling this option via Surface Area Configuration tool is rather straight forward.
Here is the instructions to enable xp_cmdshell in SQL Server 2005 via Surface Area Configuration tool:
1. Click the Start button.
2. Select All Programs.
3. Navigate to the Microsoft SQL Server 2005 folder.
4. On the flyout, mouseover Configuration Tools.
5. Select SQL Server Surface Area Configuration.
6. The SQL Server 2005 Surface Area Configuration window will appear.
7. At the bottom of the window, select Surface Area Configuration for Features.
8. In the left pane, under Database Engine, select xp_cmdshell.
9. Check the Enable xp_cmdshell checkbox.
10. Click OK.
11. Close the SQL Server 2005 Surface Area Configuration window.
Here is the script to enable xp_cmdshell in SQL Server 2005 via sp_configure:
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
-- FTP Put file using t-sql.
-- Author Nigel Rivett
This needs a work directory to create a FTP command file to execute.
In a multi-user system include the spid in the filename to make it unique for the connection.
if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ftp_PutFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ftp_PutFile]
GO
Create procedure s_ftp_putfile
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128)
as
declare @cmd varchar(1000)
declare @workfilename varchar(128)
/* usage:
exec s_ftp_putfile
@FTPServer = 'ftpserver' ,
@FTPUser = 'user' ,
@FTPPWD = 'password' ,
@FTPPath = 'path/' ,
@FTPFileName = 'test.txt' ,
@SourcePath = 'c:\' ,
@SourceFile = 'test.txt' ,
@workdir = 'c:\temp\'
*/
select @workfilename = 'ftpcmd.txt'
-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '', '^'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '', '^'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '', '^'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '', '^'),'<','^<'),'>','^>')
select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'ftp -s:' + @workdir + @workfilename
create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd
select id, ouputtmp = s from #a
go
from/to a server by using FTP.
So i googled on the net and i found a very interesting stored procedure written by Nigel Rivett
that will upload a file from the database.
But before using this stored procedure on SQL Server 2005,XP_CMDSHELL must be enabled,
because it's disabled by default on SQL Server 2005 for security reasons.
There are two ways to enable this option: Surface Area Configuration tool or sp_configure.
Enabling this option via Surface Area Configuration tool is rather straight forward.
Here is the instructions to enable xp_cmdshell in SQL Server 2005 via Surface Area Configuration tool:
1. Click the Start button.
2. Select All Programs.
3. Navigate to the Microsoft SQL Server 2005 folder.
4. On the flyout, mouseover Configuration Tools.
5. Select SQL Server Surface Area Configuration.
6. The SQL Server 2005 Surface Area Configuration window will appear.
7. At the bottom of the window, select Surface Area Configuration for Features.
8. In the left pane, under Database Engine, select xp_cmdshell.
9. Check the Enable xp_cmdshell checkbox.
10. Click OK.
11. Close the SQL Server 2005 Surface Area Configuration window.
Here is the script to enable xp_cmdshell in SQL Server 2005 via sp_configure:
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
-- FTP Put file using t-sql.
-- Author Nigel Rivett
This needs a work directory to create a FTP command file to execute.
In a multi-user system include the spid in the filename to make it unique for the connection.
if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ftp_PutFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ftp_PutFile]
GO
Create procedure s_ftp_putfile
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128)
as
declare @cmd varchar(1000)
declare @workfilename varchar(128)
/* usage:
exec s_ftp_putfile
@FTPServer = 'ftpserver' ,
@FTPUser = 'user' ,
@FTPPWD = 'password' ,
@FTPPath = 'path/' ,
@FTPFileName = 'test.txt' ,
@SourcePath = 'c:\' ,
@SourceFile = 'test.txt' ,
@workdir = 'c:\temp\'
*/
select @workfilename = 'ftpcmd.txt'
-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '', '^'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '', '^'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '', '^'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '', '^'),'<','^<'),'>','^>')
select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'ftp -s:' + @workdir + @workfilename
create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd
select id, ouputtmp = s from #a
go
Thursday, August 02, 2007
Converting the database mirroring from High Availability to High Performance
In the previous article i showed you how to configure database mirroring using the high availability configuration through a SQLCMD master script.
What if we want to change the configuration from high availability mode to high performance mode,we will need to clean the configuration and reconfigure it again?
The answer is no,we can convert the configuration.
All what we need to do is to set both the witness and the safety off
-- ConvertToHighPerf.sql
/*========================================================
File: ConvertToHighPerf.sql
Summary: Change the High Availability configuration
over to High Performance.
Turn off the witness, turn off safety!
Date: August 2007
==========================================================*/
:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
:SETVAR Database2Mirror AdventureWorksDW
go
:ON ERROR EXIT
go
:CONNECT $(PrincipalServer)
ALTER DATABASE $(Database2Mirror)
SET WITNESS OFF
go
ALTER DATABASE $(Database2Mirror)
SET SAFETY OFF
go
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status')
-- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go
:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go
What if we want to change the configuration from high availability mode to high performance mode,we will need to clean the configuration and reconfigure it again?
The answer is no,we can convert the configuration.
All what we need to do is to set both the witness and the safety off
-- ConvertToHighPerf.sql
/*========================================================
File: ConvertToHighPerf.sql
Summary: Change the High Availability configuration
over to High Performance.
Turn off the witness, turn off safety!
Date: August 2007
==========================================================*/
:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
:SETVAR Database2Mirror AdventureWorksDW
go
:ON ERROR EXIT
go
:CONNECT $(PrincipalServer)
ALTER DATABASE $(Database2Mirror)
SET WITNESS OFF
go
ALTER DATABASE $(Database2Mirror)
SET SAFETY OFF
go
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status')
-- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go
:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go
Wednesday, August 01, 2007
Configuring and Implementing Database Mirroring using SQLCMD
This article configures a database (uses AdventureWorksDW) for database mirroring – using the high availability configuration through a SQLCMD master script. SQLCMD replaces OSQL in SQL Server 2005 for better command-line automation by offering many new features including parameterization and ability to connect to different servers – all within one script. The true power can be seen by the fact that you can create complex multi-server scripts all within one master script. This script contains the steps necessary to setup mirroring through Transact-SQL. You will execute this script through the SQLCMD mode supported in SQL Server Management Studio. Please be sure to ONLY review the commands and their syntax, DO NOT EXECUTE any of the steps until the end – without the parameters set properly, this script will not execute correctly. This configuration is the High Availability configuration.
The below script (Database2Mirror.sql) is a SQLCMD script and by default, SQLCMD mode is not enabled. Enable SQLCMD command mode by choosing SQLCMD Mode from the Query menu (make sure to remember the icon shown in the drop-down menu since you could choose to use this instead). You can also toggle this mode off and on by using the SQLCMD mode icon in the toolbar. If you’re not sure of what an icon is, remember to use tool tips by hovering over an icon. Once you’ve enabled SQLCMD Mode, notice how your script changes. All SQLCMD lines are highlighted in gray.
-- Database2Mirror.sql
/*=============================================
File: Database2Mirror.sql
Summary: Setup the database for Mirroring - High Availability.
Date: August 2007
===============================================*/
:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
:SETVAR WitnessServer MSHEHADEHVPC\SQLExpress
:SETVAR Database2Mirror AdventureWorksDW
go
:ON ERROR EXIT
go
:CONNECT $(PrincipalServer)
-- Mirroring ONLY supports the FULL Recovery Model
ALTER DATABASE $(Database2Mirror)
SET RECOVERY FULL
go
USE $(Database2Mirror)
go
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5091)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO
:CONNECT $(MirrorServer)
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5092)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO
:CONNECT $(WitnessServer)
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5090)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO
:CONNECT $(PrincipalServer)
BACKUP DATABASE $(Database2Mirror)
TO DISK = 'C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak'
WITH INIT
GO
:CONNECT $(MirrorServer)
DECLARE @InstanceName sql_variant,
@InstanceDir sql_variant,
@SQLDataRoot nvarchar(512),
@ExecStr nvarchar(max)
SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')
EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
@InstanceName, @InstanceDir OUTPUT
SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread '
+ '''HKEY_LOCAL_MACHINE'', '
+ '''SOFTWARE\Microsoft\Microsoft SQL Server\'
+ convert(varchar, @InstanceDir)
+ '\Setup'', ''SQLDataRoot'', @SQLDataRoot OUTPUT'
EXEC master.dbo.sp_executesql @ExecStr
, N'@SQLDataRoot nvarchar(512) OUTPUT'
, @SQLDataRoot OUTPUT
IF @SQLDataRoot IS NULL
BEGIN
RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)
END
CREATE TABLE #BackupFileList
( LogicalName sysname NULL
, PhysicalName sysname NULL
, [Type] char(1)
, FileGroupName sysname NULL
, Size bigint
, MaxSize bigint
, FileId smallint
, CreateLSN numeric(25,0)
, DropLSN numeric(25,0)
, UniqueId uniqueidentifier
, ReadOnlyLSN numeric(25,0)
, ReadWriteLSN numeric(25,0)
, BackupSizeInBytes bigint
, SourceBlockSize bigint
, FileGroupId smallint
, LogGroupGUID uniqueidentifier
, DifferentialBaseLSN numeric(25,0)
, DifferentialBaseGUID uniqueidentifier
, IsReadOnly bit
, IsPresent bit
)
INSERT #BackupFileList
EXEC('LOAD FILELISTONLY FROM DISK = ''C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak''')
UPDATE #BackupFileList
SET PhysicalName
= @SQLDataRoot
+ N'\Data\'
+ REVERSE(SUBSTRING(REVERSE(PhysicalName)
, 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))
DECLARE @LogicalName sysname
, @PhysicalName sysname
DECLARE FileListCursor CURSOR FAST_FORWARD FOR
SELECT LogicalName, PhysicalName
FROM #BackupFileList
OPEN FileListCursor
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
SELECT @ExecStr = N'RESTORE DATABASE $(Database2Mirror)' +
N' FROM DISK = ''C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak''' +
N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName
+ ''' TO ''' + @PhysicalName + ''''
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END
-- NORECOVERY is required for Database Mirroring, replace is not.
-- Replace is used here solely to allow repetitive use of this script.
SELECT @ExecStr = @ExecStr + N' , NORECOVERY, REPLACE'
-- Useful for testing
-- Only return the string and then comment out the EXEC line below.
-- SELECT @ExecStr
EXEC (@ExecStr)
DEALLOCATE FileListCursor
GO
:CONNECT $(PrincipalServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go
:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go
:CONNECT $(MirrorServer)
ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://MSHEHADEHVPC:5091'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'
GO
:CONNECT $(PrincipalServer)
ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://MSHEHADEHVPC:5092'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'
GO
ALTER DATABASE $(Database2Mirror)
SET WITNESS = 'TCP://MSHEHADEHVPC:5090'
-- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'
GO
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
--------------------------------------------------------------------
-- CLEAN UP SECTION
--------------------------------------------------------------------
-- This section will clean the database mirroring configuration
---- Begin cleanup...
-- If you want to clean the database mirroring configuration
-- just uncomment the following section
--:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
--:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
--:SETVAR WitnessServer MSHEHADEHVPC\SQLExpress
--:SETVAR Database2Mirror AdventureWorksDW
--go
--
--:CONNECT $(PrincipalServer)
--
--USE master
--GO
--
--SELECT * FROM sys.database_mirroring_endpoints
--SELECT * FROM sys.endpoints
--SELECT * FROM sys.tcp_endpoints
--GO
--
--ALTER DATABASE $(Database2Mirror)
-- SET PARTNER OFF
--GO
--
--DROP ENDPOINT Mirroring
--go
--
--DROP DATABASE $(Database2Mirror)
--go
--
--:CONNECT $(MirrorServer)
--
--DROP ENDPOINT Mirroring
--GO
--
--ALTER DATABASE $(Database2Mirror)
-- SET PARTNER OFF
--GO
--DROP DATABASE $(Database2Mirror)
--go
--
--:CONNECT $(WitnessServer)
--
--DROP ENDPOINT Mirroring
--go
--
------ End cleanup...
The below script (Database2Mirror.sql) is a SQLCMD script and by default, SQLCMD mode is not enabled. Enable SQLCMD command mode by choosing SQLCMD Mode from the Query menu (make sure to remember the icon shown in the drop-down menu since you could choose to use this instead). You can also toggle this mode off and on by using the SQLCMD mode icon in the toolbar. If you’re not sure of what an icon is, remember to use tool tips by hovering over an icon. Once you’ve enabled SQLCMD Mode, notice how your script changes. All SQLCMD lines are highlighted in gray.
-- Database2Mirror.sql
/*=============================================
File: Database2Mirror.sql
Summary: Setup the database for Mirroring - High Availability.
Date: August 2007
===============================================*/
:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
:SETVAR WitnessServer MSHEHADEHVPC\SQLExpress
:SETVAR Database2Mirror AdventureWorksDW
go
:ON ERROR EXIT
go
:CONNECT $(PrincipalServer)
-- Mirroring ONLY supports the FULL Recovery Model
ALTER DATABASE $(Database2Mirror)
SET RECOVERY FULL
go
USE $(Database2Mirror)
go
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5091)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO
:CONNECT $(MirrorServer)
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5092)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO
:CONNECT $(WitnessServer)
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5090)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO
:CONNECT $(PrincipalServer)
BACKUP DATABASE $(Database2Mirror)
TO DISK = 'C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak'
WITH INIT
GO
:CONNECT $(MirrorServer)
DECLARE @InstanceName sql_variant,
@InstanceDir sql_variant,
@SQLDataRoot nvarchar(512),
@ExecStr nvarchar(max)
SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')
EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
@InstanceName, @InstanceDir OUTPUT
SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread '
+ '''HKEY_LOCAL_MACHINE'', '
+ '''SOFTWARE\Microsoft\Microsoft SQL Server\'
+ convert(varchar, @InstanceDir)
+ '\Setup'', ''SQLDataRoot'', @SQLDataRoot OUTPUT'
EXEC master.dbo.sp_executesql @ExecStr
, N'@SQLDataRoot nvarchar(512) OUTPUT'
, @SQLDataRoot OUTPUT
IF @SQLDataRoot IS NULL
BEGIN
RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)
END
CREATE TABLE #BackupFileList
( LogicalName sysname NULL
, PhysicalName sysname NULL
, [Type] char(1)
, FileGroupName sysname NULL
, Size bigint
, MaxSize bigint
, FileId smallint
, CreateLSN numeric(25,0)
, DropLSN numeric(25,0)
, UniqueId uniqueidentifier
, ReadOnlyLSN numeric(25,0)
, ReadWriteLSN numeric(25,0)
, BackupSizeInBytes bigint
, SourceBlockSize bigint
, FileGroupId smallint
, LogGroupGUID uniqueidentifier
, DifferentialBaseLSN numeric(25,0)
, DifferentialBaseGUID uniqueidentifier
, IsReadOnly bit
, IsPresent bit
)
INSERT #BackupFileList
EXEC('LOAD FILELISTONLY FROM DISK = ''C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak''')
UPDATE #BackupFileList
SET PhysicalName
= @SQLDataRoot
+ N'\Data\'
+ REVERSE(SUBSTRING(REVERSE(PhysicalName)
, 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))
DECLARE @LogicalName sysname
, @PhysicalName sysname
DECLARE FileListCursor CURSOR FAST_FORWARD FOR
SELECT LogicalName, PhysicalName
FROM #BackupFileList
OPEN FileListCursor
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
SELECT @ExecStr = N'RESTORE DATABASE $(Database2Mirror)' +
N' FROM DISK = ''C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak''' +
N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName
+ ''' TO ''' + @PhysicalName + ''''
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END
-- NORECOVERY is required for Database Mirroring, replace is not.
-- Replace is used here solely to allow repetitive use of this script.
SELECT @ExecStr = @ExecStr + N' , NORECOVERY, REPLACE'
-- Useful for testing
-- Only return the string and then comment out the EXEC line below.
-- SELECT @ExecStr
EXEC (@ExecStr)
DEALLOCATE FileListCursor
GO
:CONNECT $(PrincipalServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go
:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go
:CONNECT $(MirrorServer)
ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://MSHEHADEHVPC:5091'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'
GO
:CONNECT $(PrincipalServer)
ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://MSHEHADEHVPC:5092'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'
GO
ALTER DATABASE $(Database2Mirror)
SET WITNESS = 'TCP://MSHEHADEHVPC:5090'
-- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'
GO
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
--------------------------------------------------------------------
-- CLEAN UP SECTION
--------------------------------------------------------------------
-- This section will clean the database mirroring configuration
---- Begin cleanup...
-- If you want to clean the database mirroring configuration
-- just uncomment the following section
--:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
--:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
--:SETVAR WitnessServer MSHEHADEHVPC\SQLExpress
--:SETVAR Database2Mirror AdventureWorksDW
--go
--
--:CONNECT $(PrincipalServer)
--
--USE master
--GO
--
--SELECT * FROM sys.database_mirroring_endpoints
--SELECT * FROM sys.endpoints
--SELECT * FROM sys.tcp_endpoints
--GO
--
--ALTER DATABASE $(Database2Mirror)
-- SET PARTNER OFF
--GO
--
--DROP ENDPOINT Mirroring
--go
--
--DROP DATABASE $(Database2Mirror)
--go
--
--:CONNECT $(MirrorServer)
--
--DROP ENDPOINT Mirroring
--GO
--
--ALTER DATABASE $(Database2Mirror)
-- SET PARTNER OFF
--GO
--DROP DATABASE $(Database2Mirror)
--go
--
--:CONNECT $(WitnessServer)
--
--DROP ENDPOINT Mirroring
--go
--
------ End cleanup...
Tuesday, September 05, 2006
SQLCMD VS OSQL
In SQL Server 2005, Microsoft introduced a new command line utility, SQLCMD, as a replacement for osql and isql.
In this article you will find some advantage you will gain when you use SQLCMD instead of OSQL.
Migration from OSQL to SQLCMD will be very simple because most of the command line switches are similar,the only difference is that SQLCMD support additional switches.To see the difference between then type OSQL /? and SQLCMD /? at the command prompt.
1) SQLCMD supports parameterized variables
SQLCMD can be useful for running some SQL scripts which require you to pass different parameters specific to a certain environment.Environment variables and declared variables can be used in various places in the script The following is an example:
-- c:\sqlvariable.sql
DECLARE @myVar varchar(255)
SET @myVar = '$(myVar)'
SET @myVar = @myVariable + ' ' + suser_sname()
PRINT @myVar
You can run it from a command prompt:
SQLCMD -E -i"c:\sqlvariable.sql" -v myVar="You are connected as "
Result:
You are connected as ABOUHMEID02\Administrator
2) !!
This command allows you to execute an Operating System Command on the client without having to connect and use xp_cmdshell. For example while connected to SQLCMD type !!dir
3) Management Studio's query editor in the SQLCMD mode
SQLCMD can be run both in the DOS screen as well as in the MS SQL Server 2005 Management Studio's query editor in the SQLCMD mode. If you want to use scripting, which brings in features like color coding, parsing and executing scripts, and others, this must be enabled. Enabling SQLCMD allows you to write SQL Commands as well as T-SQL Statements. You can set this in Tools Options or click the button with the Red
4) Performance
It allows you to connect to any instance of SQL Server via OLE DB (rather than via older technologies such as DB-library and ODBC, used by OSQL and ISQL, respectively) which is a much better performer
5) Remote Dedicated Admin Connection (DAC)
If you use the -A switch, you can get into a server that might otherwise be hung. You can kill an offending process and restore order to the universe - without a reboot!
6) SQLCMD support Customized editor
In SQLCMD, if you type ed, it will invoke a text editor and put the last command you run in the editor buffer. The default editor is Edit.com You can change the default editor to your own preferred editor,to do this: Open a DOS prompt; Type set sqlcmdeditor=notepad Go to sqlcmd, enter and execute a sql statement, then type ed; The notepad opens.Do the modifcations you want to do, save and close.
7) SQLCMDINI
This allows you to set default parameters and even create a default query to execute on the server as soon as you connect. This helps you to automate your connection properties and to some degree your environment in command line mode. For example: Create a script file named :
-- c:\Init.SQL
-- Begin script
set nocount on go
print 'You are connected to ' + rtrim(CONVERT(char(20), SERVERPROPERTY('servername'))) + ' (' + rtrim(CONVERT(char(20), SERVERPROPERTY('productversion'))) + ')' + ' '
+ rtrim(CONVERT(char(30), SERVERPROPERTY('Edition'))) + ' '
+ rtrim(CONVERT(char(20), SERVERPROPERTY('ProductLevel')))
+ char(10)
set nocount off go
--End script
In Command prompt, type: set sqlcmdini=c:\Init.SQL Here are some sample results: C:\DOCUME~1\ADMINI~1>sqlcmd You are connected to ABOUHMEID02 (8.00.760) Developer Edition SP3 1>
8) :XML [ONOFF]
Specifies if XML output resulting from FOR XML clause will be output as a continuous stream.
9) :Connect server_name[ \instance_name][timeout] [ user_name[ password]]
From within a single script, you can connect to multiple SQL Server instances and execute commands. This allows for a great deal of automation possibilities, such as backup from one server, restore to another server. Also, you could execute a configuration change script on all servers in production.
10) :ErrorSTDERRSTDOUT
There's pretty much all you need in some combination of these to gracefully deal with run-time problems. That is so much better than piping out and digging through log files! It Redirects all error output to the file specified by filename, to stderr or to stdout. The Error option can appear multiple times in a script. Error output is sent to stderr by default.
11) :PerftraceSTDERRSTDOUT
Redirect all performance trace information to the file specified by filename, to stderr or to stdout. Performance trace output is sent to stdout by default. It lets you put your statistics (IO, TIME, ShowPlan) out to a separate file for later viewing as so not to clutter the results file output.
In this article you will find some advantage you will gain when you use SQLCMD instead of OSQL.
Migration from OSQL to SQLCMD will be very simple because most of the command line switches are similar,the only difference is that SQLCMD support additional switches.To see the difference between then type OSQL /? and SQLCMD /? at the command prompt.
1) SQLCMD supports parameterized variables
SQLCMD can be useful for running some SQL scripts which require you to pass different parameters specific to a certain environment.Environment variables and declared variables can be used in various places in the script The following is an example:
-- c:\sqlvariable.sql
DECLARE @myVar varchar(255)
SET @myVar = '$(myVar)'
SET @myVar = @myVariable + ' ' + suser_sname()
PRINT @myVar
You can run it from a command prompt:
SQLCMD -E -i"c:\sqlvariable.sql" -v myVar="You are connected as "
Result:
You are connected as ABOUHMEID02\Administrator
2) !!
This command allows you to execute an Operating System Command on the client without having to connect and use xp_cmdshell. For example while connected to SQLCMD type !!dir
3) Management Studio's query editor in the SQLCMD mode
SQLCMD can be run both in the DOS screen as well as in the MS SQL Server 2005 Management Studio's query editor in the SQLCMD mode. If you want to use scripting, which brings in features like color coding, parsing and executing scripts, and others, this must be enabled. Enabling SQLCMD allows you to write SQL Commands as well as T-SQL Statements. You can set this in Tools Options or click the button with the Red
4) Performance
It allows you to connect to any instance of SQL Server via OLE DB (rather than via older technologies such as DB-library and ODBC, used by OSQL and ISQL, respectively) which is a much better performer
5) Remote Dedicated Admin Connection (DAC)
If you use the -A switch, you can get into a server that might otherwise be hung. You can kill an offending process and restore order to the universe - without a reboot!
6) SQLCMD support Customized editor
In SQLCMD, if you type ed, it will invoke a text editor and put the last command you run in the editor buffer. The default editor is Edit.com You can change the default editor to your own preferred editor,to do this: Open a DOS prompt; Type set sqlcmdeditor=notepad Go to sqlcmd, enter and execute a sql statement, then type ed; The notepad opens.Do the modifcations you want to do, save and close.
7) SQLCMDINI
This allows you to set default parameters and even create a default query to execute on the server as soon as you connect. This helps you to automate your connection properties and to some degree your environment in command line mode. For example: Create a script file named :
-- c:\Init.SQL
-- Begin script
set nocount on go
print 'You are connected to ' + rtrim(CONVERT(char(20), SERVERPROPERTY('servername'))) + ' (' + rtrim(CONVERT(char(20), SERVERPROPERTY('productversion'))) + ')' + ' '
+ rtrim(CONVERT(char(30), SERVERPROPERTY('Edition'))) + ' '
+ rtrim(CONVERT(char(20), SERVERPROPERTY('ProductLevel')))
+ char(10)
set nocount off go
--End script
In Command prompt, type: set sqlcmdini=c:\Init.SQL Here are some sample results: C:\DOCUME~1\ADMINI~1>sqlcmd You are connected to ABOUHMEID02 (8.00.760) Developer Edition SP3 1>
8) :XML [ONOFF]
Specifies if XML output resulting from FOR XML clause will be output as a continuous stream.
9) :Connect server_name[ \instance_name][timeout] [ user_name[ password]]
From within a single script, you can connect to multiple SQL Server instances and execute commands. This allows for a great deal of automation possibilities, such as backup from one server, restore to another server. Also, you could execute a configuration change script on all servers in production.
10) :Error
There's pretty much all you need in some combination of these to gracefully deal with run-time problems. That is so much better than piping out and digging through log files! It Redirects all error output to the file specified by filename, to stderr or to stdout. The Error option can appear multiple times in a script. Error output is sent to stderr by default.
11) :Perftrace
Redirect all performance trace information to the file specified by filename, to stderr or to stdout. Performance trace output is sent to stdout by default. It lets you put your statistics (IO, TIME, ShowPlan) out to a separate file for later viewing as so not to clutter the results file output.
How to create and use partition in SQL SERVER 2005:
Basically partitions help in improving the performance, scalability and managing of the large
tables. As the table grows larger and larger the performance in accessing the data is affected, scalability and managing issues arises. With
the help of partitioning a table we can achieve a great level of performance and managing of tables.
Let us see in how we can create partitioned tables in detail. The procedure for creating a partitioned table is as follows.
1) Creation of Filegroups
Beginning with this we have to have various filegroups for the database if we need to place the partitioned tables on different filegroups. To
create a filegroup, there are different ways one is using the Alter command and the second is using the Interface. Let us see each of them
a) Using the Alter Command
The syntax is as follows
ALTER DATABASE Database Name ADD FILEGROUP Filegroup name
After adding a filegroup we need to add files to the filegroup. We can add one or more files. The syntax is as follows
ALTER DATABASE Database Name
ADD FILE
(
NAME = File Name,
FILENAME = Path,
SIZE = Size,
MAXSIZE = Size,
FILEGROWTH = Size
)
TO FILEGROUP Filegroup Name;
b) Using the Interface
The following steps show how we can create using the interface
a) Login to the Microsoft SQL SERVER Management Studio
b) Select the Database and right click and select properties.
c) Select the Filegroup section and add the necessary details and click the add button.
d) Next we will click on the Files section and add a new file and associate this file to the FG2 filegroup .
e) Then finally click on the OK button.
This is procedure for create a filegroup using the interface.
2) Creation of Partition Function
The partition function is created for setting the range partitions. The ranges can be set for a lower or upper threshold. The syntax is as follows
CREATE PARTITION FUNCTION Function Name (Data Type)
AS
RANGE LEFT/RIGHT FOR VALUES (value1,value2,…)
3) Creation of Partition Scheme
The next step is to create the partition scheme after the partition function is created. This is needed for associating the partitions to a specific
filegroups.
The syntax is as follows
CREATE PARTITION SCHEME Partition Scheme Name
AS
PARTITION Partition Function Name
TO (Filegroup1,Filegroup2,….)
4) Creation of Partition Table
The next step is to create the partitioned table which would be associated with the defined partition scheme.
The syntax is as follows
CREATE TABLE Table Name
(
Column Name1 Datatype,
Column Name2 Datatype,
……..
)
ON Partition Scheme Name ( Column Name)
The above four steps clearly states how to create a partitioned table.
Querying a Partitioned Table
After the creation of a partitioned table, now let us see how to query the data from the partitioned tables.
Let us see the various cases in querying
a) Querying the data from a particular partition
Syntax
SELECT Column Name1…/* FROM Table Name WHERE $PARTITION.Partition Function Name(Column Name) =
Partition Number
The Partition number refers to first partition range or second partition range and so on, The first partition range is referred as 1 , second as 2
and so on.
b) Querying for Knowing the Partition Number
Syntax
SELECT $PARTITION.Partition Function Name(Column Name) = Partition Range Value
c) Querying to find the count of records in each partition
Syntax
SELECT $PARTITION.Partition Function Name(Column Name), COUNT(*) FROM Table Name GROUP BY $PARTITION.
Partition Function Name(Column Name)
SPLITTING OF PARTITION
The partitions can be split by splitting the partition ranges. The splitting is done by using the alter partition command. We have to note that
before we split the partition there should be a additional filegroup already associated in the partition scheme. If there is no unused filegroup
available then we cannot split. So before splitting we have to ensure that a filegroup is added to the partition scheme. This is as shown below.
SYNTAX
ALTER PARTITION FUNCTION Partition Function Name () SPLIT RANGE (PARITITION RANGE VALUE)
MERGING OF PARTITION
The partitions can be merged by merging the partition ranges. The partition range value mentioned will merge that to the next greater
partition range value into a singe partition. This is as shown below.
SYNTAX
ALTER PARTITION FUNCTION Partition Function Name () MERGE RANGE (PARITITION RANGE VALUE)
ALTERING PARTITION SCHEME
The partition scheme is altered to add new filegroup which may be required when partitions are splitted.
SYNTAX
ALTER PARTITION SCHEME Partition Scheme Name NEXT USED Filegroup Name
DROPPING OF PARTITION
Let us see now how to drop partitions .
a) Drop a Partition Function
To drop a partition function it should not have any partition scheme associated with it.
Syntax
DROP PARTITION FUNCTION Partition Function Name
b) Drop a Partition Scheme
To drop a partition scheme it should not have any table associated with it.
Syntax
DROP PARTITION SCHEME Partition Scheme Name
Partition Function Information
The Partition Function Information is obtained from the sys.partition_functions table as shown below
SELECT * FROM SYS.PARTITION_FUNCTIONS
Partition Range Information
The Partition Range information is obtained from the sys.partition_range_values as shown below
SELECT * FROM SYS.PARTITION_RANGE_VALUES
Partition Scheme Information
The Partition Scheme information is obtained from the sys.partition_scheme as shown below
SELECT * FROM SYS.PARTITION_SCHEME
Partitions Information
The Partition information can be obtained from the sys.partitions table as shown below.
SELECT * FROM SYS.PARTITIONS
Click here to download an example : partition.sql
tables. As the table grows larger and larger the performance in accessing the data is affected, scalability and managing issues arises. With
the help of partitioning a table we can achieve a great level of performance and managing of tables.
Let us see in how we can create partitioned tables in detail. The procedure for creating a partitioned table is as follows.
1) Creation of Filegroups
Beginning with this we have to have various filegroups for the database if we need to place the partitioned tables on different filegroups. To
create a filegroup, there are different ways one is using the Alter command and the second is using the Interface. Let us see each of them
a) Using the Alter Command
The syntax is as follows
ALTER DATABASE Database Name ADD FILEGROUP Filegroup name
After adding a filegroup we need to add files to the filegroup. We can add one or more files. The syntax is as follows
ALTER DATABASE Database Name
ADD FILE
(
NAME = File Name,
FILENAME = Path,
SIZE = Size,
MAXSIZE = Size,
FILEGROWTH = Size
)
TO FILEGROUP Filegroup Name;
b) Using the Interface
The following steps show how we can create using the interface
a) Login to the Microsoft SQL SERVER Management Studio
b) Select the Database and right click and select properties.
c) Select the Filegroup section and add the necessary details and click the add button.
d) Next we will click on the Files section and add a new file and associate this file to the FG2 filegroup .
e) Then finally click on the OK button.
This is procedure for create a filegroup using the interface.
2) Creation of Partition Function
The partition function is created for setting the range partitions. The ranges can be set for a lower or upper threshold. The syntax is as follows
CREATE PARTITION FUNCTION Function Name (Data Type)
AS
RANGE LEFT/RIGHT FOR VALUES (value1,value2,…)
3) Creation of Partition Scheme
The next step is to create the partition scheme after the partition function is created. This is needed for associating the partitions to a specific
filegroups.
The syntax is as follows
CREATE PARTITION SCHEME Partition Scheme Name
AS
PARTITION Partition Function Name
TO (Filegroup1,Filegroup2,….)
4) Creation of Partition Table
The next step is to create the partitioned table which would be associated with the defined partition scheme.
The syntax is as follows
CREATE TABLE Table Name
(
Column Name1 Datatype,
Column Name2 Datatype,
……..
)
ON Partition Scheme Name ( Column Name)
The above four steps clearly states how to create a partitioned table.
Querying a Partitioned Table
After the creation of a partitioned table, now let us see how to query the data from the partitioned tables.
Let us see the various cases in querying
a) Querying the data from a particular partition
Syntax
SELECT Column Name1…/* FROM Table Name WHERE $PARTITION.Partition Function Name(Column Name) =
Partition Number
The Partition number refers to first partition range or second partition range and so on, The first partition range is referred as 1 , second as 2
and so on.
b) Querying for Knowing the Partition Number
Syntax
SELECT $PARTITION.Partition Function Name(Column Name) = Partition Range Value
c) Querying to find the count of records in each partition
Syntax
SELECT $PARTITION.Partition Function Name(Column Name), COUNT(*) FROM Table Name GROUP BY $PARTITION.
Partition Function Name(Column Name)
SPLITTING OF PARTITION
The partitions can be split by splitting the partition ranges. The splitting is done by using the alter partition command. We have to note that
before we split the partition there should be a additional filegroup already associated in the partition scheme. If there is no unused filegroup
available then we cannot split. So before splitting we have to ensure that a filegroup is added to the partition scheme. This is as shown below.
SYNTAX
ALTER PARTITION FUNCTION Partition Function Name () SPLIT RANGE (PARITITION RANGE VALUE)
MERGING OF PARTITION
The partitions can be merged by merging the partition ranges. The partition range value mentioned will merge that to the next greater
partition range value into a singe partition. This is as shown below.
SYNTAX
ALTER PARTITION FUNCTION Partition Function Name () MERGE RANGE (PARITITION RANGE VALUE)
ALTERING PARTITION SCHEME
The partition scheme is altered to add new filegroup which may be required when partitions are splitted.
SYNTAX
ALTER PARTITION SCHEME Partition Scheme Name NEXT USED Filegroup Name
DROPPING OF PARTITION
Let us see now how to drop partitions .
a) Drop a Partition Function
To drop a partition function it should not have any partition scheme associated with it.
Syntax
DROP PARTITION FUNCTION Partition Function Name
b) Drop a Partition Scheme
To drop a partition scheme it should not have any table associated with it.
Syntax
DROP PARTITION SCHEME Partition Scheme Name
Partition Function Information
The Partition Function Information is obtained from the sys.partition_functions table as shown below
SELECT * FROM SYS.PARTITION_FUNCTIONS
Partition Range Information
The Partition Range information is obtained from the sys.partition_range_values as shown below
SELECT * FROM SYS.PARTITION_RANGE_VALUES
Partition Scheme Information
The Partition Scheme information is obtained from the sys.partition_scheme as shown below
SELECT * FROM SYS.PARTITION_SCHEME
Partitions Information
The Partition information can be obtained from the sys.partitions table as shown below.
SELECT * FROM SYS.PARTITIONS
Click here to download an example : partition.sql
Monday, July 10, 2006
SQL Server 2005 TableDiff utility
While testing the Replication in SQL 2005, I found one of the command line utilities that came with SQL Server 2005 named TableDiff. It's is primarily designed for comparing replicated tables, but we can take advantage of its functionality to compare tables in the same database or in different databases. The objective of this article is to demonstrate how we can take advantage of its functionality to compare tables.
Let us assume that we have a table named TEST1 in the database TESTDB1,and a table named TEST2 in the database TESTDB2,the both tables TEST1 and TEST2 having the same structure,we will use the tablediff utility to generate script that will make the data in TEST2 exactly the same as TEST1,we can do the reverse by switching the source and the destination options parameter of Tablediff utility
N.B: The unique key is important for the data comparison
set quoted_identifier off
go
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases
WHERE name = N'TESTDB1')
DROP DATABASE [TESTDB1]
go
create database TESTDB1
go
use TESTDB1
go
create table TEST1 (id int primary key,
name varchar(100),
sal money)
go
ALTER TABLE TEST1 ADD UNIQUE
( [ID] ASC )
go
insert into TEST1 select 1,"Name1",1000
insert into TEST1 select 2,"Name2",2000
insert into TEST1 select 3,"Name3",3000
insert into TEST1 select 4,"Name4",4000
go
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases
WHERE name = N'TESTDB2')
DROP DATABASE [TESTDB2]
go
Create database TESTDB2
go
use TESTDB2
go
create table TEST2 (id int primary key,
name varchar(100),
sal money)
go
ALTER TABLE TEST2 ADD UNIQUE
( [ID] ASC )
go
insert into TEST2 select 1,"Name1",1000
insert into TEST2 select 2,"Name2",2000
insert into TEST2 select 4,"Name4",5000
insert into TEST2 select 9,"Name9",9000
go
From to the command line type the following commands:
Type cd Type cd C:\Program Files\Microsoft SQL Server\90\COM
Than when the command prompt changed to
C:\Program Files\Microsoft SQL Server\90\COM>
type
tablediff -sourceserver "ABOUHMEID02\SQLEXPRESS" -sourcedatabase "TESTDB1" -sourcetable "TEST1" -sourceuser "sa" -sourcepassword "sa" -destinationserver "ABOUHMEID02\SQLEXPRESS" -destinationdatabase "TESTDB2" -destinationtable "TEST2" -destinationuser "sa" -destinationpassword "sa" -f "c:\compare.sql"
N.B: If you installed SQL SERVER 2005 in a directory different than
C:\ Program Files\Microsoft SQL Server\90\COM folder
Change the path depending on your installation configuration.
The TableDiff utility is installed by default under
C:\Program Files\Microsoft SQL Server\90\COM folder
Result
User-specified agent parameter values:
-sourceserver ABOUHMEID02\SQLEXPRESS
-sourcedatabase TESTDB1
-sourcetable TEST1
-sourceuser sa
-sourcepassword sa
-destinationserver ABOUHMEID02\SQLEXPRESS
-destinationdatabase TESTDB2
-destinationtable TEST2
-destinationuser sa
-destinationpassword sa
-f c:\compare.sql
Table [TESTDB1].[dbo].[TEST1] on ABOUHMEID02\SQLEXPRESS and Table [TESTDB2].[dbo].[TEST2] on ABOUHMEID02\SQLEXPRESS have 3 differences.
Fix SQL written to c:\compare.sql.
Err id Col
Src. Only 3
Mismatch 4 sal
Dest. Only 9
The requested operation took 0.40625 seconds.
The generated sql file C:\Compare.SQL file :
-- Host: ABOUHMEID02\SQLEXPRESS
-- Database: [TESTDB2]
-- Table: [dbo].[TEST2]
INSERT INTO [dbo].[TEST2] ([id],[name],[sal]) VALUES (3,'Name3',3000.0000)
UPDATE [dbo].[TEST2] SET [sal]=4000.0000 WHERE [id] = 4
DELETE FROM [dbo].[TEST2] WHERE [id] = 9
Let us assume that we have a table named TEST1 in the database TESTDB1,and a table named TEST2 in the database TESTDB2,the both tables TEST1 and TEST2 having the same structure,we will use the tablediff utility to generate script that will make the data in TEST2 exactly the same as TEST1,we can do the reverse by switching the source and the destination options parameter of Tablediff utility
N.B: The unique key is important for the data comparison
set quoted_identifier off
go
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases
WHERE name = N'TESTDB1')
DROP DATABASE [TESTDB1]
go
create database TESTDB1
go
use TESTDB1
go
create table TEST1 (id int primary key,
name varchar(100),
sal money)
go
ALTER TABLE TEST1 ADD UNIQUE
( [ID] ASC )
go
insert into TEST1 select 1,"Name1",1000
insert into TEST1 select 2,"Name2",2000
insert into TEST1 select 3,"Name3",3000
insert into TEST1 select 4,"Name4",4000
go
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases
WHERE name = N'TESTDB2')
DROP DATABASE [TESTDB2]
go
Create database TESTDB2
go
use TESTDB2
go
create table TEST2 (id int primary key,
name varchar(100),
sal money)
go
ALTER TABLE TEST2 ADD UNIQUE
( [ID] ASC )
go
insert into TEST2 select 1,"Name1",1000
insert into TEST2 select 2,"Name2",2000
insert into TEST2 select 4,"Name4",5000
insert into TEST2 select 9,"Name9",9000
go
From to the command line type the following commands:
Type cd Type cd C:\Program Files\Microsoft SQL Server\90\COM
Than when the command prompt changed to
C:\Program Files\Microsoft SQL Server\90\COM>
type
tablediff -sourceserver "ABOUHMEID02\SQLEXPRESS" -sourcedatabase "TESTDB1" -sourcetable "TEST1" -sourceuser "sa" -sourcepassword "sa" -destinationserver "ABOUHMEID02\SQLEXPRESS" -destinationdatabase "TESTDB2" -destinationtable "TEST2" -destinationuser "sa" -destinationpassword "sa" -f "c:\compare.sql"
N.B: If you installed SQL SERVER 2005 in a directory different than
C:\ Program Files\Microsoft SQL Server\90\COM folder
Change the path depending on your installation configuration.
The TableDiff utility is installed by default under
C:\Program Files\Microsoft SQL Server\90\COM folder
Result
User-specified agent parameter values:
-sourceserver ABOUHMEID02\SQLEXPRESS
-sourcedatabase TESTDB1
-sourcetable TEST1
-sourceuser sa
-sourcepassword sa
-destinationserver ABOUHMEID02\SQLEXPRESS
-destinationdatabase TESTDB2
-destinationtable TEST2
-destinationuser sa
-destinationpassword sa
-f c:\compare.sql
Table [TESTDB1].[dbo].[TEST1] on ABOUHMEID02\SQLEXPRESS and Table [TESTDB2].[dbo].[TEST2] on ABOUHMEID02\SQLEXPRESS have 3 differences.
Fix SQL written to c:\compare.sql.
Err id Col
Src. Only 3
Mismatch 4 sal
Dest. Only 9
The requested operation took 0.40625 seconds.
The generated sql file C:\Compare.SQL file :
-- Host: ABOUHMEID02\SQLEXPRESS
-- Database: [TESTDB2]
-- Table: [dbo].[TEST2]
INSERT INTO [dbo].[TEST2] ([id],[name],[sal]) VALUES (3,'Name3',3000.0000)
UPDATE [dbo].[TEST2] SET [sal]=4000.0000 WHERE [id] = 4
DELETE FROM [dbo].[TEST2] WHERE [id] = 9
Monday, June 12, 2006
Importing a SDO file manually using VB script in MCMS 2002
When i tried to import a large SDO file (18 MB), from Site manager on a machine with Windows 2003 server,MCMS 2002 with Sp1a installed i got the following error:
Error: 0Description: The underlying connection was closed: An unexpected error occurred on a receive. Severity: 5Source: System Debug info: N/A Extra info: N/A Client Source: CImProgress::ProcessPackage Recommended Action: N/A
So i tried to import the SDO file using VB script instead of doing it from site manager and it works, below you will find a file named import.vbs
' VBScript for performing an import operation.
'usage: import.vbs filename.sdo
Dim strROPPath
Dim shell
Dim fso
strROPPath = WScript.Arguments(0)
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
If ( fso.FileExists(strROPPath) ) Then
Call Import(strROPPath)
Else
Call MsgBox(strROPPath, vbCritical, "File Does Not Exist")
End If
'**************************************
'* Routine that does the import.
'**************************************
Sub Import(strROPPath)
Dim pCmsDeployImport
On Error Resume Next
Set pCmsDeployImport = WScript.CreateObject("CmsDeployServer.CmsDeployImport.1")
If ( Err.Number <> 0 ) Then
Call MsgBox(Err.Description, vbCritical, "Import Problem")
Set pCmsDeployImport = Nothing
Exit Sub
End If
pCmsDeployImport.AuthenticateAsCurrentUser()
If ( Err.Number <> 0 ) Then
Call MsgBox(Err.Description, vbCritical, "Import Problem")
Set pCmsDeployImport = Nothing
Exit Sub
End If
Dim pImportOptions
Set pImportOptions = pCmsDeployImport.Options
If ( Err.Number <> 0 ) Then
Call MsgBox(Err.Description, vbCritical, "Import Problem")
Set pCmsDeployImport = Nothing
Set pImportOptions = Nothing
Exit Sub
End If
' Importing the grant rights
pImportOptions.IncludeRightsGroups = 3
pImportOptions.RightsOnAdd = 3
pImportOptions.RightsOnReplace = 2
pImportOptions.IncludeCreatedBy = 2
Dim strReportUrl
strReportUrl = pCmsDeployImport.Import(strROPPath)
If ( Err.Number <> 0 ) Then
Call MsgBox(Err.Description, vbCritical, "Import Problem")
Set pCmsDeployImport = Nothing
Set pImportOptions = Nothing
Exit Sub
End If
' Display the report in the Web browser.
Set shell = WScript.CreateObject("WScript.Shell")
shell.Run "http://localhost" & strReportUrl, 7
Call MsgBox( strReportUrl,vbInformation,"" )
Set shell = Nothing
Set pCmsDeployImport = Nothing
Set pImportOptions = Nothing
End Sub
Thursday, May 25, 2006
Killing all processes in the database 'DBNAME' except those connected throw OSQL in Sql Server
I have to restore a snapshot of a Sql server database daily used in a web site,to do this i must be in exclusive mode and the only user connected to the database, so i came up with this script to kill all processes connected to 'DBNAME' , except connected throw OSQL (my connection):
First of all let's create a procedure to kill db processes:
use master
go
CREATE proc kill_process(
@dname varchar(20))
as
Declare
@did int,
@sid int,
@s nvarchar(128)
select @did = dbid
from master..sysdatabases
where name = @dname
declare spid_cursor cursor for
select spid
from master..sysprocesses
where dbid = @did
and program_name <> 'OSQL-32'
open spid_cursorfetch next
from spid_cursor
into @s
While @@fetch_status = 0
Begin
Select @s = 'Kill '+convert(nvarchar(30),@sid)
exec(@s)
--print @s
fetch next
from spidcurs
into @sid
End
Deallocate spidcurs
GO
Now how to use this procedure:
USE MASTER
go
--Killing all processes in the database 'DBNAME' except those connected throw OSQL
exec proc kill_process 'DBNAME'
go
---set the database to single user mode
sp_dboption 'DBNAME', 'single user', true
now let's do the database restore,and don't forget to remove single user mode while finishing
sp_dboption 'DBNAME', 'single user', false
First of all let's create a procedure to kill db processes:
use master
go
CREATE proc kill_process(
@dname varchar(20))
as
Declare
@did int,
@sid int,
@s nvarchar(128)
select @did = dbid
from master..sysdatabases
where name = @dname
declare spid_cursor cursor for
select spid
from master..sysprocesses
where dbid = @did
and program_name <> 'OSQL-32'
open spid_cursorfetch next
from spid_cursor
into @s
While @@fetch_status = 0
Begin
Select @s = 'Kill '+convert(nvarchar(30),@sid)
exec(@s)
--print @s
fetch next
from spidcurs
into @sid
End
Deallocate spidcurs
GO
Now how to use this procedure:
USE MASTER
go
--Killing all processes in the database 'DBNAME' except those connected throw OSQL
exec proc kill_process 'DBNAME'
go
---set the database to single user mode
sp_dboption 'DBNAME', 'single user', true
now let's do the database restore,and don't forget to remove single user mode while finishing
sp_dboption 'DBNAME', 'single user', false
Wednesday, May 24, 2006
Microsoft SQL Server Migration Assistant for Oracle (SSMA)
I was working on converting an oracle database to SQL Server 2005 using SSMA ,so i remarked that if you have an oracle package and because there is no package in SQL Server,so using SSMA the package conversion is done using the following convention :Package functions will be converted to user-defined functions using PackageName_FunctionName naming convention.Package procedures will be converted to stored procedures using PackageName_ProcedureName naming convention.Package variables are emulated using a table and a set of functions.So you must change the function call in your application from PackageName.FunctionName to PackageName_FunctionName, and the procedure call from PackageName.ProcedureName to PackageName_ProcedureName.
Subscribe to:
Posts (Atom)
