Saturday, September 29, 2012

ps1 cannot be loaded because the execution of scripts is disabled on this system


If your powershell script failed test.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details. At line:1 char:19
1. First run the powershell command
Get-ExecutionPolicy
You will see Restricted; if then

2. Enable powershell to run the below
Set-ExecutionPolicy RemoteSigned

You will see RemoteSigned:

Your powershell  script will work now onwards

Thursday, September 20, 2012

SQL Server 2012 Express Editions


Microsoft also released a new set of SQL Server 2012 Express editions. SQL Server Express is a free edition of SQL Server, and it’s used as the internal database for hundreds of third-party products.
Although it's free, however, it also has several limitations. Compute-wise, the new SQL Server 2012 Express editions are limited to the lesser of one socket or four cores. The editions are also limited in size. The maximum memory for the database engine is limited to 1GB and the maximum relational database size is 10GB.
Although SQL Server Express is limited in scalability, a common misconception is that it’s limited to a single database or 10 connections. Neither of these is true. SQL Server Express can support multiple databases where each database can be up to 10GB. The myth about the number of connections is probably a holdover from the early days of MDSE (Microsoft Database Engine—the first version of the desktop SQL Server Engine that was released in the SQL Server 2000 timeframe.)
SQL Server 2012 Express has no limits on incoming connections or workload other than the CPU and memory limitations. And it comes in both 32-bit and 64-bit versions. You can download all of the SQL Server 2012 Express editions from Microsoft’s website.

Reference : http://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx

Wednesday, September 19, 2012

Microsoft Confirms October 25 Launch Event in NYC for Windows 8

While Windows 8 will be made generally available via software upgrades and new PC purchases starting October 26, Microsoft confirmed today that it will hold a launch event for the product on October 25 in New York City. This confirms reports that the firm would hold a “midnight madness” event the night before in order to celebrate the launch and let customers get their hands on Windows 8 as soon as possible.

My Windows Weekly co-host Mary Jo Foley wrote about the October 25 event in Mark your Microsoft calendars: Windows 8, Windows Phone 8 launch dates revealed last month. “October 25, the day before general availability, will be Microsoft's big launch event for Windows 8 and the Surface RT,” she wrote. “And that event will be in New York City, the site of most recent previous Windows launches. Windows 8 and the Surface RT ARM-based devices should be available (at least technically) at midnight, following the launch event.”

Partitioning existing table SQL2008R2

Create partioning for existing table with more than 1120962253 rows today. It was amazing to see the query performance.

Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partioned table
Also you can create Separate file group for Non-Clustered Index  on different drive NCINDEX_FG5

USE [TestDB]
GO
--Step 1. Creating a Partition Function
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);
--The result for this RANGE LEFT assignment is:
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}


--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION  PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])

--Step 3. CREATE CLUSTERED INDEX 1
-- Now create a Partitioned using clustered index based Scheme
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
      [M_ID] ASC
) ON SvrIdScheme(s_id)

-- Step 4.  CREATE NONCLUSTERED INDEX 2 on Separate file group  
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(  [t_id] ASC,  [s_id] ASC
)ON [NCINDEX_FG5] 
GO

--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
 GO

Tuesday, September 18, 2012

Creating a database with multiple file groups











USE [master]
GO

/****** Object:  Database [Testdb]    Script Date: 09/18/2012 16:52:59 ******/
CREATE DATABASE [Testdb] ON  PRIMARY
( NAME = N'Testdb', FILENAME = N'F:\SQLDBs\Testdb.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA]
( NAME = N'Test_Data', FILENAME = N'F:\SQLDBs\Test_Data.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA2]
( NAME = N'Test_Data2', FILENAME = N'F:\SQLDBs\Test_Data2.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_INDEX]
( NAME = N'Test_Index', FILENAME = N'F:\SQLDBs\Test_Index.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'Testdb_log', FILENAME = N'G:\SQLLogs\Testdb_log.ldf' , SIZE = 1048576KB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO

ALTER DATABASE [Testdb] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Testdb].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

Monday, September 17, 2012

Blog status

Thanks very much for everyone to visitng this blog, It is really amazing journey to more than 51000 hits.
 
 

Download Service Pack 2 for Microsoft® SQL Server® 2008 R2

Make sure to read KB article 2630458 before you install SP2

Download Service Pack 2 for Microsoft® SQL Server® 2008 R2 from the following web link

http://www.microsoft.com/en-gb/download/details.aspx?id=30437