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
Restoration Options
Today I was asked what the difference was in some of the restoration options. Thus, I thought this would make a good blog post as it is somewhat confusing in the beginning.
On the options page of the restore database interface in SQL Server Management Studio, there are several options than can be quite frightening to new DBAs. I have tried to explain them here in the order that they appear on the screen:
- Overwrite the existing database (WITH REPLACE): This is a scary option as you should always make sure that you REALLY want to overwrite an existing database. Proceed with caution. This option overwrites a safety check that prevents you from overwriting database X with database Y.
- Preserve the replication settings (WITH KEEP_REPLICATION): This option is really only relevant if the database was replicated when the backup was created. I do not have a strong background in replication so I don’t have much to add to this item.
- Prompt before restoring each backup: This essentially will let you pause a restore with a dialog box prompt when moving between media sets. Unless your backup is stored across multiple tapes, this is probably of little use to you. This might be helpful if you would like to break up the restore into smaller pieces, but I am not sure that is practical.
- Restrict access to the restored database (WITH RESTRICTED_USER): This option will make your restored database only available to the members of sysadmin, dbcreator, or db_owner roles. This might be helpful if you need to perform additional steps before making the database available to the users to keep them from accidentally jeopardizing some scripts you need to run post restore.
I hope this helps your understanding. Stay tuned for tomorrow where we discuss the recovery state options. Enjoy!
Moving SSRS Reports
Yesterday I was tasked with migrating our Citrix EdgeSight database off of a 2005 SQL Server and migrating it to a new 2008R2 Server. No problem, I have done that a million times before. But wait a minute, that database has SSRS reports installed on that server and not our main reporting server. If you follow my blog, you will probably see that I have not had much experience with SSRS, my reporting experience has been with Crystal and other minor packages.
I started researching how to migrate these reports and was not pleased to see Citrix’s response. They recommended migrating the ReportServer and ReportServerTempDB databases to the new server and then reinstalling SSRS on the original server. So what happens then if you have other jobs on the new and old servers? This is not a good recommendation. After an exhaustive search the only option I saw within the tool was to download reports one at a time and upload them into the new server. This was also not an option with a fair amount of reports to migrate.
Enter Reporting Services Scripter from SQLdbatips.com. Run the program on the original server and select your Report folder and scripts are generated and placed into a folder of your choosing. These scripts can then be copied over to the new server and executed by command shell. In this particular instance we had to reopen the data source and enter new credentials. Problem solved, reports migrated successfully.
Enjoy!
SQL Saturday Updates
It is an honor and privilege to be selected to present at SQL Saturday Tampa on March 2, 2013. We will be at a new location this year, Hillsborough Community College in beautiful Ybor City, due to our growth over the last couple of years. In addition, we have three pre-cons this year and I will be attending the “Creating Your Best Technical Presentation: A Speaker Workshop” by Buck Woody (b|t). Hopefully, after this session, my presentation skills will continue to improve before my session on that Saturday. If the winter blues got you down then head down to beautiful Tampa for a warm weekend of SQL learning and register for SQL Saturday #192.
This year I will finally get to attend two other SQL Saturdays that are in the relative vicinities. In the past there was always something going on during those two weekends. As it stands, I am planning on submitting my newest session, Backups: Getting the Most Out of Your Storage, to both of those events. Curious as to what they are?
SQL Saturday Jacksonville (#SQLSat215) will be April 27th and SQL Saturday Atlanta is tentatively scheduled for May 18th. Come on out and join me at all three of those events and maybe one of them will have some SQLKaraoke for the after event! Enjoy!
SQL Server Auditing: A Learning Series Part Nine, Reporting
Continuing our learning series on auditing, today we look at the next logical step: Reporting. We have created audits for the server and for the database as well as being able to query the audits, but what good is an audit if you cannot print a report and hand it to the auditors?
Having been a developer and admin in my past life, you may find it strange that I have never before today created a SQL Server Reporting Services report. Now don’t get me wrong, I have written a boat load of reports in many other formats (crystal, web, actuate, etc) but have never really had the opportunity or the pleasure to write in my preferred environment.
Given the following function, I thought reporting would be a little more difficult:
SELECT * FROM fn_get_audit_file (‘g:\auditing\*’, DEFAULT, DEFAULT)
This arguments for this function are (file_pattern, initial_file_name, audit_file_offset). The file_pattern argument must include a path and a filename but can include a wildcard. The initial_file_name argument must specify the path and name of a specific file in the file set to start reading the records from or the DEFAULT|NULL value. The audit_file_offset argument will beginning reading the first record after the specified offset (which is a field that can be queried with the function).
Now on to the report….
- Open Visual Studio and choose File –> New Project.
- Select Report Server Project Wizard and name the project appropriately.
- Enter your connection information and credentials.
- When prompted to design the query enter the query above as shown in Figure 1.
- Select additional reporting options as your preferences allow and then save the report.
Figure 1 – Report Wizard Query String
This concludes our series on auditing, I hope you found it helpful!
SQL Server Auditing: A Learning Series Part Nine
Continuing our learning series on auditing, today we prepare for a database audit. Just like with the server audit specification, you must setup a new server audit that correlates to this database audit specification (remember, it is a one-to-one relationship).
- Create a new server audit for your database audit as demonstrated in part three of this series.
- In SQL Server Management Studio, select the database for which you plan to audit.
- Then select the Security node followed by right-clicking on Database Audit Specification and select New Database Audit Specification.
- From here, the directions are the same as was demonstrated in part five of this series except here we can select the Object Class of either DATABASE, OBJECT, or SCHEMA. For example if we audit the UPDATE action then we can choose the object upon to audit.
Enjoy and stay tuned.
SQL Swampland 
