Thursday, July 11, 2013

[SQL 2012] In 2012, how to create a fully contained database?

[SQL 2012] In 2012, how to create a fully contained database?

In 2012, how to create a fully contained database?

Posted: 11 Jul 2013 12:21 AM PDT

based on a previous post here, someone had a quesiton on Contained databases, so i decided to create one myself to follow along.BOL says the settings for the database containment can be NONE, PARTIAL or FULL.When you create a database via the GUI, you only get a selection for PARTIAL or NONE.so how do you create a fully contained database?[img]http://www.sqlcoffee.com/images/SQLServer2012_0009_0003.PNG[/img]if i use the scripting option, and try to modify the script to say FULL, i get a syntax error.[quote][color="#FF0000"]Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'FULL'.Msg 911, Level 16, State 1, Line 1Database 'FullyContained' does not exist. Make sure that the name is entered correctly.[/color][/quote][code]CREATE DATABASE [FullyContained] CONTAINMENT = FULL ON PRIMARY ( NAME = N'FullyContained', FILENAME = N'F:\SQLData\SQL2012\FullyContained.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )by the way, my Google-Fu is strong, and everything i see in BOL so far shows changing a database from None to Partial, but nothing for Partial to FULL or None to FULL LOG ON ( NAME = N'FullyContained_log', FILENAME = N'F:\SQLData\SQL2012\FullyContained_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)[/code]

Moving data from Server-A/table-x to Server-B/Table-x

Posted: 11 Jul 2013 12:41 AM PDT

Dear All, I have a script that has 20-25 small single line queries.Objective of script is to select all data from Server-A/table-x and copy to Server-B/Table-x. Overall script takes almost 6-7 hours to complete as most of the tables have more then a billion records. Individual queries has nothing to optimize as they all are single liner select queries. What Should I do to reduce this time, or should I completely change the strategy.Any help will be highly appreciated.Thanks.

SQL Express Tools

Posted: 10 Jul 2013 08:47 PM PDT

Hi, sorry for the fairly basic question but I want to download SQL Express Tools, but just want the SSMS Tool, not Full Express and I'm having difficulty finding it. I already have a number of live production Express installations on various servers and just want to add the SSMS tool, therefore re-installing Express is not an option, i just want the tool! Thanks

Attunity CDC Service Between SQL2012 and Oracle

Posted: 10 Jul 2013 07:53 PM PDT

Hello everyone,I work the last days on a Project where I must transfer Data from an Oracle Database to a Sql Server 2012 Database. The CDC Service works fine and it captures all changes on the Source Database but the Data when transfered to the Destination DB, to our beloved SQL Server 2012, it is altered!!!See, the source Data on the Oracle DB = '164f3293-e5fa-435e-a15d-f67ab12b1486'and the Data on the Destination = '??????????????????' Why?Thank you a lot!Regards,Rena

Contained database

Posted: 10 Jul 2013 05:38 PM PDT

Hi,Can someone please explain what is the downside of switching a database into contained database?I know the benefit of contained database is easy for migrating between server/instance, it makes the database very portable.Any help will be much appreciated.Thanks

How to overcome a Deadlock specifically a Key Lock

Posted: 10 Jul 2013 07:54 AM PDT

