August 25, 2011 at 12:40 pm
We've been using this for about a year, and I think we have more luck with the product. We're using it for backup/restores naturally, but also take advantage of the Cloning to other machines, verifying on other machines, etc. Also, I've created a script in PowerShell that mounts the last backup for an index maintenance scan before actually performing the index maintenance on the primary data.
It's quirky to be sure, and we've encountered many of the same issues, but it has advantages that we now rely on.
August 31, 2011 at 11:50 pm
We also use SnapManager for backups and restores and clones. And Document Server volume snaps.
We now find that when the databases are frozen it may take several minutes for the snapshot to complete. We do have quite a few databases on the LUN. In fact about 90 databases. (Before any guffawing we have no choice but to use SnapManager and we inherited the applications as is.).
I am tending towards what has been written - ie 25 to 35 db per LUN. Now we also have some big indexes and I was tending towards putting the indexes onto their own LUN.
Any thoughts
September 1, 2011 at 8:06 am
Raymond0 (8/31/2011)
We also use SnapManager for backups and restores and clones. And Document Server volume snaps.We now find that when the databases are frozen it may take several minutes for the snapshot to complete. We do have quite a few databases on the LUN. In fact about 90 databases. (Before any guffawing we have no choice but to use SnapManager and we inherited the applications as is.).
I am tending towards what has been written - ie 25 to 35 db per LUN. Now we also have some big indexes and I was tending towards putting the indexes onto their own LUN.
Any thoughts
Raymond0,
The limitations isn't based off a solid number. The limitation is based off a rough estimate of how long SMSQL will need to quiesce the system to complete the snapshot. The length is normally based off the total size of the volume. I'm actually surprised that if the volume is frozen for that long that SQL Server doesn't lose connection to your databases. I had always assumed anything greater than about 30 seconds would cause the databases to go suspect.
My recommendation would be to allocate additional LUNs to separate volumes and move even distribute the datafiles across the volumes. Snapshots are completed at the volume level so if you can arrange to maybe spread your database files across 3 or 4 volumes then you'll limit the total amount of time the system will freeze. I would also think about putting the log files on a separate volume than your datafiles.
In summary, if you have a bunch of small databases then put them on a single volume. If you have 1 or 2 very large databases then I would definitely recommend putting those on their own separate volumes. Keep in mind that since you have multiple databases on a single volume your restores will be streaming and could actually take as long or longer than a native restore.
September 22, 2011 at 10:07 am
scott.shaw (7/12/2011)
I've posted my blog. I hope it helps.
Yep, it helped feed my growing unease for implementing SMSQL.
We're currently in the throes of carving up an aggregate for 4 db servers and I gotta say that there are no warm and fuzzies after reading through this thread. Nevertheless, I have a couple questions regarding configuration.
1. Putting the TempDb on it's own volume. It was my understanding that the software snapshots all volumes so is this recommendation strictly to keep TempDb from being intermingled with UserDb snapshots? Since system dbs don't get snapshoted, what's the drawback of putting TempDb on the same volume?
2. Two of our servers house single 4-5TB dbs. Both are made up of multiple filegroups so we're leaning toward creating separate volumes for each filegroup. Make sense?
Any other observations using SMSQL with dbs in the multi-TB range would be appreciated.
_____________________________________________________________________
- Nate
September 22, 2011 at 12:49 pm
RP_DBA (9/22/2011)
scott.shaw (7/12/2011)
I've posted my blog. I hope it helps.Yep, it helped feed my growing unease for implementing SMSQL.
We're currently in the throes of carving up an aggregate for 4 db servers and I gotta say that there are no warm and fuzzies after reading through this thread. Nevertheless, I have a couple questions regarding configuration.
1. Putting the TempDb on it's own volume. It was my understanding that the software snapshots all volumes so is this recommendation strictly to keep TempDb from being intermingled with UserDb snapshots? Since system dbs don't get snapshoted, what's the drawback of putting TempDb on the same volume?
2. Two of our servers house single 4-5TB dbs. Both are made up of multiple filegroups so we're leaning toward creating separate volumes for each filegroup. Make sense?
Any other observations using SMSQL with dbs in the multi-TB range would be appreciated.
Yes, you can put tempdb on the same volume as your system drive. But, there are 2 downsides. 1. tempdb could grow and fill the drive; and, 2. If your server team is taking snapshots of the volume, tempdb could grow and you will be wasting space taking a snapshot of tempdb. We put it on the a separate volume so it would be excluded from any server-level snapshots.
As far as your second point - my initial thought is you will want all the filegroups associated with one database on the same volume. This way you get a single, consistent snapshot of the whole database. What you don't want are .mdf, .ndf, or .ldf files from different databases on the same volume - especially for large databases. This is because the snap may be quick but the restore will be streaming and take a very long time.
Sorry to add to your unease. I do think SMSQL could be useful for VLDBs so you may be on the right track. I would still not recommend it for managing more than 20 SQL Servers (rough estimate) large or small.
Good luck!
Scott
September 22, 2011 at 1:05 pm
Once the configuration wizard is run and SMSQL knows where all your datafiles, filegroups, etc are distributed on whatever volume they're on, I believe SMSQL will stop all IO for that DB, take the snapshot on all volumes including all filegroups, so a consistent snapshot would still be taken.
September 22, 2011 at 1:33 pm
Thanks Scott, makes sense.
pjdiller (9/22/2011)
Once the configuration wizard is run and SMSQL knows where all your datafiles, filegroups, etc are distributed on whatever volume they're on, I believe SMSQL will stop all IO for that DB, take the snapshot on all volumes including all filegroups, so a consistent snapshot would still be taken.
Thanks PJDiller for the confirmation - breaking them out won't buy us anything.
_____________________________________________________________________
- Nate
December 4, 2011 at 12:59 am
Scott,
Our company just installed SnapManager for SQL last week. (I have never used the product before, I'm not familiar with their distinction bewteen aggregate, Volume, and LUN, but as DBA I'm responsible for the databses. Wonderful). The installer setup an hourly snapshot job that we see under the Sql Server Agent.
1. I can't figure our for the life of me where the actual hourly snapshot job is stored in SnapManager. I want to "edit" that existing backup job when Ineed to add a new database or remove a database from the server. Is that the right approach or is one supposed to create a new SnapManager backup job everytime a database is added or removed? (I hope not).
2. Can I install SnapDrive and SnapManager For Sql on my local workstation and apply it my local sql VM with multiple drives? (None of them are SAN LUNS just Hyper-V disks.). I need a "learning" environment where I can play with SnapManager I don't feel comfortable learning on our production system!
3. I was told that we need SnapManager For Sql because it quiesces the databases before taking a snapshot. That guarantees the snapshots are consistent. I was also told that SnapDrive (?) takes snapshots but, on its own, will not guarantee consistent snapshots. Does that sound right to you?
TIA,
"Lost in NetAppLand" -- Barkingdog
P.S. We are running sql 2008 but I was so glad to see anyone writing about the product that I dropped into this forum.
December 5, 2011 at 6:38 am
Barkingdog (12/4/2011)
Scott,Our company just installed SnapManager for SQL last week. (I have never used the product before, I'm not familiar with their distinction bewteen aggregate, Volume, and LUN, but as DBA I'm responsible for the databses. Wonderful). The installer setup an hourly snapshot job that we see under the Sql Server Agent.
1. I can't figure our for the life of me where the actual hourly snapshot job is stored in SnapManager. I want to "edit" that existing backup job when Ineed to add a new database or remove a database from the server. Is that the right approach or is one supposed to create a new SnapManager backup job everytime a database is added or removed? (I hope not).
2. Can I install SnapDrive and SnapManager For Sql on my local workstation and apply it my local sql VM with multiple drives? (None of them are SAN LUNS just Hyper-V disks.). I need a "learning" environment where I can play with SnapManager I don't feel comfortable learning on our production system!
3. I was told that we need SnapManager For Sql because it quiesces the databases before taking a snapshot. That guarantees the snapshots are consistent. I was also told that SnapDrive (?) takes snapshots but, on its own, will not guarantee consistent snapshots. Does that sound right to you?
Barkingdog - I'm by no means as knowledgeable as Scott on this but have some experience so I'll try to answer as best as possible.
1. I don't believe it is stored in SnapManager and if it is I don't think it matters. From what I have found you have to walk through the Configuration manager when you add / remove a database. I posted something on the NetApp forum about this to see if there was a way that you could avoid this and have a potential workaround but it is not on the top of my priority list at the moment. HERE is the thread.
2. I would be pretty sure that the answer here is no as it would need to be able to communicate with the SAN controller to mark the blocks associated with that snapshot but I may be incorrect.
3. Yes that sounds right to me.
Again, I'm not the authority here so hopefully Scott will be able to confirm what I have stated.
Enjoy your NetApp experience. 🙂 Seriously though they do have some tool woes when considering larger environments from my perspective but they also seem pretty responsive to working through those things. I'm hopeful that SnapManager will be one of those items that they are working to improve.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 5, 2011 at 7:55 am
Barkingdog (12/4/2011)
1. I can't figure our for the life of me where the actual hourly snapshot job is stored in SnapManager. I want to "edit" that existing backup job when Ineed to add a new database or remove a database from the server. Is that the right approach or is one supposed to create a new SnapManager backup job everytime a database is added or removed? (I hope not).
IIRC the wizard for setting up the job creates the job in SQL, it isn't stored anywhere in SnapManager.
The switches on the job in SQLAgent control which databases are done, I think there was one which was just the number of databases, and another which listed the names if you weren't backing up all the user db's in one job.
My setup had system db's on one LUN, tempDB on another, main production DB on it's own on another, and finally one containing all other databases.
Worked well for us but glad the setup wasn't any bigger.
Remember the front end being utterly terrible though!
December 5, 2011 at 9:40 am
Barkingdog (12/4/2011)
Scott,1. I can't figure our for the life of me where the actual hourly snapshot job is stored in SnapManager. I want to "edit" that existing backup job when Ineed to add a new database or remove a database from the server. Is that the right approach or is one supposed to create a new SnapManager backup job everytime a database is added or removed? (I hope not).
I blogged a dynamic backup script for SnapManager for SQL a few months ago, this may help you achieve what you're after:
NetApp Dynamic Backup Script[/url]
Worst case, as others have mentioned you can add/remove the database names and amend the count from the list of DBs to be backed up.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
December 5, 2011 at 1:15 pm
Barkingdog (12/4/2011)
Scott,Our company just installed SnapManager for SQL last week. (I have never used the product before, I'm not familiar with their distinction bewteen aggregate, Volume, and LUN, but as DBA I'm responsible for the databses. Wonderful). The installer setup an hourly snapshot job that we see under the Sql Server Agent.
1. I can't figure our for the life of me where the actual hourly snapshot job is stored in SnapManager. I want to "edit" that existing backup job when Ineed to add a new database or remove a database from the server. Is that the right approach or is one supposed to create a new SnapManager backup job everytime a database is added or removed? (I hope not).
2. Can I install SnapDrive and SnapManager For Sql on my local workstation and apply it my local sql VM with multiple drives? (None of them are SAN LUNS just Hyper-V disks.). I need a "learning" environment where I can play with SnapManager I don't feel comfortable learning on our production system!
3. I was told that we need SnapManager For Sql because it quiesces the databases before taking a snapshot. That guarantees the snapshots are consistent. I was also told that SnapDrive (?) takes snapshots but, on its own, will not guarantee consistent snapshots. Does that sound right to you?
TIA,
"Lost in NetAppLand" -- Barkingdog
P.S. We are running sql 2008 but I was so glad to see anyone writing about the product that I dropped into this forum.
Other users already answered some these but I'll take another gander at them just to be sure.
1. Only the job configuration is stored in SMSQL (everything in SMSQL is stored in a flatfile - this is why it is soooo slow). The actual job is a SQL agent job stored in MSDB. There is a disconnect between SMSQL and MSDB meaning one does not automatically update the other. You can create an SQL job which will generate a script at runtime to include any added databases or remove deleted databases for SnapManager backup (see link in the other post) but SMSQL will have no knowledge of this. The only way to update SMSQL is to rerun the configuration utility each time a database is added or deleted. For my shop this was unacceptable and was one of the reasons we ultimately ended up rejecting SMSQL as an Enterprise solution.
2. As far as I know you can but I don't have much experience setting up the SnapDrive portion. What we did was have the storage team create LUNs and then we created test VMs. We installed SnapDrive and SMSQL on the VMs and tested from there.
3. Let's be honest here. The storage team needs SMSQL because it uses NetApp technology (which they paid for) to backup SQL Server databases. As a SQL DBA, you don't need SMSQL. It is true that the benefit of using SMSQL is that it will quiesce the databases for snapshots but this is only beneficial for the storage team. You will find that as a DBA this can cause issues both in management and design. The storage team likes it because it uses the same technology to backup databases that they use to backup file servers and anything else. The storage team does not understand how this affects you as a DBA. One of your jobs going forward will be to educated them. Snapshots may not be applicable in all situations and certainly the SMSQL tool used to manage these backups may be completely prohibitive.
Let me know if you need anything and good luck! Also, I wanted to point to my blog entry talking about why we've rejected SMSQL at our company. Hopefully, you can avoid some of the pitfalls we went through.
http://blogofshaw.blogspot.com/2011/07/skinny-on-snapmanager-for-sql-server.html
Note: NetApp originally indicted some desire to work with us to improve their product but they have never followed through.
Thanks,
Scott
December 5, 2011 at 10:12 pm
Scott,
Your replies are numbered. Mine are indented.
1. Only the job configuration is stored in SMSQL (everything in SMSQL is stored in a flatfile - this is why it is soooo slow). The actual job is a SQL agent job stored in MSDB. There is a disconnect between SMSQL and MSDB meaning one does not automatically update the other. You can create an SQL job which will generate a script at runtime to include any added databases or remove deleted databases for SnapManager backup (see link in the other post) but SMSQL will have no knowledge of this. The only way to update SMSQL is to rerun the configuration utility each time a database is added or deleted. For my shop this was unacceptable and was one of the reasons we ultimately ended up rejecting SMSQL as an Enterprise solution.
>>> So I guess my idea of finding the SMSQL job and editing it won't go. I need to re-run the configuration
wizard to generate a new job instead.
I just found this on page 3 of the SnapManager 5.1 Installation and Configuration guide:
SnapManager for SQL Server now discovers newly created databases:
A scheduled backup job will automatically detect a new database without
needing to rerun the Configuration wizard, if the the following requirements are
met:
? All databases are selected to be backed up.
? The newly created database is in a valid configuration for SnapManager for
SQL Server to back it up.
? The SnapManager for SQL Server configuration remains the same when the
new database is created. For example, if a database was not originally
configured to share a LUN with other databases, the database should not
share a LUN with other databases after the new database is added.
3. Let's be honest here. The storage team needs SMSQL because it uses NetApp technology (which they paid for) to backup SQL Server databases. As a SQL DBA, you don't need SMSQL. It is true that the benefit of using SMSQL is that it will quiesce the databases for snapshots but this is only beneficial for the storage team.
>>> Why do you say that? I thought if one did not quiesce the databases then it is highly
unlikely that the database snapshots will be valid. That seems benefical to both the
DBA and storgae team. (Maybe I don't understand what NetApps means by quiescing.)
You will find that as a DBA this can cause issues both in management and design.
>>> What issue(s) are you referring to? Again, it would seem the quiescing the DB is the right
thing todo from both the DBA and Storage Admin sides.
....and certainly the SMSQL tool used to manage these backups may be completely prohibitive.
>>> I have to read more of your writings to understand why you say that. SMSQL seems, to put it
mildly, a clunky tool at best, but I hope it snapshots are of use.
I think the main value of SMSQL and these snapshots is that they can be taken frequently with minimal
impact on the sql server and the snapshots can "easily" be ported to our remote DR office.
Hopefully, you can avoid some of the pitfalls we went through.
http://blogofshaw.blogspot.com/2011/07/skinny-on-snapmanager-for-sql-server.html
>>>> I defintiely will read your blog tonight.
One thing I definitely dislike about SMSQL is that each of our hourly snapshots truncates the
transaction log (don't know why it has to be that way). That just kills point-in-time recovery
which can ba a very valuable feature.
Barkingdog
December 5, 2011 at 10:31 pm
Scott,
Just read your article:
http://blogofshaw.blogspot.com/2011/07/skinny-on-snapmanager-for-sql-server.html
Oy vey is my summary.
Because the whole NetApp SMSQL setup is unfamiliar to me I am still using our RedGate backup to backup the databases nightly. (Especially for transferring DBs to another sql server that does not have SMSQL installed.) The resulting backups can also be copied to tape for longer term retention. I thought at one time that RedGate backup may be replaced by SMSQL but I'm not so sure now.
Barkingdog
December 5, 2011 at 11:40 pm
Scott,
I just looked at the sql server agent job. Her it is!
>>>
C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe new-backup –svr '<ServerName>'
-RetainBackupDays 3 -lb -bksif -RetainSnapofSnapInfo 8 -updmir –mgmt
standard
>>>
I expected to see an enumeration of all the databses it would backup! No show.
You mentioned that only the job configuration is stored in SMSQL (everything in SMSQL is stored in a flatfile - this is why it is soooo slow). What is the name\location of that file?
TIA,
Barkingdog
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply