In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5.3 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.
 
SQL Monitor "It really helped us isolate where we were experiencing a bottleneck"
- John Q Martin, SQL Server DBA. Get started with SQL Monitor today to solve tricky performance problems - download a free trial
 
Red Gate Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.

In This Issue

Stairway to Server-side Tracing - Level 10: Profiler versus Server-Side tracing

Compares and contrasts tracing using Profiler with server-side tracing, illustrating important performance differences so that one can choose the right tool for the task at hand. More »


From the SQLServerCentral Blogs - Database Mirroring Performance Counters

This week’s SQL Skills insider email contains a simple but often too true scenario regarding database mirroring.  If you haven’t... More »


Editorial - The Small Print

Steve is currently away on vacation. Today's guest editorial is by Phil Factor.

Cloud technologies can be adopted to advantage, but there’s more to it than signing a cheque and moving gracefully, tranquilly into the Cloud like some Greek god. You are entering a commercial relationship with a Cloud provider that may lock your company into long-term commitments. Those commitments could prove to be expensive if you don’t do the right checks.

I remember once chatting to a salesman for a large computer company who confided in me that most of the profit was in the ‘extras’. He had based his career on the trick of pitching a ‘bottom-line’ figure that belied the trail of extra requirements that only became apparent near launch.

Cloud services seem cheaper than in-house provision, but you have to check for ‘extra’ costs that emerge when the terms of service turn out not to meet your requirements. The service providers present you with a bewildering menu of  subscription plans, SLOs, and SLAs to meet different needs, but the menu is in Greek or perhaps Double Dutch.

How do the fees scale? The fee that you’re being quoted for a Cloud service may be based on particular volume of use or level of service. What happens if the volume of data or the level of access increases? Is the extra charge linear? Is there a volume discount, or a punitive increase?

Once you have that sorted, what service level should you select. Do regulations compel you to ensure that certain types of data are held in a particular legislative region? Does it cost extra?

You’ve got to be able to deploy your applications easily. Does the service provider offer the proper management tools? Is it easy to move data, applications, and VMs into the Cloud? Is it easy to get your data out? Is it all part of the basic service?  Can your data be retrieved should the service cease to exist?

Are your security requirements met? Does the service provider also give you the reports that are required for compliance and auditing?

Your disaster-recovery strategy may specify that your Cloud provider will automatically move your data away from any areas of risk, such as civil unrest, storm, or pandemic. Can they do this? Do they charge extra for this service?

You may find that, as your needs change, you require faster network access or data storage. You might need improved data resiliency. It is best to know beforehand what your prospective provider will charge for all of this.

I’m as open as anyone to the idea of Cloud services, but just don’t forget to read the small print.

» Join the debate, and respond to today's editorial on the forums

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

Which one of the transaction Isolation levels would you use for a transaction that meets this criteria

1. The statements in your transaction should read only committed data.

2.  Other transactions should not be able to modify the data that has been read by your transaction

3. Other transactions can insert new data into the table with values that would fall in the range read by your transaction.

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: Isolation Levels. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!


Yesterday's Question of the Day

SQL2012 introduced a new functionality called the FileTable. What is it, where and how is it used and what data is stored therein? (Select 3 options)

Answer:

  • It is a special table of files for applications that require file and directory storage in the database, with Windows API compatability and non-transactional access;
  • It is used in conjunction with the VARBINARY(MAX) datatype and FILESTREAM storage type;
  • Every row contains: A FILESTREAM column for stream data and a file_id (GUID) identifier (The FILESTREAM column is NULL for a directory.); Both path_locator and parent_path_locator columns for representing and maintaining the file and directory hierarchy; 10 file attributes such as created date and modified date that are useful with file I/O APIs; A type column that supports full-text search and semantic search over files and documents.

Explanation: FileTable enables the non-transactional addressing of files stored in the database by applications by making use of given Windows API's. It requires the database be configured to allow for non-transactional access, with the file and directory structure hierarchy exposed under the FILESTREAM share configuration for the SQL Server instance. for further reading, please see FileTables (SQL Server)

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 Security Cookbook 

Each recipe comprises step-by-step instructions followed by an analysis of what was done in each task and other useful information. The book is designed so that you can read it chapter by chapter, or look at the list of recipes and refer to them in no particular order. Each example comes with its expected output to make your learning even easier thus enabling you to successfully secure your SQL Server 2012 database. This book is for SQL Server administrators, developers, and consultants who want to secure their SQL Server database with cutting edge techniques for data and code encryption, user authentication and authorization, protection against brute force attacks, denial-of-service attacks, and SQL Injection, securing business intelligence, and more. Working knowledge of SQL Server is expected.

