RSS

Tag Archives: sql server

Listing server and database roles for all logins and users in SQL Server – Part 1 – Local Server

Listing server and database roles for all logins and users in SQL Server – Part 1 – Local Server

I was approached with a unique problem to solve; find out who has what permissions on my Database Servers.  This is the first post in a three part series about finding all user permissions on your SQL Server instance. The next posts will build upon this and introduce a way to use the same queries to get permissions from remote servers.  In the final post, I will create a report that will nicely display the results of all those queries making it easy to audit and fix security problems. The granularity I’m going for is mapping logins/users to their corresponding roles at the instance and database level.

Read the rest of this entry »

 

Tags: , , , , ,

Link

Log File Full–Availability Group Database

An interesting post by Gethyn Ellis about an issue he encountered using Availability Groups. Follow the link above to read more.

I run into an interesting issue with an Availability Group database recently. I was running a data load against a database in an Always On Availability group database in SQL Server 2012.  The load was SSIS package migrating data from staging database when the package fell over with the following error

“The transaction log for database DBName is full due to ‘AVAILABILITY_REPLICA’”

 

Tags: , , ,

Point in Time recovery via GUI a new SQL 2012 Feature


Very nice post about one of the new features of SQL 2012 – the GUI for point in time recovery.

Basit's SQL Server Tips

SQL Server 2012 introduces Database Recovery Advisor that provides significant user experience improvements to the ways DBAs can restore databases with SQL Server Management Studio. As we know, SQL Server provides a variety of backup types, so creating the proper recovery sequence for each point in time can be tricky at times. This is where Database Recovery Advisor is useful, because it makes the database restoration process more agile by helping the customers to create more predictable and optimal recovery sequence.

The Database Recovery Advisor provides a visual timeline from the backup history of the database and then presents the available points in time at which the user can restore the database, algorithms to streamline the identification of the appropriate sets of backup media to restore the database back to a specific point in time. By default, the Database Recovery Advisor tries to restore the database from the last backup…

View original post 186 more words

 

Tags: ,

Running a SQL Server Environment at Home Using Native Boot Virtual Hard-Drives (VHD)

Running a SQL Server Environment at Home Using Native Boot Virtual Hard-Drives (VHD)

I’ve run into a road block. As a Microsoft Certified Trainer, I have all the software I need to get hands on with SQL Server. I even have Virtual Hard Drives (VHDs) that I can download directly from Microsoft; there are even publicly available ones HERE. My problem is on the hardware end. Since I’m not a millionaire that can throw money around and buy an entirely new server to put Windows Server on with SQL Server, I had to find another way… and I did 🙂

Read the rest of this entry »

 

Tags: , , , , , , , ,

SQL SERVER – Beginning of SQL Server Architecture – Terminology – Guest Post


Great post

 

Tags: , , ,

Querying the Management Data Warehouse for Disk Usage over time (Data Collector)

Querying the Management Data Warehouse for Disk Usage over time (Data Collector)

A part of every DBA’s job is to justify all those disk space requests. The phrases “I just need it” or “Trust me this database is going to grow one terabyte this year” might not convince your boss.

Many people are under the impression that disk space is cheap since they base that notion off of the shell shocker they saw on NewEgg or the weekly circular from Best Buy. If you are using consumer SATA drives from retail outlets for your production databases please do the following:
1. Update your resume
2. Apply for jobs at least 1000 miles from your current location
3. Hope the next DBA that replaces you has more common sense

We’re in the times of SANs, iSCSI, SAS drives, SSD drives and RAID architecture! You should be forking out almost the cost of your server in disk space. Unless of course losing data is an option where you work.

Now that I’ve bored you or intrigued you (or something in between) I can get to my point. There is a wonderful tool that comes with SQL Server 2008 and later called the Management Data Warehouse (MDW) also known as the Data Collector.

Read the rest of this entry »

 

Tags: , , , , ,

SQL Server 2012 New Feature – The Sequence Object

SQL Server 2012 New Feature – The Sequence Object

One of the cool new features with SQL 2012 is the Sequence Object.  One of the worst parts of this feature is that whenever I mention it’s a cool new feature a lot of people say “that has been in Oracle forever.” Patience is a virtue!!

