SQL Server

33 posts

SSDT Schema Compare

Most database professionals know this a BIDS from previous versions of SQL Server. However there is a key distinction here. Previously database design was handled within Visual Studio so if you wanted to create a database you would use Visual studio and if you wanted to create a SSIS package you would open BIDS. With SSDT the entire set of project related to the database has now been integrated into one set. This means you can now create the MS SQL Server database in the same interface as the packages. As a result the schema compare feature is also available by default after installing SQL and there is no need for an additional VS interface.

Extended events are among the most powerful data capture tools available within MS SQL Server. This video explores the new extended event GUI which helps the DBA configure and setup the extended events. While being similar to what Profiler would do, XE or extended events are much more powerful and user configurable. It allows the DBA to do much more in terms of monitoring SQL Server. The level of detail and the ability to filter it allows the DBA to work with a variety of options and monitor SQL Server at a very granular level.

Microsoft SQL Server 2012 User defined Server Roles

In this video we are going to explore the use of User defined Server roles. This feature adds additional security and help DBA follow the principle of least privileges within the DBA group. This feature allows the DBA to create customer roles that have very specific permissions and can be created to suit different unconventional roles within the DBA Group. With this feature we can now secure access to the database and the server based on job role and the DBA becomes securable just like any application roles within the databases residing on the server. Many DBAs might not see this is a big deal but for very large organizations which manage thousands of database servers with a resource pool of hundreds of DBAs it helps keep things manageable.

Microsoft SQL Server 2012 Always On, Part 3 of 3

In this last video of the series we explore the DMVs used to monitor the health of the always on availability groups, the dashboard that summaries the health of the different replicas in the group, How to perform a manual failover and the Always on Availability group listener. The video explore the different DMVs and shows what kind of information is available from them. In addition it also shows the dashboard which uses the same DMVs to summarize the health of the system and how to perform the failover manually when doing rolling patch updates. With that we wind up the series on Always On. As can be seen Always On while being a new feature is based on the traditional high availability solutions and therefore very easy to grasp.

Microsoft SQL Server 2012 Always On, Part 2 of 2

In this video we continue exploring the Always ON feature within MS SQL server 2012. This video aims to cover how to setup and configure the Always on Availability group. Here we explore the different screen you will encounter as part of the setup and the basic features you will need to setup the Always on availability group. The video then explore the creation of multiple secondary’s that support read operations as well as setting up the listener etc. At this point the user will be familiar with setting up the Always on availability group. In the next video we cover how to perform a manual failover , and how to connect to the Always On listener and the different DMVs and Extended events associated with Always ON.

Microsoft SQL Server 2012 Always On, Part 1 of 2

In this video we introduce one the new features available within MS SQL Server called Always ON. Like its name suggests Microsoft is pushing this technology as the way to achieve 99.999% uptime and achieve much more scalable databases. These two features have been lacking in the Microsoft database stack for many years now while other databases namely Oracle RAC have been providing similar capabilities for some years now. The Always on feature in MS SQL server is meant to provide instance and database level failover as well as provide the ability to off load non critical business process and maintenance activities to other replicas. The video explain the fundamentals of Always On and how it provides advantages over the previous options available namely Clustering, Mirroring and replication.

Policy-Based Management

In this video we cover the policy based management feature or PBM within MS SQL server. PBM has been around for some time now however there are many DBAs who are still unaware of this feature. PBM is a great tool for ensuring compliance across the database stack especially when dealing with a large number of instances.

MS SQL Server Profiler Basics, Part 3 of 3

In this video we explore how to create a server side trace using the inbuilt stored procedures available within MS SQL Server. We explore how to make this job easy by creating a trace template using the profiler tool and then implementing this within SQL Server. We will also explore how to get the details on which traces are running on a server as well as how to query data from within a trace file.

MS SQL Server Profiler Basics, Part 2 of 3

This video discusses some of the templates that are found in SQL Server profiler and how to use them in relation with other features available in MS SQL Server. We will explore using templates, creating a template and then exporting the TSQL command from within the trace.

MS SQL Server Profiler Basics, Part 1 of 3

This introduction serves as a starting point to working with SQL server profiler and how to configure a trace file and monitor events that happen on a specific database using column filters and additional events. Every DBA is expected to know how to configure and use MS SQL Server profiler and what scenarios to use it in so video is meant to serve as a starting point to understanding profiler.

Connecting to a Microsoft SQL Server Using The Visual Studio 2008 Designer

An inherently powerful feature in Visual Studio is the ability to create a SQL connection and manipulate data in the same fashion as adding components and controls to a project. There is some debate on whether this method is better or worse than interacting strictly via the code, but it is an important feature that developers should know about, should they wish to use it. In this video, I will use a sample database and table previously created, and show you how to create a SQL connection from scratch, a common error encountered during the process, and how to visually manipulate the data directly from your Windows Forms application, without the need for an external editor or administration tool.

Installing SQL Server 2008 Management Studio

SQL Server Express is installed by default along with Visual Studio, which saves developers a lot of time in choosing an appropriate relational database platform. It also exposes most of the concepts one will find in comparable products, and for using a Structured Query Language in general. This means that developers do not have to re-learn fundamental concepts when switching to other technologies such as Oracle and MySQL. There is, however, one thing that is missing by default, and that is a administration option using a graphical tool that most DBA's are familiar with. This can lead to frustrating debugging sessions and more. In this video, I will show you where to get the SQL Server 2008 Management Studio installer, and how to navigate a somewhat unintuitive installation process. From there, I will show you how to view and edit data contained in one of the tables.

Introduction to Spatial data types in SQL Server

In this article I would introduce you to the new data types added in SQL Server 2008 called as spatial data types and the different terminologies and the structures used in them. Prior to SQL Server 2008 spatial data used be stored in a conventional manner by basically creating  two different […]