Get your copy from Amazon today.


Featured Script

Dynamic Database Backup

Backup up all databases based on sys.databases. More »


Database Pros Who Need Your Help!

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Restore fails - Hello I am getting below error while i try to restore the backup from network drive Msg 3201, Level 16, State 2,...

Setup multiple SQL Instance in the same cluster - Hi Guys, I have two SQL 2008R2 Enterprise servers setup in an Active/Passive cluster(running on Windows 2008R2 Enterprise server) with...

SP - output confusion? - Hello, we have a SP which in turn calls 2 sp's. because of one sp output data got updated wrongly . but when...

Backup size confusion - My backup sproc logs the start and finish of a database backup. It then updates my BackupSchedule table with the...

SQL job on startup - Hi All, How could I run a sql job or maintenance plan on server startup? Thanks

SQL SERVER AGENT SERVICE NOT STARTED - Error: SQL SERVER 2000 ENterprise sp3.. Could not start SQLserverAgent Service on local computer. The service did not return an error. This...

Full Backup after changing Changing recovery models - Hi Experts, I know we have to run a full backup if we change the recovery model from full--> simple-->full. My...

%FreeSpace Alert in SQL 2005 - Hi All, Im looking for a way to create an alert to email an operator when, for arguments sake, a physical...

Need to enable Ad Hoc Distributed Queries option - Hi, I need the "Ad Hoc Distributed Queries" feature (for the SQLServer installation of my service provider) which is disabled by...

SQL Server 2005 : Backups

Transaction log backp - 15 Mins Frequency - Hi , If i set my Txn log backup frequency for 15 mins, what is the worse data loss? Is it...

Sharepoint SQL Database Backups - Has anyone had an issue with SQL Server databases supporting Sharepoint 2007 waiting sometimes for hours to start the backup...

SQL Server 2005 : SQL Server 2005 General Discussion

locking question - Hello, For this scenario : update Table1 with (TABLOCK, XLOCK) ... then a "INSTEAD OF TRIGGER" on Table1 update Table1 with other value Does the...

SSIS Container Connectors more user friendly? - I know, I'm completely Anal about this stuff; however, I don't like the connectors between the containers to be overlapping...

Delete Using join in min 3 tables - Can any one plz tell me how to delete records from minimum 3 tables using inner join on the basis...

SQL Server 2005 : SQL Server 2005 Security

SQL Authentication login weirdness... - Forgive me upfront for not supplying a snapshot of this issue, but here is what happened. I tried to log...

SQL Server 2005 : SS2K5 Replication

merge replication across different networks - Hi i would like to know how would i be able to do a merge repication of databases if the...

SQL Server 2005 : SQL Server 2005 Integration Services

Need to load additional tab in Excel every month using SSIS - Hi, Excel file has two tabs(Mon1 and Mon2) and i have loaded them into table by creating SSIS package. All the tabs...

data Conversion Help - trying to Imprt data from Excel to Table in SSIS. One of the column in excel is in Date format. But...

SQL Server 2005 : T-SQL (SS2K5)

Concurrent access to duplicate records - Hi all, Got a situation I haven't had to deal with before. Basically, I have a table which stores products. This...

Stored Proc - Nullable Parameters for optional filtering - handling LinkTable LEFT JOINs - Hey all, Pretty long title, but the problem itself is fairly simple to explain. Suppose I have the following tables: [code] CREATE TABLE...

SQL Server 7,2000 : T-SQL

Script to identify who is using which view and at what time? - HI, There is a requirement to create a script to identify who is using which view and at what time? Its...

SQL Server 2008 : SQL Server 2008 - General

Help with doing “Cast the decimal as string, parse into the format specified, and then cast back into datetime”? - Hello Fellow developers, I'm a rokee when it comes to T-sql. I have been trying to create a view from...

Access??? - I'm getting the access denied error. I went into DCOM config and I don't see anything near "Microsoft SQL Server...

SSAS Partitioning & SQL Server Partitioning - Just wanted to clarify if these 2 are treated the same in terms of licensing. SQL Server Standard Edition does not...

job history - hello experts, if i remove job history in every 2 weeks, is there any way to get information of any job...

SQL Server security help - Just fiunished installing a new instance of SQL Server on a brand new server in the DMZ. I created two local...