Legend:Storefront = the glass & entry door portion of the front of a store like 7-11Bay = the different section of glass that are divided by aluminum verticals that span from the ground to the ceilingHorizontal = A piece of aluminum that spans from the left to right of the inside of a bay.I've got an application that deletes a record from time to time just like what a database does and everything is fine.But I've got a case now where we've added a feature to where 1 - n many of rows could be deleted. What my app is doing is calling a static method for each newly deleted item because that is the way the client is sending the request to my server.It's a loop that does a delete request for each removed / deleted item from the client, and when these request arrive at the server, they arrive one at a time in order, so my static delete method is called for each newly delete request; therefore, it creates a new process (SPID) for each time my stored procedure is called to delete that row/ record, and this leads to a key deadlock.Keep in mind my fellow professionals I'm fairly good at SQL but I'm no guru; however, if someone can point me in the right direction I would be grateful!Let me give you some background on what I'm developing. I working on a CAD program that the CAD UI is written JS, so my brain is already mushy!For this specific situation here is what is happening. I'm allowing the user to add a door to a bay that has horizontals lower than what the door is in height; therefore, the new door covers up the existing horizontals. So, the solution is to remove all horizontals from a bay that are lower to the ground than what a door is in height.[b]Here is a portion of what the CAD UI looks like. I've got the steps in order.[/b]1.) A bay with horizontals that are lower than what the doors height is2.) The door is added to the bay and everything looks as if the horizontals were removed.3.) I removed the door from that bay and the horizontals are still in place, they were never moved because the deadlocks would not allow it.[b]A snap shot reflecting adding a door to the bay with horizontals and then removing the door to see no changes ever took place because of deadlocks[/b][img]http://www.alumcloud.com/h/deadlock-keylock-help.png[/img][b]Diagram of the important portion of this deadlock[/b][img]http://www.alumcloud.com/h/deadlock-keylock-diagram.png[/img][b]A few of the deallock graphs that I was able to capture.[/b][img]http://www.alumcloud.com/h/deadlock-keylock-graph.png[/img][b]The script relevant to this question[/b][url=http://www.alumcloud.com/h/deallock-keylock-script.txt][/url]I've got to get this resolved extremely quickly so any and all suggestions would be great.I'm needing to know how to keep sql from deadlocking when a batch of deletes need to take place on one table.------------------------------------------------------------------------------------------------------Here is the script in text format rather that that link.USE [StorefrontSystem]GO/****** Object: StoredProcedure [Storefront].[proc_DeleteHorizontal] Script Date: 7/10/2013 3:16:10 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE proc [Storefront].[proc_DeleteHorizontal]@HorizontalID INTASSET NOCOUNT ON;BEGIN BEGIN TRAN DeleteHorizontal DECLARE @SizeID INT, @FinishID INT, @SitelineID INT; SELECT @SizeID= h.SizeID, @FinishID = h.FinishID, @SitelineID = h.SitelineID FROM Storefront.Horizontal h with(nolock) WHERE h.ID = @HorizontalID DELETE FROM [Storefront].[Horizontal] WHERE [Storefront].[Horizontal].ID = @HorizontalID; DELETE FROM Storefront.Size WHERE ID= @SizeID; DELETE FROM Storefront.Finish WHERE ID= @FinishID; DELETE FROM Storefront.Siteline WHERE ID= @SitelineID; COMMIT TRAN DeleteHorizontalEND;SET NOCOUNT OFF;GO/****** Object: Table [Storefront].[Finish] Script Date: 7/10/2013 3:16:10 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [Storefront].[Finish]( [ID] [int] IDENTITY(23,1) NOT NULL, [Left] [varchar](30) NULL, [Right] [varchar](30) NULL, [Top] [varchar](30) NULL, [Bottom] [varchar](30) NULL, [Type] [varchar](8) NOT NULL, [Note] [varchar](150) NULL, CONSTRAINT [PK_Finish_ID] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** Object: Table [Storefront].[Horizontal] Script Date: 7/10/2013 3:16:11 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [Storefront].[Horizontal]( [ID] [int] IDENTITY(11,1) NOT NULL, [Position] [tinyint] NOT NULL, [Name] [varchar](15) NULL, [Floor] [decimal](8, 4) NOT NULL, [SizeID] [int] NOT NULL, [IsFiller] [bit] NOT NULL, [FinishID] [int] NOT NULL, [SitelineID] [int] NOT NULL, [Note] [varchar](150) NULL, CONSTRAINT [PK_Horizontal_ID] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** Object: Table [Storefront].[Siteline] Script Date: 7/10/2013 3:16:11 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [Storefront].[Siteline]( [ID] [int] IDENTITY(1,1) NOT NULL, [LeftCompID] [int] NULL, [RightCompID] [int] NULL, [TopCompID] [int] NULL, [BottomCompID] [int] NULL, [Top] [decimal](6, 4) NULL, [Bottom] [decimal](6, 4) NULL, [Left] [decimal](6, 4) NULL, [Right] [decimal](6, 4) NULL, CONSTRAINT [PK_Siteline_ID] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object: Table [Storefront].[Size] Script Date: 7/10/2013 3:16:11 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [Storefront].[Size]( [ID] [int] IDENTITY(231,1) NOT NULL, [HeightFeet] AS ([HeightInches]/(12)) PERSISTED, [HeightInches] [decimal](8, 4) NOT NULL, [HeightPercent] [decimal](7, 4) NOT NULL, [HeightPixels] AS (([HeightInches]/(12))*(25)), [WidthFeet] AS ([WidthInches]/(12)) PERSISTED, [WidthInches] [decimal](8, 4) NOT NULL, [WidthPercent] [decimal](7, 4) NOT NULL, [WidthPixels] AS (([WidthInches]/(12))*(25)), CONSTRAINT [PK_Size_ID] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [Storefront].[Size] SET (LOCK_ESCALATION = DISABLE)GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Left] DEFAULT (' ') FOR [Left]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Right] DEFAULT (' ') FOR [Right]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Top] DEFAULT (' ') FOR [Top]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Bottom] DEFAULT (' ') FOR [Bottom]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Type] DEFAULT ('Anodized') FOR [Type]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Note] DEFAULT (' ') FOR [Note]GOALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_Position] DEFAULT ((0)) FOR [Position]GOALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_Floor] DEFAULT ((0)) FOR [Floor]GOALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_IsFiller] DEFAULT ((0)) FOR [IsFiller]GOALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_Note] DEFAULT (' ') FOR [Note]GOALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Left] DEFAULT ((0)) FOR [LeftCompID]GOALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Right] DEFAULT ((0)) FOR [RightCompID]GOALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Top] DEFAULT ((0)) FOR [TopCompID]GOALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Bottom] DEFAULT ((0)) FOR [BottomCompID]GOALTER TABLE [Storefront].[Horizontal] WITH CHECK ADD CONSTRAINT [FK_Horizontal_Finish_FinishID] FOREIGN KEY([FinishID])REFERENCES [Storefront].[Finish] ([ID])GOALTER TABLE [Storefront].[Horizontal] CHECK CONSTRAINT [FK_Horizontal_Finish_FinishID]GOALTER TABLE [Storefront].[Horizontal] WITH CHECK ADD CONSTRAINT [FK_Horizontal_Siteline_SitelineID] FOREIGN KEY([SitelineID])REFERENCES [Storefront].[Siteline] ([ID])GOALTER TABLE [Storefront].[Horizontal] CHECK CONSTRAINT [FK_Horizontal_Siteline_SitelineID]GOALTER TABLE [Storefront].[Horizontal] WITH CHECK ADD CONSTRAINT [FK_Horizontal_Size_SizeID] FOREIGN KEY([SizeID])REFERENCES [Storefront].[Size] ([ID])GOALTER TABLE [Storefront].[Horizontal] CHECK CONSTRAINT [FK_Horizontal_Size_SizeID]GO

management studio

Posted: 10 Jul 2013 06:30 AM PDT

Hi Friends, We installed sql server 2012 on a windows 2012 server last week. We are unable to connect to the 2012 database from SQL 2008/2005 Management studio's. Is there a way to make the 2005/2008 SSMS connect to 2012 db's or is it not forward compatible? Thanks a lot again
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog