You might get the following error when trying to connect to a SQL Server CE edition on a network share SQL Server Compact does not support opening database files on a network share. (System.Windows.Forms). So much for me trying to put my SQL Server CE database on my cluster her at home. One interesting thing…you can’t connect to it via a network share, but you can create it on a network share and connect to it via the node that you happen to be on. WTF? Below, I am on one of my cluster nodes and have a SQL Server Compact Edition database open on my network share (M:). Let’s dork around with this some more. I’ll get back to ya. Lee --------------------------
b05ab027-f0a4-46bc-bbb3-7a1ba28f9e8a|0|.0
Installing SQL Server 2008R2, you might get the following...SQL Server starts to load, and then it craps out:
Managed SQL Server Landing Page has stopped working
The problem is that – for SQL Server 2008R2 circa Jan 2012, you got to overly excited and downloaded and installed the .Net Framework 4.0. One catch though. When you go to Uninstall a Program in the Control Panel, I found that you do not need to uninstall the Microsoft .Net 4 client profile, only the Microsoft .Net 4 Extended. The consequences of leaving the client profile installed I have no idea, but I know that it works because I just installed three instances of SQL Server 2008R2 as I prepare to set up a SQL Server Mirror on my network, and had to do this uninstall three times; each time I did not uninstall the client profile.
Thanks for reading,
Lee
-----------------------
7cde4432-3326-47e6-8a56-e8d0b329cf3d|0|.0
Download a free eBook available from PASS, the Professional Association for SQL Server. It’s located here and looks to be chock full of good stuff, excerpts from volumes 1 and 2 of the full deep dives books. It’s 96 pages that comprise eight chapters selected from the original books. If you like the information, then you may want to go ahead buy the others for your toolbelt. Lots of “heavys” within the SQL world who author the chapters, some top-notch SQL folks for sure, so I’m confident that the reading is good. I’ll be downloading the freebie and checking it out. Lee --------------- http://www.manning.com/passbook/
35f49875-9d3a-4efd-bbc8-9453189b6008|0|.0
Fresh off of reading the great blog by the kind folks over at SQLCat, I thought first in using their discovery and experimentation for a slightly more practical use…trying to implement the consistency and richness of HASHBYTES over BINARY_CHECKSUM() for comparing rows in a table. (Their example is fine, but I’m not doing a lot of sharding and cloud stuff these days). Because BINARY_CHECKSUM() isn’t all that in SQL Server, this might work perfectly for this type of operation, so let’s check it out. Run the following script:USE tempdb
IF OBJECT_ID('HashbyteTest') IS NOT NULL
DROP TABLE dbo.HashbyteTest
GO
IF OBJECT_ID('HashbyteTest2') IS NOT NULL
DROP TABLE dbo.HashbyteTest2
GO
SELECT fileid, groupid, size, maxsize
INTO dbo.HashbyteTest
FROM sys.sysfiles
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Now I’m going to add a computed column using HASHBYTE, and create another table to do our data compare. Remember that SELECT INTO doesn’t take computed columns and push them forward, so I’m going to have to script it out to get HashbyteTest2 table like I want it:
ALTER TABLE HashbyteTest
ADD ColumnHash AS
HashBytes('MD5', CAST(fileid AS VARCHAR(4000)))
+HashBytes('MD5', CAST(groupid AS VARCHAR(4000)))
+HashBytes('MD5', CAST(size AS VARCHAR(4000)))
+HashBytes('MD5', CAST(maxsize AS VARCHAR(4000)))
GO
CREATE TABLE [dbo].[HashbyteTest2](
[fileid] [smallint] NULL,
[groupid] [smallint] NULL,
[size] [int] NOT NULL,
[maxsize] [int] NOT NULL,
[ColumnHash] AS
(((hashbytes('MD5',CONVERT([varchar](4000),[fileid],0))
+hashbytes('MD5',CONVERT([varchar](4000),[groupid],0)))
+hashbytes('MD5',CONVERT([varchar](4000),[size],0)))
+hashbytes('MD5',CONVERT([varchar](4000),[maxsize],0)))
) ON [PRIMARY]
GO
INSERT dbo.HashbyteTest2 (fileid, groupid, size, maxsize)
SELECT fileid ,groupid ,size ,maxsize
FROM dbo.HashbyteTest
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
I’m just adding the hashbyte result together. Also note that the data type for the column converts to VARBINARY, so if you decide to do this some other way using variables, you can use VARBINARY to stuff the values in.
Do our cool compare from a previous blog post and see how this one works using the ColumnHash:
SELECT MIN(fileid) AS fileid, ColumnHash
FROM
(SELECT fileid, ColumnHash
FROM dbo.HashbyteTest
UNION ALL
SELECT fileid, ColumnHash
FROM dbo.HashbyteTest2) X
GROUP BY ColumnHash
HAVING COUNT(1)=1
These are the same. So let’s update the data and then run the same script again:
UPDATE dbo.HashbyteTest2
SET groupid=-500
WHERE fileid=2
GO
The actual value changed, which you can see if you run only the Hashbyte function for the groupid column. Check the below comparison where I”ve highlighted the groupid hash:
F89CC14862CB876184FCDFD776B5C3DCB5E21872DAB26657D5FF90F89CC14862CCFCD208495D565EF66E7DFF9F98764DA26657D5FF90
Update it back and then run the function – looks pretty good again to me.
UPDATE dbo.HashbyteTest2
SET groupid=0
WHERE fileid=2
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Thanks for reading,Lee
----------------------
http://blogs.msdn.com/b/sqlcat/archive/2011/11/28/writing-new-hash-functions-for-sql-server.aspx
a8fffc51-d38a-4de5-81eb-b1bdcc45aa98|0|.0
I was geeking last weekend, no wait, actually three weeks ago, and then last night and tonight…it doesn’t really matter, with CASCADE DELETES in SQL Server 2008 R2, and was curious about whether or not I could trace Cascade Referential Integrity in SQL Server Profiler. Do you use Cascade Deletes in your foreign keys? Me neither that much, but I find them sort of fascinating. I should run a test comparing them to coded deletes – I bet they would leave delete statements in the dust. Anytime that SQL Server can do something natively, I’m putting my money on the engine that it can process faster whatever you or I might be trying to do in code. Create the tables to set up our little experimentation: /****** Object: Table [dbo].[tbl_1] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_1](
[Id1] [int] NOT NULL,
[Value1] [varchar](20) NULL,
CONSTRAINT [PK_tbl_1] PRIMARY KEY CLUSTERED
(
[Id1] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_2] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_2](
[Id2] [int] NOT NULL,
[Id1] [int] NULL,
[Value2] [varchar](20) NULL,
CONSTRAINT [PK_tbl_2] PRIMARY KEY CLUSTERED
(
[Id2] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_3] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_3](
[Id3] [int] NOT NULL,
[Id2] [int] NULL,
[Value3] [varchar](20) NULL,
CONSTRAINT [PK_tbl_3] PRIMARY KEY CLUSTERED
(
[Id3] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_4] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_4](
[Id4] [int] NOT NULL,
[Id3] [int] NULL,
[Id2] [int] NULL,
[Value4] [varchar](20) NULL,
CONSTRAINT [PK_tbl_4] PRIMARY KEY CLUSTERED
(
[Id4] 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
SET ANSI_PADDING OFF
GO
/****** Object: ForeignKey [FK_tbl_2_tbl_1] Script Date: 11/15/2011 20:57:06 ******/
ALTER TABLE [dbo].[tbl_2] WITH CHECK ADD CONSTRAINT [FK_tbl_2_tbl_1] FOREIGN KEY([Id1])
REFERENCES [dbo].[tbl_1] ([Id1])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_2] CHECK CONSTRAINT [FK_tbl_2_tbl_1]
GO
/****** Object: ForeignKey [FK_tbl_3_tbl_2] Script Date: 11/15/2011 20:57:06 ******/
ALTER TABLE [dbo].[tbl_3] WITH CHECK ADD CONSTRAINT [FK_tbl_3_tbl_2] FOREIGN KEY([Id2])
REFERENCES [dbo].[tbl_2] ([Id2])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_3] CHECK CONSTRAINT [FK_tbl_3_tbl_2]
GO
/****** Object: ForeignKey [FK_tbl_4_tbl_3] Script Date: 11/15/2011 20:57:06 ******/
ALTER TABLE [dbo].[tbl_4] WITH CHECK ADD CONSTRAINT [FK_tbl_4_tbl_3] FOREIGN KEY([Id3])
REFERENCES [dbo].[tbl_3] ([Id3])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_4] CHECK CONSTRAINT [FK_tbl_4_tbl_3]
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
I’ve created four tables, all with a PK and an FK that include DELETE CASCADE; so what’s happening here is that when I delete a record in tbl_1, the foreign keys through to tbl_4 will delete the associated foreign key records. Rather than write some trigger or delete the associated rows in some other fashion, I’m letting SQL Server do it.
Compile this procedure (it’s complicated I know right?) and then take a look at the execution plan. If you forgot, just highlight the stored procedure name in a management studio query window and type control + L:
DROP PROCEDURE proc_cascadetest
GO
CREATE PROCEDURE proc_cascadetest
AS
DELETE tbl_1 WHERE Id1=1
GO
Notice that the execution plan shows the CASCADE DELETE action for the stored procedure; each step for the foreign key tables is clearly found in the plan. This sort of coincides with what Microsoft said awhile back in the following post on Microsoft Support.
You cannot trace cascading deletes and updates in the context of regular events such asTSQL:StmtCompleted and TSQL:BatchCompleted. However, when you execute a statement that performs cascading updates or deletes in SQL Server Profiler, you can trace these cascade actions in the execution plan.
So, these in fact cannot be traced in SQL Profiler. Then, I remember the hundreds or maybe thousands of blogs by Jonathan Keyhaias on the internet about Extended Events in SQL Server 2008 R2. I think this dude maybe invented them and put them into the SQL Engine or something, I’m not quite sure. Since these are supposed to be the all-powerful, all-knowing for internals or so I’m told or led to believe, I thought I’d give them a shot and see if they could serve up the SQL text since profiler can’t. Let’s give it a go. Run the following:
First get the data into our tables…all that I’m doing here is creating the FK equal to the PK + 1, with the FK inserted alongside. Run the script and then go look at the data:
DECLARE @t1_insert int
,@t2_insert int
,@t3_insert int
,@t4_insert intSET @t1_insert=1
SET @t2_insert = @t1_insert+1
SET @t3_insert = @t2_insert+1
SET @t4_insert = @t3_insert+1
WHILE (@t1_insert < 5)
BEGIN
INSERT tbl_1 VALUES (@t1_insert, 'tbl1 val')
INSERT tbl_2 VALUES (@t2_insert, @t1_insert, 'tbl2 val')
INSERT tbl_3 VALUES (@t3_insert, @t2_insert, 'tbl3 val')
INSERT tbl_4 VALUES (@t4_insert,@t3_insert, @t2_insert, 'tbl4 val')
SET @t1_insert+=1
SET @t2_insert+=1
SET @t3_insert+=1
SET @t4_insert+=1
ENDGO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Run this code now to start up extended events session. Make sure and change the sqlserver.database_id for your particular database ID. And, don’t run the script below all at once – maybe just the first part to create the session, then run the stored procedure, and then the part to kill the session:
-- Create the event session and add some events
DROP EVENT SESSION track_sprocs ON SERVER
GO
CREATE EVENT SESSION track_sprocs ON SERVER
ADD EVENT sqlserver.module_end (ACTION (sqlserver.tsql_stack, sqlserver.sql_text) WHERE sqlserver.database_id=6),
ADD EVENT sqlserver.sql_statement_starting (ACTION (sqlserver.tsql_stack, sqlserver.sql_text) WHERE sqlserver.database_id=6),
ADD EVENT sqlserver.sql_statement_completed (ACTION (sqlserver.tsql_stack, sqlserver.sql_text) WHERE sqlserver.database_id=6)
ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
GO
-- Start the session
ALTER EVENT SESSION track_sprocs ON SERVER
STATE = START
GO
-- Add another event
ALTER EVENT SESSION track_sprocs ON SERVER
ADD EVENT sqlserver.lock_released
-- Run the test procedure
EXEC proc_cascadetest
GO
-- Drop the events now
ALTER EVENT SESSION track_sprocs ON SERVER
DROP EVENT sqlserver.module_end,
DROP EVENT sqlserver.sql_statement_completed,
DROP EVENT sqlserver.lock_released,
DROP EVENT sqlserver.sql_statement_starting
GODROP EVENT SESSION track_sprocs ON SERVER GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Now run either or both of the following: I don’t see the code that shows me that the delete has happened in the CASCADE action that I coded. WTF?
SELECT top 1000
event_xml.value('(./@name)', 'varchar(100)') as [event_name],
event_xml.value('(./data[@name="object_name"]/value)[1]', 'varchar(255)') as [object_name],
CAST(event_xml.value('(./action[@name="tsql_stack"]/value)[1]','varchar(MAX)') as XML) as [stack_xml],
event_xml.value('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') as [sql_text]
FROM #xml_event_data
CROSS APPLY xml_data.nodes('//event') n (event_xml)
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
SELECT TOP 20 SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY Creation_Time DESC
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Both images above clearly show that the code that does the RI isn’t in the session that I ran.
My conclusion – none. I tried adding several events in my Extended Event session (is this the same as what people are calling “xevents”?) and couldn’t get anything to pop out. Anyone have the phone number to SQLSkills.com?
Thanks for reading,
Lee Everest
-------------------------
I laid an egg on this one – you try it and give me a shout if you figure it out. I’m gonna go drink a beer. Bye.
fc762b93-24c3-446e-b377-2999b6d84729|0|.0
You might get the following when working with Managed Service Accounts, or MSAs that connect with SQL Server 2008 R2: Login failed for user ‘username'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: IP adress] First off, you might ask what an MSA is. Well, check out the following link and learn a bit about them; you’ll probably be seeing them sooner or later in your shop. Essentially, you would use these instead of a domain account for services such as Exchange or IIS on a particular box. From the Service Accounts Step-by-Step Guide, they do the following: “Managed service accounts in Windows Server 2008 R2 and Windows 7 are managed domain accounts that provide the following features to simplify service administration: Automatic password management. Simplified SPN management, including delegation of management to other administrators. Additional automatic SPN management is available at the Windows Server 2008 R2 domain functional level. For more information, see "Requirements for using managed service accounts and virtual accounts" in this document.” If using them in an SOA architecture for services, clear the app pool to allow access of the MSAs and then they will be able to connect to SQL Server. Thanks for reading, Lee ------------------------------ http://technet.microsoft.com/en-us/library/dd560633(WS.10).aspx http://technet.microsoft.com/en-us/library/dd548356(WS.10).aspx
61e9efd5-311b-4f2e-b778-741ca20bd7de|0|.0
My good friend Lori Brown just had an article in this month’s SQL Server Magazine (November 2011), so go check it out at the link and read up on this great technique of capturing actions on a SQL Server via SQL Profiler, and then replaying those actions on another machine. It’s an OLTP thing, mostly, but can be used in the data warehouse as well. The concept is, on a high level, that you back up your database, take a trace of an operation/set of operations, restore your database, and run them again via the SQL Trace facility. It’s a cool way to check your environment when major changes have taken place: a new version of SQL Server install, a new server, new operating system…this may even be a great way to load test a virtual machine to see how it performs to the old box. I actually just thought of this as I’m typing, and it’s a great idea since people are so paranoid about how “performant” these Hyper-V boxes are – this would be a great way of testing a move to VMs! By the way, the graph that Lori gave to SQL Server Mag (below)…this picture, right…it’s mine and I drew it. Where’s my credit, Lori? Just kidding. This sort of test can also be done – maybe even a bit easier than the native way described – by using the Scalability Experts SQL Server Upgrade Assistant. Not to be confused with the SQL Server Upgrade Advisor, this tool is a download for doing basically the same thing, and the best part is that it automates a goodly portion of the tasks in the above picture. My picture!!! Check out Lori’s great article, and try this technique sometime. It works very well. It’s not easy and it takes some time, but it’s worth it, especially with SQL 2012 coming out next March. Thanks for reading, Lee ------------------------ http://www.sqlmag.com/article/performancetuning/sql-server-trace-replay-tool-139800 http://www.scalabilityexperts.com/tools/downloads.html http://www.windowsitpro.com/content/content/139800/Brown-SQL2144-Fig1-lg.jpg
89bca54a-6b5a-4cfb-bc12-c5c74358753d|0|.0
There was a whitepaper that Microsoft put out about a year ago, and was recently reposted by Tony Rogerson and the DatabaseWeekly.com, and I thought it was a pretty cool paper myself, one which I hadn’t seen by the way, so I thought that I would post it was well. Download it – I think it’s a pretty cool paper. A few takeways on the paper that I thought were interesting, here are my cliff notes: Microsoft has a simple multicore licensing policy, in which it reduces licensing costs by physical processors licensing, not cores Microsoft does not tie user licensing with number of processors like those other guys do Microsoft does not require customers to pay licensing on the standby server. The other guys apparently bend you over on these. Damn Double damn Microsoft states that they don’t make any money on editions other than Enterprise (not in this paper, I know this for a fact so just trust me here), but geezsh it shows Standard Edition costs between $7000-$18000 per CPU or per core. So they can’t make any money on that price? They want everyone to be on Enterprise Edition, even if they’re not serving up enterprise data; Enterprise by the way is, in their scenario, $27000 to $55000 per CPU or core. Damn that’s expensive, but those other guys are 3X that cost. Anyway, just rambling here. Go check out the download. Good read. Lee --------------------- I can honestly say that I’ve never run a bootleg…never mind. http://bit.ly/q1FYvI
7244e05f-a9ca-4b50-b8f6-14821052172f|0|.0
I’m doing an interesting project in work where I am “scraping” data off of word documents, and may do a few blogs on it. Probably not the best way to handle this sort of thing, but it works well without the cost of a third-party tool that transforms data to XML, and the code is fairly straight forward. In my task, the data from a series of .doc and .docx templated files will be moved to a .csv, imported to SQL Server, cleaned, moved to a dimension table, and finally, fed to a cube in Analysis Services. Pretty cool eh? I think so. One thing about doing this is that you’ll have to have either 1) Microsoft Office installed on the server, or 2) the Primary Interop Assemblies Redistributable, or PIA, installed on the server, both so that you can take advantage of the Office (in my case Word) methods to fetch the data off of the documents if you plan on scheduling and running from the server itself. Most servers normally don’t have Microsoft Office installed – there’s no reason – and to avoid burning a copy of office that costs money, you can add these assemblies…the download is free. It reminds me back in the day when we used SQL Mail, going back to 1998 and SQL 6.5 and then SQL 7. You had to install the entire Outlook client on the server, and then a smart DBA would go and randomly delete the GUI so nobody could open the client. Just a random thought…didn’t SQL Mail really suck back then, and now? For those of you who started as a SQL practitioner beginning with SQL Server 2005 and only know database mail, you really missed a lot of fun trying to figure out why that abomination never worked correctly. Oh well. Anyway, get the PIARedist.exe, and start doing some slammin’ Office Automation. Happy Primary Interopping! Lee Everest -------------------------------- http://www.microsoft.com/download/en/details.aspx?id=3508
da7d0a11-f174-4c21-a52e-87eff91dcc40|4|4.0
Here’s a .pdf download, put together by the SQL Server Forum Support Team, that lists (and answers) the most common questions on MSDN and TechNet forums. Pretty cool! They divide it up into four parts: Admin, SSAS, SSRS, and SSIS of course. Download "SQL Server Community FAQs Manual (PDF)", or at the link below. Lee Everest ---------------- http://blogs.msdn.com/b/sqlforum/archive/2011/06/10/sql-server-faqs-ebook-with-database-engine-ssrs-ssis-ssas-.aspx
070ac383-8aa6-48a1-921c-4b0c86503749|0|.0
Happened to catch a hotfix - 2489376 Cumulative Update package 6 for SQL Server 2008 R2: FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2 If you get message such as the following, you may have a problem: Analysis of database 'mydatabase' (7) is 0% complete (approximately 1234 seconds remain). This is an informational message only. No user action is required. The fix is intended for those who haven’t managed their transaction logs well. Of course, the best way to handle is to keep the VLF count at 1000 or below; grow your tlog out from the beginning and you won’t have a problem. Anyway, if you see this message in your logs, go get more info here and get the workaround. Lee ----------------- http://support.microsoft.com/kb/2455009
05c4c517-7db1-437e-9695-e4f94fbdf3c0|0|.0
Perusing the MSDN blogs I found a post on a new Windows Azure book just out (5/2011); I think this is the latest book on the subject, so it’s probably pretty decent. (As opposed to the one from 2009). I went over to Amazon and saw it here for 20 bills, so I’ll probably get it just to see what’s up. Some of the info from Amazon: Teach yourself how to build and host scalable applications in the cloud using Windows Azure—one step at a time. Ideal for those with basic programming skills, this tutorial provides practical, learn-by-doing exercises for working with the core services and features of the Windows Azure platform. Discover how to: Extend your existing skills to the cloud development model Build a simple web role application and deploy it to the cloud Create a worker role project to perform backend processes Store persistent data with Windows Azure Storage Develop a scalable database application in the cloud using Microsoft SQL Azure™ Connect several cloud-based applications with Windows Azure AppFabric Design a multitiered solution that can scale to meet user demand Looks pretty decent to me, although I work at an insurance company and they ain’t puttin’ nothing, I mean not one iota’s worth of data on someone else’s server or virtual server! I’ll probably get the book anyway; I have a cloud account, but haven’t been geeking with it whole lot these days. Lee ---------------------- http://blogs.msdn.com/b/microsoft_press/archive/2011/06/08/new-book-windows-azure-step-by-step.aspx http://www.amazon.com/Windows-Azure-Step-Roberto-Brunetti/dp/0735649723/ref=sr_1_6?ie=UTF8&qid=1307675085&sr=8-6 http://oreilly.com/catalog/0790145309099
a28e4953-d2a0-4d90-82c9-06ec22e99ae0|0|.0
I got the following when attempting to process and deploy a cube today:
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'tbl', Column: 'ID', Value: '12345'. The attribute is 'ID'. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: ID of Dimension: dim from Database: db, Cube: cube, Measure Group: fact, Partition: TBL, Record: 1.
I looked around for about 5 minutes on Google and really didn’t find much. Then I located a post from a guy that had ‘scoured’ the internet looking for a solution for hours but couldn’t find one. At that point, I simply pushed it to a new SSAS database and it worked fine; optionally I could have blown away the old and pushed again, but I’m doing development work.
Does anyone have a solution for this problem? I couldn’t find one!
Lee
UPDATE 7/18/2011 - In VS right click and process on the dim or dims that are erroring out. This should fix the problem.
-----------------------
There are occasions where I ask the reader for the answer – this is one of those occasions!
8ed760ea-ccc8-4702-b5a5-dd7da2b647b4|0|.0
Solid Quality Mentors has a free e-book download entitled Data Quality and Master Data Management with Microsoft SQL Server 2008 R2 for you to check out. Grab it here and let me know what you think of it. Master Data Management is definitely picking up some steam for sure. These guys are pretty salty, and from looking at the download, they’ve spent some “quality” time putting it together. I’m not doing anything in particular this Friday afternoon…think I’ll read it for a bit. Lee ------------------- Says “This is not a beginners book”. Should we be scared??? <gulp> http://www.solidq.com/ce-en/News/Pages/Data-Quality-and-Master-Data-Management-with-Microsoft-SQL-Server-2008-R2.aspx
2486e149-3b0d-4237-9955-981d57218802|0|.0
A message from NTSSUG the other day read: Members of local chapters can get a $200 discount to PASS Summit 2011 by using the Discount Code CHM11. This discount has no expiration date. If you would pass it along to your members, we would greatly appreciate it. Not sure if you can simply plug the discount in or if you need to be a member of your particular local chapter, but give it a shot anyway. If you’re down with PASS this year, it’s worth a try. Yeah, $200 ain’t much, but better than a kick in the ass right? I’m down with two bills. Thanks, Lee Everest -------------------- http://www.sqlpass.org/ http://www.sqlpass.org/summit/2011/
136b9546-a1a2-4289-9db4-f363c5fe4bd9|0|.0
I happened across a link the other day and found that Microsoft has released several SQL Server 2008 R2 virtual labs. Not sure if these either trickled out or were all out at the same time, but back a few months ago I couldn’t find any 2008 R2 versions, only SQL Server 2008 stuff existed. Anyway, here’s the links to what I found if you’re interested. If you’ve never spent the time doing these labs, I suggest that you try a few sometime and see if you like them. I’ve always found them really useful for taking the new technologies for a spin, and think the Mothership does one helluva job putting them together. I’m going to catch up on a few this weekend, so you should give ‘em a shot too! - TechNet Virtual Labs for SQL Server 2008 R2 - SQL Server 2008 R2 Virtual Labs Lee Everest --------------- Ah! Yeah. It's just we're putting new coversheets on all the TPS reports before they go out now. So if you could go ahead and try to remember to do that from now on, that'd be great. All right! http://technet.microsoft.com/en-us/virtuallabs/cc164207 http://msdn.microsoft.com/en-us/sqlserver/cc138238
a5f24816-c2a3-4543-98ad-aed7a516d597|0|.0
Check out the Springboard Tour nearest you for some free Microsoft training. Here’s the skinny: Join us for a full day as we take you through a deep dive into the tools, solutions and options to help you do more with less. We will cover managing the flexible workspace, a first look at Windows Intune and Office 365. We’ll also show you some of the new Windows Slates and give you details around Microsoft’s strategy for Slate devices. We will preview the new tools in the MDOP 2011 suite, a deep dive into managing and deploying Office 2010 and great tips and tricks to help you deploy Windows 7 and move your users from Windows XP with speed and ease. Register now and save your seat for this free day of technical demos, Q&A sessions, and real-world guidance from Microsoft experts! Thanks for reading, Lee Everest ------------------------- Doyle: Your buddy Karl here is going. We can't be no normal family with him living in the garage and comin' in the damn bedroom at 4:00 in the morning, carryin' hammers and $hit.
97f5aa99-8ff1-4d96-b360-4ce9ac0aff7b|0|.0