Anyway, so I like to consider the Sequence Object as a “Global Identity” that can be shared by many objects in the same database, or even across databases on the same server (I have not tried it via Linked Servers because I only have one test SQL 2012 server). You can take values from the Sequence Object and insert them into tables or you can have a column in a table default to a value from the Sequence Object. A warning to the wise: If you assign a default value for the table as a Sequence Object then you cannot drop it because of dependencies. That is actually a pretty good security measure.

I have included a code snippet so that you can play around with this new feature and I’ve put a few screen shots to show how you can manage it via the GUI. Make sure you run the code section by section; I have included a lot of comments to let you know what to expect while running it.

Read the rest of this entry »

 

Tags: , , ,

Deploying a SQL Server 2012 Multi-Subnet Cluster


Excellent write up on how to deploy a SQL Server 2012 Multi-SubNet Cluster (GeoCluster).

- Microsoft technologies and what I do for fun -

I’ve been wanting to write a series of articles on deploying a SQL Server 2012 on a multi-subnet cluster for quite some time now. This was driven by the fact that my series of articles on SQL Server 2008 Failover Clustering had been in the Top 10 Tips for more than 2 years since being published three years ago. I guess more and more systems administrators and SQL Server DBAs are being tasked with deploying failover cluster instances. Ever since I had my hands on the beta version of Denali (codename for SQL Server 2012) last year,  I’ve been testing some configurations for the multi-subnet clustering feature. I think I’ve built like 3 test environments prior to Denali going RTM just so I can wrap my head around the concepts (plus the fact that Windows Clustering Experts like Microsoft MVP Allan Hirt (blog | Twitter) have been gracious…

View original post 107 more words

 

Tags: , ,

SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name


Good way to find a column being used in a stored procedure rather quickly.

 

Tags: ,

System Center for the SQL Server DBA: Wrap-Up


Might be worth investigating if you need a robust enterprise level system to monitor your database servers.

MSSQLDUDE Blog

I’m going to call the series on System Center for the SQL Server DBA complete with this one last post today. First, here is the full series to date … my apologies for the lack of post naming consistency!!

System Center for the SQL Server DBA Intro

The Continuing Story of System Center for the SQL Server DBA

Monitor SQL Server from System Center Operations Manager

SQL Server management pack for SCOM

System Center for the SQL Server DBA Part 2: DPM

System Center Data Warehouse for SQL Server DBAs

What I wanted to point you all to is an update to my methodology of using SQL Server 2012 business intelligence leveraging in-memory analytical models to provide very important insights into your SQL Server environment with System Center Operations Manager. The link is here to my blog on this on SQL Server Pro Magazine.

By leveraging the SCOM data warehouse…

View original post 61 more words

 

Tags: ,

SQL SERVER – Delete Backup History – Cleanup Backup History


SQL SERVER – Delete Backup History – Cleanup Backup History.

 
1 Comment

Posted by on July 3, 2012 in Database Administration

 

Tags: , , ,

SQL Security Architecture Decomposed Presentation

SQL Security Architecture Decomposed Presentation

Professional Association for SQL Server Presentation
Topic: SQL Server Security Architecture Decomposed
Presenter: Ayman El-Ghazali

Abstract: Security can be difficult to understand when first approaching SQL Server due to the many layers. In this webinar we will take a top down look at SQL Server Security and then “peel” its layers one by one to understand how it works. This webinar will help those who wish to understand security layers and concepts better with in SQL Server. We will cover how to properly manage individual and group access to your SQL Instance, Databases, and many other securables. Please join us on this journey from your server’s network port all the way to your tables, stored procedures, and functions.

http://dba.sqlpass.org/MeetingArchive.aspx

June 27, 2012 – SQL Server Security Architecture Decomposed. Click here to start streaming.

Slides and Code for all Presentations can be found here

 

Tags: , , , , ,

SQL Server 2012 New Features Presentation

SQL Server 2012 New Features Presentation

Professional Association for SQL Server Presentation
Topic: SQL Server 2012 New Features
Presenter: Ayman El-Ghazali

SQL Server Presentations with PASS (Professional Association for SQL Server) Virtual DBA Chapter.

http://dba.sqlpass.org/MeetingArchive.aspx

January 11, 2012 – SQL 2012 New Features. Click here to start streaming.

Slides and Code for all Presentations can be found here

 

Tags: , , , ,

 
%d bloggers like this: