Monthly Archives: February 2013
SQL Saturday #192 Tampa in a Few Days
We interrupt our series on replication, to remind everyone that this Saturday is SQL Saturday Tampa. Our Tampa user groups are proud to present a stellar lineup and three pre-con trainings this year focusing on DBA, BI, and professional development. The pre-cons are as follows:
- For the DBAs: SQL Server Internals from the practical angle by Dmitri Korotkevitch
-
For the BI enthusiasts: Taking the Next Step with Reporting Services by Jessica Moss
- For Professional Development: Creating Your Best Technical Presentation: A Speaker Workshop by Buck Woody (Come sit with me in this session with all of the cool kids!)
Keep in mind that we have moved to Hillsborough Community College Ybor Campus to accommodate our recent growth and to allow us to host more speakers featuring eight tracks this year with 46 sessions lined up. Here is a map of the layout so that you do not get lost:
If you have not registered yet, what are you waiting on? Register now and avoid the rush!
Replication Series, Part Five: Creating a Snapshot Replication
Figure 1 – New Publication
Continuing our series on replication, today we look a little deeper at Snapshot Replication. After you have ensured that replication is installed on both nodes, open the replication node in SQL Server management studio on the node that you want to be the publisher and right-click Local Publisher and select New Publication (See Figure 1).
This will open the New Publication Wizard. Select Next if you are presented with a starting page. Choose the defaulted server which should be your local server and select Next.
Now we are prompted for the snapshot folder. This is the network share that needs to have permissions enabled for the publisher and subscriber.
Next we will choose the database that we wish to replicate and then select Next. Then we will select the Snapshot publication type as shown in Figure 2.
Figure 2 – Replication Type
Then we will select the articles or tables and views that we wish to replicate. Here, I have selected all tables and views in Figure 3, but you can drill down and select only certain ones if you prefer. The next screen indicates if there are any issues and how to correct them.
Figure 3 – Select Articles
Now we must setup the agent and create the snapshot so that we may initialize the subscription as shown in Figure 4. Select the Change button to set up your scheduled agent times to generate your snapshot.
Figure 4 – Snapshot Agent
On the final page of the wizard, we must select an account that will run the agent. You can run this under a Windows account or the SQL Server Agent service but I would not recommend that as it is not best practices. After you name the publication you will see a status screen as your publication is set up by SQL Server. You’re done!
Stay tuned and enjoy!
Replication Series, Part Four: Snapshot Replication
Continuing our series on replication, today we look at Snapshot Replication. When is the best time to use snapshot replication?
- Your data changes infrequently or you have a set of static tables such as lookup tables.
- You have a small amount of data with which to replicate.
- You make a great deal of changes over short periods of time. For example, you have a database that only gets updated once a year like for open enrollment.
One of the caveats to snapshot replication is the snapshot folder permissions. The replication agents must have the appropriate permissions to read and write to the snapshot folder.
Another caveat is that you can only compress the snapshot if it is less than 2GB in size.
Stay tuned and enjoy tomorrow as we configure the snapshot replication!
SQL Saturday #192 Tampa
We interrupt our series on replication, to remind everyone that next week is SQL Saturday Tampa. Our Tampa user groups are proud to present a stellar lineup and three pre-con trainings this year focusing on DBA, BI, and professional development. The pre-cons are as follows:
- For the DBAs: SQL Server Internals from the practical angle by Dmitri Korotkevitch
-
For the BI enthusiasts: Taking the Next Step with Reporting Services by Jessica Moss
- For Professional Development: Creating Your Best Technical Presentation: A Speaker Workshop by Buck Woody
Keep in mind that we have moved to Hillsborough Community College Ybor Campus to accommodate our recent growth and to allow us to host more speakers featuring eight tracks this year with 46 sessions lined up.
If you have not registered yet, what are you waiting on? Register now and avoid the rush!
Replication Series, Part Three: Preparation
Continuing our series on replication, today we look at the steps to prepare for replication found within SQL Server.
The first step is to ensure that you have replication installed on each of the nodes that you wish to use replication. One good way to determine if you have it installed is to run the Installed SQL Server features discovery report from the tools menu of the SQL Server Installation Center. If you do not see the feature installed then you will need to install it.
Figure 1 – Discovery Report
Stay tuned and enjoy!
Replication Series, Part Two: Replication Types
Continuing our series on replication, today we look at the different types of replication found within SQL Server.
Within the replication realm we have four different forms of replication:
- Transactional replication: transactions are updated as soon as they happen (near real time) between the publisher and subscriber.
- Snapshot replication: replicates data at a point-in-time, typically used for data that does not change often. Completes refreshes of data entirely instead of incrementally.
- Peer-to-Peer replication: similar to transactional replication, replicates data to multiple server instances or nodes. Changes occur as they happen and allows for data redundancy.
- Merge replication: similar to transactional replication, but changes are made via synchronization triggers when the nodes are reconnected.
Stay tuned and enjoy!
Replication Series, Part One: The Beginning
Fresh back from a week of intense training in the SQLskills.com Immersion Event Two, I thought I would start a new learning series on replication. Today we will start with the basics, definitions.
- Publisher: A database instance that publishes data to other instances.
- Subscriber: A database instance that receives publications.
- Article: A published object which can be a table, view, user-defined function, indexed view, or stored procedure.
- Publication: A collection of articles to be published.
- Distributor: A database instance that manages the distribution between publisher and subscriber.
- Local Distributor: A distributor located on the same LAN as the publisher.
- Remote Distributor: A distributor located on a remote network from the publisher.
- Agent: A service enabling replication tasks such as publishing, distributing, and subscribing.
Stay tuned and enjoy!
Need to Predict Backup Sizes?
Someone showed me a neat trick a little while ago that I thought I would pass on. The problem is I wrote down the trick but not who told it to me, so if you are reading this and you think it was you who told me please let me know and I will cite you properly. I apologize for this transgression but I thought the tip was appropriate given my recent posts concerning backup and recovery and I was searching through some old notes looking for something to blog about tonight.
Have you ever added a new database to your server and thought to yourself, hey do I have enough space on my backup drive to cover however many days you are backing up? Here is an easy way to get a general idea for an uncompressed backup size. If you are running compression, then it will obviously be less but this is an estimate.
USE <Database Name>
GO
EXEC sp_spaceused @updateusage = ‘TRUE’
Which for a particular database on one of my servers it returned:
| database_name | database_size | unallocated_space | |
| <Database Name> | 2449.81 MB | 40.45 MB | |
| reserved | data | index_size | unused |
| 2396472 KB | 2386856 KB | 5144 KB | 4472 KB |
The actual uncompressed backup for this database was 2,317,543 KB and the reserved data from the stored procedure was 2,396,472 which is pretty close to the actual backup. Not bad, huh? I hope this helps.
Exceptional DBA, the Song!
My apologies to Lynyrd Skynyrd, although I haven’t exactly forgiven them for writing a song about Alabama when they are from Florida!
My mentor told me when I was a Junior
Come sit beside me, my only one
And listen closely to what I say.
And if you do this
It’ll help you some sunny day. Oh Yah!
Oh, take your time… Don’t code too fast,
Wait stats will come and they will pass.
You’ll find an execution plan, you’ll find performance,
And don’t forget Junior,
There is someone up above.(Chorus)
And be an exceptional, kind of DBA
Oh T-SQL, you will love and understand.
Baby be an exceptional, kind of DBA
Oh, won’t you do this for me Junior,
If you can?Forget your lust, for the BIG DATA
All that you need, is in your soul,
And you can do this, oh baby, if you try.
All that I want for you my Junior,
Is to be satisfied.(Chorus)
DBA, don’t you worry… you’ll find TechNet.
Follow the SQL PASS and nothing else.
And you can do this if you try.
All that I want for you my Junior
Is to be satisfied.(Chorus)
Baby be an exceptional, be an exceptional DBA
Oh T-SQL, you will love and understand.
Baby be an exceptional, kind of DBA
SQL Features Discovery Report
While going through my preparation for the MCSA exam I stumbled upon this gem that I always seem to forget about so I thought I would pass it along to you. For a quick an easy way to determine what is installed on an instance of SQL Server, run the SQL Server Installation Center and select tools. Then select Installed SQL Server features discovery report, as shown in Figure 1. This will produce a nice report as shown in Figure 2.
Enjoy!
Figure 1 – SQL Server Installation Center
Figure 2 – Setup Discovery Report
SQL Swampland 