merge replication across different networks - Hi I have two servers, they both on a different site(hence being on different networks). I need to be able to...

Creating a Stored Procedure that accepts parameters - Hello, I'm trying to create a stored procedure named InsertSalesOrderPart that accepts parameters: @OrderNumber int, @PartID int, @Quantity int, and @ExtendedCost...

Unable to shrink tempdb - In one of our servers tempdb is grown to 75 gb. If we check properties of tempdb it is showing...

way to enforce object naming conventions? - Hi, i'm wondering if there is any way to enforce object naming conventions (using three-part identifiers) within views/functions/procedures ? for example, use...

Connect To Server Dialog showing logins that no longer exist - Hello, When connecting to a SQL2005 instance using 2008R2 Studio, any 'Connect to Server' dialog boxes include a deleted login in...

Deny Backup - Is there a way to deny a login from backing up any database on a sql instance, or will I...

Attach database with the same id - i detached the database with the dbis as 6 , after i attached the database the db is has changed. How...

Difference in CDC vs Trigger - What is the Difference in CDC vs Trigger? and which one is performance wise a good one to go. Thanks!

Find a string in SSIS Packages folder - Hi, I have a folder of about 250 packages. I want to find all the packages which have a string '2075'...

Search String in Entire Database - Hi, I want to search a string in Entire Database. But it should not take more time to execute. Someone please help...

migration form sql 2005 to 2008 - HI every one I have SQL SERVER 2005 on Windows server 2003r2. We want to upgrade it from the SQL...

restore database with different domains - Hello I had one task. I need to restore on DATABASE on [Server A] from [Server B]. [Server A] is on VINEMAPPLE...

Getting sql server job status into a table - Hi Guys, 1) I need to insert a single job status after completing every run into a new table and 2)...

SQL Server 2008 installation - Need to install SQL Server on a new box. OS installed and ready etc... The server has two drives: C - RAID 1...

To OTAP or not to OTAP? - Currently I am working in an organization, where development and production databases reside in the same SQL Server instance. The...

Using Full-Text Search - Hi, I am quering a big table (about 150,000 rows) with a big varchar field (size 2000) which can't be indexed...

SQL Search Query - hello, right now on my search page the sql query i am using is similar to: select * from table where name like...

query taking long time for execution inthis case what should i do - hi all 1. application team complaining that queries are executing slow and want reason for that in this case what should...

rebuild index worsens query performance - we have a query which runs several times a day reads from several tables in one database ("W"), and two tables...

xp_fileexist works differently for different users - I have a situation where I am checking to see if a file exists before I create a file. I...

Very slow opening "Job Activity Monitor" in SQL 2008, no changes has been done - Hello: Either when I open "Job Activity Monitor" from my workstation in SSMS or in an Remore Desktop Session, It takes...

Latin1_General_BIN Collation - I am installing SQL Server 2008 R2 Standard Edition with required SQL_Latin1_General_BIN collation, but this particular collation is not found...

SQL Server 2008 : T-SQL (SS2K8)

How do I get database space used - Hi, How do I get database space used (not database size) for all given databases? At this moment, I do have a...

Need help with a script to identify count of negative and positive numbers in a record - Hello All, I need help with a tsql query. Below is the table structure. [code="sql"] CREATE TABLE [dbo].[TableName]( [col1] [int] NOT NULL, [col2] [int] NOT...

Performance Issue - We have a procedure which performs _NO_ update or delete operation but only read. This procedure runs daily without any...

Convert values of mulitiple rows into single column with no duplicate entries - hi all, i went through the forum and found out a way of concatenating row values into column using stuff, but...

returning all dates that match year and month - SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime, MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount,...

Avoiding cursor: Help with getting only first match after previous match - Hi I'm sure there is a better name for this problem, and its probably quite a common one with a...

CASE Statement in SELECT clause to convert into the where clause - ???

SQL Server 2008 : SQL Server Newbies

New DBA job, done some digging on Database and have some concerns! - I've recently started a Job at company working as the sole DBA. I come from a .net developer background and...

Alternative to views - Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2...

last job that ran - Hi all, If I have a job that executed twice, how can I, using t-sql, find which one executed last? Thanks, Eugene

SQL Server 2008 : Security (SS2K8)

Trace login created or dropped event - Hi All, I have been trying to findout the logins that were created or dropped in past few days. I queried...

SQL Server 2008 : SQL Server 2008 High Availability

Mirroring Strange Behaviour Question - Hi all Last night one of our mirrored databases failed over and then failed back automatically. This seems strange behaviour to...

Need to Install Multi Instance SQL Server on a 2-node A/P Cluster - Hi All, I am trying to install Multiple instances of SQL Server 2008 R2 on a Windows Cluster in a Active...

SQL JOBS Not Functioning - SQL JOBS Not Functioning :w00t: I have Stored procedure & that stored procedure needs to be executed on 20 Databases daily, Currently i...

SQL Server 2008 : SQL Server 2008 Administration

Plan Cache/Memory Performance - Hi Guys I am dealing with a system that seems to be under memory pressure. According to task manager, memory is...

SQL Server Agent - Unable to determine if the owner of job has server access. - I've read a few posts regarding similar messages, but I don't seem to be able to find a specific answer...

system databases mirroring - On the mirroring server (The server which is mirroring the production server databases), should it also mirror the system databases...

Setting Notification email (When the job fails) - Hi Everyone, I have setup a job(SQLS ERVER 2008 R2) in two servers Server1.Domain1 & Server1.Domain2 and I have enabled notification email...

SQL Server Backups - This is more of a poll but I'd like to get others opinions. What is the recommend and preferred methods...

Report Server help - I am confusing with below questions from my manager. Could you please answe asap. 1.what is the difference between DB server...

Tempdb log file location and Filegroup optimizations - I've been reading articles about tempdb optimizations and I already understand a lot better the different ways to archive it....

Career : Certification

Hi Thanks for assisting me in my efforts to get VmWare certification. - Hi I would like to thank some friends and members for assisting me in my efforts to get VmWare certification....

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

SQLServerCentral.com : Anything that is NOT about SQL!

Career Advice Needed - I have an interesting dilemma, and could use some advice. I have spent the last two years as a Network...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Cannot connect to Reporting Services with Managment Studio - I've just set up a new instance of SSRS 2012 and I'm unable to connect to it through Management Studio....

How to remove trailing zeros and decimal? - Hi, this is my first post here. I tried searching for this and didn't have luck, so I apologize if...

Database Design : Design Ideas and Questions

Advanced group design - Hi All I ask you guidance on this design question because this seems to be one of those things where if...

Lots of Key Lookups vs. UniqueIdentifier Clustered Index - Hello. I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes. Later the clustered indexes were rebuilt on...

Just a rant about how third parties design apps - And by third parties, I mean of all parties, Microsoft themselves. If a column needs to be added to a...

Field Sizes in Staging database : all varchar(2000)? - NEWBIE to DW : the IT Manager wants to use varchar2(2000) for all fields in all tables. I thought we should be...

Schema binding across multiple databases - Hi All We need to create a clustered index by implementing schema binding in the view. As you know it throws...

Data Warehousing : Integration Services

ssis package for loading excel sheet with diff no of columns - hai, can anybody tell me how to load the excel with diff columns ie i have global , region, cluster, market what...

Load newly inserted data from millions of records on daily basis-Using SSIS - Hi All, I have to load data from one server to another server on daily basis using SSIS Package. I have only...

What is the best isolation level - Could you tell me , Which is best isolation level for SSIS and T- SQL ? anyone advice me.. Thanks solomon

Data Warehousing : Strategies and Ideas

Building SSAS Cubes - Beginner - Hi, Is it mandatory to have a data warehouse in dimensional model to build a SSAS cubes above? I'm trying to see...

Datawarehouse - My PO keeps asking for more tempdb - I think we have all been in this situation before. Devs fill TEMPDB then say they need more TEMPDB. Here is...

Data Warehousing : Analysis Services

SSAS - Cube - Startup - Hi, Is it mandatory to have a data warehouse in dimensional model to build a SSAS cubes above? I'm trying to see...

Store Comments in Cube - Hi everyone, I need some pointers with my problem. I have a financial application cube in SSAS 2008 R2. My client...

Excel 2010 Not showing Calculations created - Hi I hope someone can help me. I have a cube that contains several calculations, these are created in the...

Creating measure groups/folders - I have an existing cube, which I will be adding a lot more measures to as we will now be...

Article Discussions by Author : Discuss Content Posted by Jeff Moden

need a urgent help - CREATE TABLE STUDENTLOGIN(username varchar(30), password varchar(30), created_by_id varchar(20), created_date varchar(20), updated_id varchar(20), updated_date datetime, transaction_id numeric(12)); the above table i create