|
|
You're in the
sql server replication
group:Need Help/Advice with Snapshot Backup API
sql server replication:
Please excuse this message if it is a little simplistic, but I'm very new to the concept of using the Snapshot API and SQL Snapshots in general. I'm after some information or advice regarding the API which is used to pause/resume the SQL Server for the purpose of carrying out a Snapshot backup. I'm currently working on a hardware platform which is capable of carrying out snapshot backups (SAN) and would like to develop an application which can pause/resume the server using the API commands provided in the SQL backup SDK. Is this something that I need to develop, or is this functionality already provided by some part of SQL which I haven't yet discovered? I've been hunting around the web and the newsgroups, and have been left a little confused as to whether this is something that I need to develop at all, as my impression is that this can be automated possibly in some other way without having to code an app to do it. Any help would be greatly appreciated! Regards,
Which SAN are you using ? We use EMC and we use Business Continuity
Volumes. For this to be effective EMC has to Freeze then Thaw the SQL Server IO. It does this through it's own software. Can you elaborate on what you are doing and how it relates to Replication ? -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org [quoted text, click to view] "Emil Nadinic" <quake_dude@hotmail.com> wrote in message news:6024dc11.0312281559.2a3aee5e@posting.google.com... > Hello All, > > Please excuse this message if it is a little simplistic, but I'm very > new to the concept of using the Snapshot API and SQL Snapshots in > general. > > I'm after some information or advice regarding the API which is used > to pause/resume the SQL Server for the purpose of carrying out a > Snapshot backup. I'm currently working on a hardware platform which is > capable of carrying out snapshot backups (SAN) and would like to > develop an application which can pause/resume the server using the API > commands provided in the SQL backup SDK. > > Is this something that I need to develop, or is this functionality > already provided by some part of SQL which I haven't yet discovered? > I've been hunting around the web and the newsgroups, and have been > left a little confused as to whether this is something that I need to > develop at all, as my impression is that this can be automated > possibly in some other way without having to code an app to do it. > > Any help would be greatly appreciated! > > Regards, > > Emil Nadinic.
Hi Allan,
Firstly, thanks for the reply. We're running a Hitachi San, I'm not sure of the actual model, as its all in place and running, and I've just started in this particular role. My manager has basically said that the san supports Snapshot backups, and that he wants me to develop an app which will "Freeze and Thaw" the transactions to the server so we can perform a snapshot. From my understanding, they've developed all the other scripts needed to do the work, the only component missing is something to issue a pause and resume to the SQL server. I know Hitachi sell a package which does all this, but its rather expensive, and they've already got 90% of the functionality in place (just missing this bit), hence my need to develop this component. Hopefully this all makes sense. We run a backup server, with an additional server in a Disaster recovery site, so my understanding is that this app is all they require to finish off their solution. Again, I've come into this one after its all been set up, so my focus is whether this application is going to be a feasable option or not! Thanks Kindly, Emil. [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:<O05gAwezDHA.3196@TK2MSFTNGP11.phx.gbl>...
> Which SAN are you using ? We use EMC and we use Business Continuity > Volumes. For this to be effective EMC has to Freeze then Thaw the SQL Server > IO. It does this through it's own software. > > Can you elaborate on what you are doing and how it relates to Replication ? > > -- > > ---------------------------- > Allan Mitchell (Microsoft SQL Server MVP) > MCSE,MCDBA > www.SQLDTS.com > I support PASS - the definitive, global community
You are going to have to somehow PAUSE SQL Server.
You can use NET PAUSE <<service name>> I have never done this and am unsure of the implications. Applications will not be able to connect to SQL Server during this period so you will need to make sure it is an "Off" period. After the snapshot has been taken you can issue NET CONTINUE <<service name>> Our SAN server snapshots take ~3secs for 450GB This may be the way EMC do it as well. Some applications have spiralled out of control when the thaw didn't happen as expected but that was remedied easily. I would suggest you will need a lot of testing. If you can guarantee the time this is done will be devoid of activity then you can easily STOP SQL Server and then issue a RESTART. This will cause transactions and applications to be exited gracefully with reliable error messages that mean something. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org [quoted text, click to view] "Emil Nadinic" <quake_dude@hotmail.com> wrote in message news:6024dc11.0312301500.a434b7f@posting.google.com... > Hi Allan, > > Firstly, thanks for the reply. > > We're running a Hitachi San, I'm not sure of the actual model, as its > all in place and running, and I've just started in this particular > role. My manager has basically said that the san supports Snapshot > backups, and that he wants me to develop an app which will "Freeze and > Thaw" the transactions to the server so we can perform a snapshot. > From my understanding, they've developed all the other scripts needed > to do the work, the only component missing is something to issue a > pause and resume to the SQL server. I know Hitachi sell a package > which does all this, but its rather expensive, and they've already got > 90% of the functionality in place (just missing this bit), hence my > need to develop this component. Hopefully this all makes sense. > > We run a backup server, with an additional server in a Disaster > recovery site, so my understanding is that this app is all they > require to finish off their solution. > > Again, I've come into this one after its all been set up, so my focus > is whether this application is going to be a feasable option or not! > > Thanks Kindly, > > Emil. > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:<O05gAwezDHA.3196@TK2MSFTNGP11.phx.gbl>... > > Which SAN are you using ? We use EMC and we use Business Continuity > > Volumes. For this to be effective EMC has to Freeze then Thaw the SQL Server > > IO. It does this through it's own software. > > > > Can you elaborate on what you are doing and how it relates to Replication ? > > > > -- > > > > ---------------------------- > > Allan Mitchell (Microsoft SQL Server MVP) > > MCSE,MCDBA > > www.SQLDTS.com > > I support PASS - the definitive, global community > > for SQL Server professionals - http://www.sqlpass.org
Hi Allan,
Microsoft has a "Microsoft SQL Server Virutal backup Device Specification" SDK which details how you can create a virtual snapshot device in memory, which provides the fastest snapshot method.. As part of the SDK, they ship some API calls which have the potential to "freeze" and "thaw" the transaction process, without interrupting the running process whatsoever. How this works internally within SQL I'm not sure, but my understanding was that this API could be used to issue the Freeze and Thaw commands. I guess what I'm hoping to be able to do is to write a small application which can do this. the sample code they supply with the SDK doesn't specifically show the freeze and thaw process, as it seems to be part of the Snapshot flow.. The only example I've seen of someone being able to do this is the Hitachi quicksnap.exe application, which they charge like wounded bulls for. I'm guessing that no one outside of them has done anything similar - there certainly seems to be a lack of information on the web about the specifics of the API calls, from what I can see. Thanks! Regards, Emil. [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:<enV6oHr0DHA.2856@TK2MSFTNGP09.phx.gbl>...
> You are going to have to somehow PAUSE SQL Server. > > You can use NET PAUSE <<service name>> > > I have never done this and am unsure of the implications. Applications will > not be able to connect to SQL Server during this period so you will need to > make sure it is an "Off" period. > After the snapshot has been taken you can issue NET CONTINUE <<service > name>> > > Our SAN server snapshots take ~3secs for 450GB > > This may be the way EMC do it as well. Some applications have spiralled out > of control when the thaw didn't happen as expected but that was remedied > easily. > > I would suggest you will need a lot of testing. > > If you can guarantee the time this is done will be devoid of activity then > you can easily STOP SQL Server and then issue a RESTART. This will cause > transactions and applications to be exited gracefully with reliable error > messages that mean something. > -- > -- > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > www.allisonmitchell.com - Expert SQL Server Consultancy. > www.SQLDTS.com - The site for all your DTS needs. > I support PASS - the definitive, global community
Ahhh good. I learnt something new today. Perhaps EMC also use this API.
Let me see if I can't find an example. -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org [quoted text, click to view] "Emil Nadinic" <quake_dude@hotmail.com> wrote in message news:6024dc11.0401041622.59f70e04@posting.google.com... > Hi Allan, > > Microsoft has a "Microsoft SQL Server Virutal backup Device > Specification" SDK which details how you can create a virtual snapshot > device in memory, which provides the fastest snapshot method.. As part > of the SDK, they ship some API calls which have the potential to > "freeze" and "thaw" the transaction process, without interrupting the > running process whatsoever. How this works internally within SQL I'm > not sure, but my understanding was that this API could be used to > issue the Freeze and Thaw commands. I guess what I'm hoping to be able > to do is to write a small application which can do this. the sample > code they supply with the SDK doesn't specifically show the freeze and > thaw process, as it seems to be part of the Snapshot flow.. The only > example I've seen of someone being able to do this is the Hitachi > quicksnap.exe application, which they charge like wounded bulls for. > I'm guessing that no one outside of them has done anything similar - > there certainly seems to be a lack of information on the web about the > specifics of the API calls, from what I can see. > > Thanks! > > Regards, > > Emil. > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:<enV6oHr0DHA.2856@TK2MSFTNGP09.phx.gbl>... > > You are going to have to somehow PAUSE SQL Server. > > > > You can use NET PAUSE <<service name>> > > > > I have never done this and am unsure of the implications. Applications will > > not be able to connect to SQL Server during this period so you will need to > > make sure it is an "Off" period. > > After the snapshot has been taken you can issue NET CONTINUE <<service > > name>> > > > > Our SAN server snapshots take ~3secs for 450GB > > > > This may be the way EMC do it as well. Some applications have spiralled out > > of control when the thaw didn't happen as expected but that was remedied > > easily. > > > > I would suggest you will need a lot of testing. > > > > If you can guarantee the time this is done will be devoid of activity then > > you can easily STOP SQL Server and then issue a RESTART. This will cause > > transactions and applications to be exited gracefully with reliable error > > messages that mean something. > > -- > > -- > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > > www.allisonmitchell.com - Expert SQL Server Consultancy. > > www.SQLDTS.com - The site for all your DTS needs. > > I support PASS - the definitive, global community > > for SQL Server professionals - http://www.sqlpass.org
Hi Allan,
yes, I most certainly have. Unfortunately I'm not a C++ coder, so the samples are tantalisingly close to what I need, but I can't quite understand how the freeze and unfreeze components are called! If I can capture the api components that need to be called, I've basically got my solution. Kindest Regards, Emil. [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:<O2KaF8H1DHA.1744@TK2MSFTNGP12.phx.gbl>...
> Have you looked at this page > > http://www.microsoft.com/sql/downloads/virtualbackup.asp > > -- > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > www.allisonmitchell.com - Expert SQL Server Consultancy. > www.SQLDTS.com - The site for all your DTS needs. > I support PASS - the definitive, global community > for SQL Server professionals - http://www.sqlpass.org >
Have you looked at this page
http://www.microsoft.com/sql/downloads/virtualbackup.asp -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:%23w8WfN30DHA.2568@TK2MSFTNGP09.phx.gbl... > Ahhh good. I learnt something new today. Perhaps EMC also use this API. > Let me see if I can't find an example. > > -- > > ---------------------------- > Allan Mitchell (Microsoft SQL Server MVP) > MCSE,MCDBA > www.SQLDTS.com > I support PASS - the definitive, global community > for SQL Server professionals - http://www.sqlpass.org > > > > "Emil Nadinic" <quake_dude@hotmail.com> wrote in message > news:6024dc11.0401041622.59f70e04@posting.google.com... > > Hi Allan, > > > > Microsoft has a "Microsoft SQL Server Virutal backup Device > > Specification" SDK which details how you can create a virtual snapshot > > device in memory, which provides the fastest snapshot method.. As part > > of the SDK, they ship some API calls which have the potential to > > "freeze" and "thaw" the transaction process, without interrupting the > > running process whatsoever. How this works internally within SQL I'm > > not sure, but my understanding was that this API could be used to > > issue the Freeze and Thaw commands. I guess what I'm hoping to be able > > to do is to write a small application which can do this. the sample > > code they supply with the SDK doesn't specifically show the freeze and > > thaw process, as it seems to be part of the Snapshot flow.. The only > > example I've seen of someone being able to do this is the Hitachi > > quicksnap.exe application, which they charge like wounded bulls for. > > I'm guessing that no one outside of them has done anything similar - > > there certainly seems to be a lack of information on the web about the > > specifics of the API calls, from what I can see. > > > > Thanks! > > > > Regards, > > > > Emil. > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > news:<enV6oHr0DHA.2856@TK2MSFTNGP09.phx.gbl>... > > > You are going to have to somehow PAUSE SQL Server. > > > > > > You can use NET PAUSE <<service name>> > > > > > > I have never done this and am unsure of the implications. Applications > will > > > not be able to connect to SQL Server during this period so you will need > to > > > make sure it is an "Off" period. > > > After the snapshot has been taken you can issue NET CONTINUE <<service > > > name>> > > > > > > Our SAN server snapshots take ~3secs for 450GB > > > > > > This may be the way EMC do it as well. Some applications have spiralled > out > > > of control when the thaw didn't happen as expected but that was remedied > > > easily. > > > > > > I would suggest you will need a lot of testing. > > > > > > If you can guarantee the time this is done will be devoid of activity > then > > > you can easily STOP SQL Server and then issue a RESTART. This will > cause > > > transactions and applications to be exited gracefully with reliable > error > > > messages that mean something. > > > -- > > > -- > > > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > > > www.allisonmitchell.com - Expert SQL Server Consultancy. > > > www.SQLDTS.com - The site for all your DTS needs. > > > I support PASS - the definitive, global community > > > for SQL Server professionals - http://www.sqlpass.org > >
I am neither a C++ coder. I am assured though that this is what you want.
-- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org [quoted text, click to view] "Emil Nadinic" <quake_dude@hotmail.com> wrote in message news:6024dc11.0401061617.b883ed6@posting.google.com... > Hi Allan, > > yes, I most certainly have. Unfortunately I'm not a C++ coder, so the > samples are tantalisingly close to what I need, but I can't quite > understand how the freeze and unfreeze components are called! If I can > capture the api components that need to be called, I've basically got > my solution. > > Kindest Regards, > > Emil. > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:<O2KaF8H1DHA.1744@TK2MSFTNGP12.phx.gbl>... > > Have you looked at this page > > > > http://www.microsoft.com/sql/downloads/virtualbackup.asp > > > > -- > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > > www.allisonmitchell.com - Expert SQL Server Consultancy. > > www.SQLDTS.com - The site for all your DTS needs. > > I support PASS - the definitive, global community > > for SQL Server professionals - http://www.sqlpass.org > > > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |