Availability while applying a snapshot

In merge replication, if I select to re-initialize the subscriber. Is the database available to the end users while the snapshot is being applied?
The publisher requires a table lock to create the snapshot, while on the
subscriber it depends on the sync type. By default you'll drop the table on
the subscriber and recreate it, which would preclude user availability.
Regards,
Paul Ibison
"ReplGuy" <anonymous@.discussions.microsoft.com> wrote in message
news:E90EEDC1-D397-41D9-B413-D2B1FD10B6E0@.microsoft.com...
> In merge replication, if I select to re-initialize the subscriber. Is the
database available to the end users while the snapshot is being applied?
|||Thanks Paul,
So if I want the users to keep on working, then I should ellect not to re-initialize the database? Is this correct?
|||It depends why you are reinitializing. If it is because the schema has
modified substantially, then it is mandatory and your subscriber users will
not be able to access the table. If there is an addition of a column, then
sp_repladdcolumn is needed rather than reinitialization. As for the
reinitialization, it is usually treated as an out-of-hours procedure because
of this restriction.
HTH,
Paul Ibison
|||well it is available but merge replication by default deletes the data in the existing table and then refreshes it. So for a moment or two (depending on how long it takes to bcp the data in) the table is empty.
While the truncation and refreshing is going on there is some locking.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Availability report

I have a table in Access with fields as follows:

EmpCode
Month_Year
Percentage of Availability (PA)

I have another table called StaffMaster which has the following fields:

EmpCode
EmpName
Dept

In the report, I wish to retrieve and display records as follows:

Month1 Month2 Month3 Month4

EmpCode EmpName PA PA2 PA3 PA4

Can this be accomplished? Please help. Urgent!

ThanksTry this query and use labels for Month1, Month2, etc...

SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.PA, 'PA2' = T2.PA, 'PA3' = T3.PA, 'PA4' = T4.PA
FROM StaffMaster AS SM
LEFT JOIN Table1 AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = 0104
LEFT JOIN Table1 AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = 0204
LEFT JOIN Table1 AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = 0304
LEFT JOIN Table1 AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = 0404

This will return all the records in StaffMaster and the matching records in your other table (which I referred to as Table1), it will return a NULL if no records are found in Table1.|||But I don't have 4 tables. I have only 2 tables, where T1 is the StaffMaster table and T2 is the Availability table.|||The query I posted is only based on 2 tables, it just calls Table1 4 times to get each month's data. SM is the StaffMaster table and T1-T4 are all from the Availability Table. In my example, T1 gets January's data (0104), T2 gets February's data (0204), T3 gets March's data (0304), and T4 gets April's data (0404).

Try running the query (substituting the real table names and the values in the Month_Year column) and see if it produces the results you're looking for.|||I tried running the query, but I get this error.

Synatx error (missing operator) in query expression ".

This is how I changed the query.

"SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.PA, 'PA2' = T2.PA, 'PA3' = T3.PA, 'PA4' = T4.PA " & _
"FROM StaffMaster AS SM " & _
"LEFT JOIN Availability AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = #June 2004# " & _
"LEFT JOIN Availability AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = #July 2004# " & _
"LEFT JOIN Availability AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = #August 2004# " & _
"LEFT JOIN Availability AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = #September 2004#"

Thanks|||What is the data type of the field Month_Year? Maybe your syntax error is in there. If it's a string, try replacing the # with a single quote(').

If you want to post a few rows of sample data from your table, I might be able to help you further.|||The data type of Month_Year is date. Hence the #.

This is from the StaffMaster Table

EmpCode| EmpName| Designation| Discipline |Status

X059| $Dummy-SE1| Structural Engineer| Structure| A|
X060| $Dummy-SE2| Structural Engineer| Structure| A|
X061| $Dummy-SE3| Structural Engineer| Structure| A|
X062| $Dummy-SD1| Structural Drafter| Structure| A|
X063| $Dummy-SD2| Structural Drafter| Structure| A|
X064| $Dummy-SD3| Structural Drafter| Structure| A|
X065| $Dummy-SE1| Specifications Engineer| Specifications| A|

This is from the Availability Table

Empcode| Discipline| Month_Year| Avail| AvailDate| Id|

X059| Structure| June 2004| 0| 01 July 2004| Admin|
X059| Structure| July 2004| 0| 01 August 2004| Admin|
X059| Structure| August 2004| 0| 01 September 2004| Admin|
X060| Structure| August 2004| 0| 01 September 2004| Admin|
X060| Structure| July 2004| 0| 01 August 2004| Admin|
X060| Structure| June 2004| 0| 01 July 2004| Admin|
X060| Structure| July 2004| 48| 17 July 2004| Admin|
X060| Structure| August 2004| 0| 01 September 2004| Admin|
X060| Structure| September 2004| 48| 11 September 2004| Admin|
X060| Structure| October 2004| 0| 01 November 2004| Admin|
X060| Structure| November 2004| 0| 01 December 2004| Admin|
X060| Structure| December 2004| 24| 04 December 2004| Admin|
X060| Structure| January 2005| 46| 01 January 2005| Admin|
X061| Structure| June 2004| 0| 01 July 2004| Admin|
X061| Structure| July 2004| 0| 01 August 2004| Admin|
X061| Structure| August 2004| 0| 01 September 2004| Admin|
X062| Structure| August 2004| 7| 21 August 2004| Admin|

Thanks|||You need to replace the PA's after SELECT with the actual name of the column that you use to represent 'Percentage of Availability' (per your first post).

"SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.{ColumnName}, 'PA2' = T2.{ColumnName}, 'PA3' = T3.{ColumnName}, 'PA4' = T4.{ColumnName} " & _
"FROM StaffMaster AS SM " & _
"LEFT JOIN Availability AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = #June 2004# " & _
"LEFT JOIN Availability AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = #July 2004# " & _
"LEFT JOIN Availability AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = #August 2004# " & _
"LEFT JOIN Availability AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = #September 2004#"|||I replaced the PA after the Select with the actual name of the column. This is how it is now.

"SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.{Avail}, 'PA2' = T2.{Avail}, 'PA3' = T3.{Avail}, " & _
"'PA4' = T4.{Avail} FROM StaffMaster AS SM " & _
"LEFT JOIN Availability AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = #June 2004# " & _
"LEFT JOIN Availability AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = #July 2004# " & _
"LEFT JOIN Availability AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = #August 2004# " & _
"LEFT JOIN Availability AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = #September 2004#"

When I run this query, I get this error.

Malformed GUID. in query expression ''PA=T1{Avail}'.|||Thanks malleyo for the reply. I got it to work. The code is below:

strsql = "TRANSFORM Sum(Availability.Avail) AS SumOfAvail " & _
"SELECT StaffMaster.EmpCode, StaffMaster.EmpName " & _
"FROM StaffMaster INNER JOIN Availability ON StaffMaster.EmpCode = Availability.Empcode " & _
"WHERE StaffMaster.Discipline='Architecture' " & _
"AND StaffMaster.Designation<>'Project Manager' " & _
"AND StaffMaster.Designation NOT LIKE 'Head%' " & _
"AND StaffMaster.Designation NOT LIKE '%Manager%' " & _
"AND StaffMaster.Designation<>'Designer' " & _
"AND StaffMaster.EmpCode NOT LIKE 'X%' " & _
"AND Availability.Month_Year BETWEEN #May 2004# AND #October 2004# " & _
"GROUP BY StaffMaster.EmpCode, StaffMaster.EmpName " & _
"ORDER BY StaffMaster.EmpName " & _
"PIVOT Availability.Month_Year"

R0.Open strsql, Cn, adOpenDynamic, adLockReadOnly

Report.DiscardSavedData
Report.Database.Tables.Add "", , R0

Here I have one problem more to be solved. I hope it can be solved. The problem is that , if the database table does not have a record for that month, it displays blank in the report for that month. Instead of displaying blank, I wish to display it as 100. Can this be accomplished? Please help.

Thanks|||Any help please...|||Use a Conditional Suppress on that field.|||You mean this?

crField.ConditionFormula(crEnableSuppressConditionFormulaType) = "If (IsNull(Report.Database.Tables(1).Fields(1).Name)) then 100"

If so, when the code is executed, it gives a message saying

'The ) is missing'

Thanks|||Try creating a Formula instead of using the Conditional Suppress. I think I read your last post too quickly, I don't think the Conditional Suppress will work in this case.

If you're creating you report files with the designer in VB, right-click on the word 'Formula' in the list on the Left and Click 'New'. Give it a Name. Type something similar to this:

If {FieldName} = NULL Then
100
Else
{FieldName}

I don't know the proper syntax for If statements in Crystal Reports, so you'll have to look it up. Once you get the Format correct, drop the Formula field into place where you have your field (delete the field).|||Try this,

crField.ConditionFormula
(crEnableSuppressConditionFormulaType) = "If IsNull(Report.Database.Tables(1).Fields(1).Name) = True then 100"|||Originally posted by harmonycitra
Try this,

crField.ConditionFormula
(crEnableSuppressConditionFormulaType) = "If IsNull(Report.Database.Tables(1).Fields(1).Name) = True then 100"

This gives me an error.

'The ) is missing.'

Thanks|||Originally posted by malleyo
Try creating a Formula instead of using the Conditional Suppress. I think I read your last post too quickly, I don't think the Conditional Suppress will work in this case.

If you're creating you report files with the designer in VB, right-click on the word 'Formula' in the list on the Left and Click 'New'. Give it a Name. Type something similar to this:

If {FieldName} = NULL Then
100
Else
{FieldName}

I don't know the proper syntax for If statements in Crystal Reports, so you'll have to look it up. Once you get the Format correct, drop the Formula field into place where you have your field (delete the field).

But I am creating every object in the crystal report with code. Also, adding the database into the report too with code. Everything is done on the fly.

Thanks

Availability of SSIS Run-time support (Redistributable package)?

Can somebody please help answering this question?

I'm planning to consider writing a SSIS package for a new project that requires downloading large chunk of data and transform into the diverse databases such as MS SQL or Oracle depending on the Client's Datbase.

For the clients having SQL Server installed at their end, i had no issues in deploying this package on their server and run it in their licensed instance.

What should be the case for others having Oracle database? Wouldn't installing the SQL 2005 client tools install the necessary run-time services for running SSIS packages? What i understood from the MSDN library (http://msdn2.microsoft.com/en-us/library/ms403355.aspx) is that there's no run-time support available for running the SSIS packages (unlike DTS run-time support) in production environment!

Would that mean that it requires a SQL Standard edition, at minimum, (as Integration Services is OOTB from Standard Edition onwards) to be installed at the production site to run this package?

If so, the client wouldn't be ready (which is fair too) to buy the new license just to run this package. Is there any work-around/suggestions for this case?

If not, can somebody please point me to the right location where i can download the run-time support for running SSIS packages?

Any thoughts on this is highly appreciated.

Thanks for your time.

~Sagar

You're reading of the Help is correct, SSIS does not offer a redistributable like DTS did, and also the licensing has changed. SSIS is a server component, not client, so you need a Server license, not a CAL. Mind you for your Oracle shops, you would always have needed a server as you cannot have CALs without having purchased a server as well.

If you want to install/run SISS you need to but a "SQL Server" license. Choose the edition, Standard is normally fine for most people. I also think it is quite nice you get a relational engine, OLAP server and report server for free as well. Most ETL packages don't include that level of free software with them.

This is a big change from the DTS world, SSIS is a considered a Server now, and licensed as such.

|||

This is strange. Why would anybody (well, most of them at least) go for a full fledged database software with so many OOTB packages (database, SSIS, SSAS, OLAP Server, SSRS etc) for just running few ETL packages?

One hand we use SSIS package to transform the data to different databases and on the other hand we are asked to buy one MS SQL database license in order to execute the package. How justifiable it is to ask the Customer buy a SQL Server license (costing ~$3000 for a server setup) just to run this package? This not only a resource exhaustive but also a maintenace over head to tackle with the software which the customer might not want to..

Isn't it possible just to get a stand-alone install of SSIS (let alone the availability of run-time support for SSIS) if not now, may be in the future?

Regards,

Sagar

|||Take a look at the prices for other ETL software out there. $3000 a server isn't bad at all, even if the only thing you were getting is the ETL tool.|||

All happies If it's just done with 3000$.

But Who would like to share the Production server (on which Oracle database is installed) with MS Sql Server just to run the SSIS packages? It is a maintenance cost overrun to allocate a different server machine just to host the MS SQL license and hence a performance hit due to the cross-machine calls between SSIS Packages and the destination Server (i.e. Oracle database server).

With this background, does it not make sense having a redistributable package from MS (run-time support) for executing SSIS packages that can be installed on Oracle database server(on Production server)?

|||

VidyaSagarCh wrote:

All happies If it's just done with 3000$.

But Who would like to share the Production server (on which Oracle database is installed) with MS Sql Server just to run the SSIS packages? It is a maintenance cost overrun to allocate a different server machine just to host the MS SQL license and hence a performance hit due to the cross-machine calls between SSIS Packages and the destination Server (i.e. Oracle database server).

With this background, does it not make sense having a redistributable package from MS (run-time support) for executing SSIS packages that can be installed on Oracle database server(on Production server)?

IIRC, you do not need to install the SQL Server RDBMS to run SSIS. So the scenario you're describing should work just fine - if you only want to install SSIS on the Oracle box, you should be able to do that.

With that said, if your Oracle DBAs are anything like mine, you will have just as much trouble getting them to let you install SSIS as you would have getting them to let you install the full SQL Server stack.

Availability of SQL Server 2000

It may be not suitable to ask about SQL 2000 here, but I really want an answer from you experts.
I have my SQL 2000 installed in a Win2000 server, however, the SQL 2000 is not available to my application until I log into a shared folder of the Win2000 or I enable the Guest account of the Win2000.
What is the proper way to let the SQL 2000 available to my application? My SQL 2000 uses SQL Security Mode.
Thanks.If you're using SQL Authentication (also known as mixed mode--it still supports Windows Authentication, too), you can supply a SQL user name and password in your database connection string (or whatever provider you use).

Windows Authentication is by far preferred, because you can use all sorts of Windows domain policies to control access with a lot of flexibility.

-Ryan / Kardax

availability of Enterprise Manager

Is there a way to run the user interface tools for SQL Server, such as
Enterprise Manager and Query Analyzer, from two different computers on a
network, or do you need a separate license for each computer that will be
running them?
We are considering migrating to SQL Server, and we will have two developers
working on it. We will be installing our data in only one database, so we
only need one copy of SQL Server for the actual database. However, both
developers will need access to the user interface tools. Does this mean we
need to purchase two copies of SQL Server if the developers are working from
two different computers?
Thanks in advance,
Paul
"Paul Ponzelli" <begone@.spam.forever> wrote in
news:O4VpAi5PFHA.2252@.TK2MSFTNGP15.phx.gbl:

> Is there a way to run the user interface tools for SQL Server, such as
> Enterprise Manager and Query Analyzer, from two different computers on
> a network, or do you need a separate license for each computer that
> will be running them?
> We are considering migrating to SQL Server, and we will have two
> developers working on it. We will be installing our data in only one
> database, so we only need one copy of SQL Server for the actual
> database. However, both developers will need access to the user
> interface tools. Does this mean we need to purchase two copies of SQL
> Server if the developers are working from two different computers?
Licensing is not my field, but I try... If you're running in per processor
licensing mode I do not think that you need extra licenses for the
developers. Otherwise I think you only have to ensure that the developers
have client license to the database. What you should consider (in my
opinion) since this seems to be a development project, is to buy one MSDN
license for each developer. As far as I've understood Microsofts policy,
that should do, even without any licenses for the Server.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||Thanks for your reply, Ole. However, everyone in my organization already
has a copy of Access 2002, and thus a license for MSDE. But MSDE doesn't
have the Enterprise Manager tool, and that's what I'm asking about. Can
developers on two different computers use Enterprise Manager for a SQL
Server database without having to buy two copies of SQL Server, or is there
a way they can both use Enterprise Manager from a single copy of SQL Server?
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns9636EFC48769Folekristianbangaas@.207.46.248 .16...
> "Paul Ponzelli" <begone@.spam.forever> wrote in
> news:O4VpAi5PFHA.2252@.TK2MSFTNGP15.phx.gbl:
>
> Licensing is not my field, but I try... If you're running in per processor
> licensing mode I do not think that you need extra licenses for the
> developers. Otherwise I think you only have to ensure that the developers
> have client license to the database. What you should consider (in my
> opinion) since this seems to be a development project, is to buy one MSDN
> license for each developer. As far as I've understood Microsofts policy,
> that should do, even without any licenses for the Server.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||"Paul Ponzelli" <begone@.spam.forever> wrote in
news:u6WUar6PFHA.3336@.TK2MSFTNGP09.phx.gbl:

> Thanks for your reply, Ole. However, everyone in my organization
> already has a copy of Access 2002, and thus a license for MSDE. But
> MSDE doesn't have the Enterprise Manager tool, and that's what I'm
> asking about. Can developers on two different computers use
> Enterprise Manager for a SQL Server database without having to buy two
> copies of SQL Server, or is there a way they can both use Enterprise
> Manager from a single copy of SQL Server?
Note: I said MSDN, not MSDE. MSDN is Microsoft Developer Network, which
gives access to software for testing and development purposes. One MSDN
membership is (as far as I know) required per developer. Except for that,
no other licenses should be neccessary. For further information:
http://msdn.microsoft.com/howtobuy/vs2005/subscribe/
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||>> I said MSDN, not MSDE.
You're right! Duh.
Thanks so much, Ole.
|||hi,
in addition to Ole answer, you can get SQL Server Developer edition for
about $50, which includes all the client tools ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That's Great! Thanks, Andrea.

Availability of cool new features ...

Hi all,

Some feature availability and capability questions (specifics would be great, but ballpark estimates are better than nothing ). First off, which CTP will deliver the

- DB encryption improvements?

- Performance data warehouse?

- DB mirroring improvements?

- DB (row/data) compression?

- DB backup compression?

- Auditing improvements (table DDL-driven, new tools, auditor role, etc.)?

Capability-wise, will the DB encryption improvements include integration w/ the Windows PKI infrastructure?

Thx,

TimR

Hi

All the features you mention except Auditing and row/data Compression will be available in CTP-5 (somewhere around September). The other two will show up in the next CTP-6.

Don't exactly what you're looking for inthe integration, but yes, one of the encryption enhancements is to support external key management.

- Christian Kleinerman [MSFT]

Availability of cool new features ...

Hi all,

Some feature availability and capability questions (specifics would be great, but ballpark estimates are better than nothing ). First off, which CTP will deliver the

- DB encryption improvements?

- Performance data warehouse?

- DB mirroring improvements?

- DB (row/data) compression?

- DB backup compression?

- Auditing improvements (table DDL-driven, new tools, auditor role, etc.)?

Capability-wise, will the DB encryption improvements include integration w/ the Windows PKI infrastructure?

Thx,

TimR

Hi

All the features you mention except Auditing and row/data Compression will be available in CTP-5 (somewhere around September). The other two will show up in the next CTP-6.

Don't exactly what you're looking for inthe integration, but yes, one of the encryption enhancements is to support external key management.

- Christian Kleinerman [MSFT]

Availability during ETL processing

Hi,

I am in the midst of designing a new Data Warehouse system. As we get further into the design of the system, the more we are realising how complex our ETL is going to be and that the amount of time it will take to run could be significant i.e. a few days! My question is obviously I don't want to have a down time in my relational system for this long and prevent my users from accessing the data for days at a time each month. So what functionality should I be looking at to allow me to maintain a working copy of the data that users can query whilst I perform database updates and then perform a quick promotion of the updated data to users for querying?

If you can point me in the direction of the right functionality in SQL Server 2005 and possible some relevant white papers that cover this sort of scenario I would be grateful.

regards

Colin

In your ETL process, are you building a completely new DW database, or are you updating the existing one?

This seems like a very good application for some form of snapshots.

If you're updating an existing database, then database snapshots should fit the bill. They will present a static view of the data as it existed at the point in time when the snapshot was created. This allows the source database to be updated without disturbing the users.

If you create and populate a new database in each ETL cycle, then you may want to look into something like volume snapshots using the VSS framework. In this case, you are making a snapshot of the entire volume(s) on which the database resides. This can then be mounted as a read-only database for your users to query. The advantage in this scenario is that it doesn't need to be tied to the previous database.

Availability after an instance crash.

Up to now I have no experience with an instance failure of
a large database.
If you have a database say 150 Gb, and the machine has
to 'reboot', what would be a 'normal' time before the
database is available again ?
And in a worse case scenario ?
What if the OS does start a diskcheck before booting ?
(On home pc's I have seen this happening, but never
on a serious server, can this happen on a server ?)
How long will it take SQL-server to 'recover', from
a crash ?
(I am thinking of failures like a power failure or
an OS crash, not a hardware failure).
On a small testsystem where I did try this kind of
scenario's, after the OS boot, it took SQL-server only
a small time to recover (seconds).
thanks for your attention,
ben brugman.Be
It really depends on the perfomance of your hardware. There is no set rule that I know of for time. It also depends on what needs to be recovered and what was in the log at the time of the crash. The more of the work in the log the longer the time. It could take anywhere from 1 minute to 45 minutes depending on how much work it has to do
Sorry it is so vagu
Jef
MCDBA, MCSE+I|||The recovery interval setting can impact the speed at which the =databases will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL =Server program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know =if it could happen or not.
In my experience the databases are available shortly after the machine =comes online. I have experienced a few hardware related machine crashes =that have caused a server to black screen (become totally unresponsive). = We had to press the power button to turn the machine off and back on =again. Upon restarting the database was available almost right away. =This was an OLTP database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range =that the machine became unplugged from the UPS. Upon power-up SQL =Server was running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to =backup and restore databases. Books Online has lots of good information =in this area.
-- Keith
"ben brugman" <ben@.niethier.nl> wrote in message =news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> > If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> > What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> > How long will it take SQL-server to 'recover', from
> a crash ?
> > (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> > On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> > thanks for your attention,
> ben brugman.
> >|||Thanks Jeff and Keith,
My greatest concern was the time the OS will take on a reboot.
(And do diskchecks or other time consuming matters).
As you both indicate that you are not concerned, I'll drop this
concern.
Thanks for your answers,
Ben Brugman
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OMKoDB9IEHA.3968@.TK2MSFTNGP12.phx.gbl...
The recovery interval setting can impact the speed at which the databases
will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL Server
program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know if
it could happen or not.
In my experience the databases are available shortly after the machine comes
online. I have experienced a few hardware related machine crashes that have
caused a server to black screen (become totally unresponsive). We had to
press the power button to turn the machine off and back on again. Upon
restarting the database was available almost right away. This was an OLTP
database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range that
the machine became unplugged from the UPS. Upon power-up SQL Server was
running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to
backup and restore databases. Books Online has lots of good information in
this area.
Keith
"ben brugman" <ben@.niethier.nl> wrote in message
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> How long will it take SQL-server to 'recover', from
> a crash ?
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> thanks for your attention,
> ben brugman.
>

Availability after an instance crash.

Up to now I have no experience with an instance failure of
a large database.
If you have a database say 150 Gb, and the machine has
to 'reboot', what would be a 'normal' time before the
database is available again ?
And in a worse case scenario ?
What if the OS does start a diskcheck before booting ?
(On home pc's I have seen this happening, but never
on a serious server, can this happen on a server ?)
How long will it take SQL-server to 'recover', from
a crash ?
(I am thinking of failures like a power failure or
an OS crash, not a hardware failure).
On a small testsystem where I did try this kind of
scenario's, after the OS boot, it took SQL-server only
a small time to recover (seconds).
thanks for your attention,
ben brugman.
Ben
It really depends on the perfomance of your hardware. There is no set rule that I know of for time. It also depends on what needs to be recovered and what was in the log at the time of the crash. The more of the work in the log the longer the time. It
could take anywhere from 1 minute to 45 minutes depending on how much work it has to do.
Sorry it is so vague
Jeff
MCDBA, MCSE+I
|||The recovery interval setting can impact the speed at which the =
databases will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL =
Server program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know =
if it could happen or not. =20
In my experience the databases are available shortly after the machine =
comes online. I have experienced a few hardware related machine crashes =
that have caused a server to black screen (become totally unresponsive). =
We had to press the power button to turn the machine off and back on =
again. Upon restarting the database was available almost right away. =
This was an OLTP database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range =
that the machine became unplugged from the UPS. Upon power-up SQL =
Server was running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to =
backup and restore databases. Books Online has lots of good information =
in this area.
--=20
Keith
"ben brugman" <ben@.niethier.nl> wrote in message =
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
>=20
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
>=20
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
>=20
> How long will it take SQL-server to 'recover', from
> a crash ?
>=20
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
>=20
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
>=20
> thanks for your attention,
> ben brugman.
>=20
>
|||Thanks Jeff and Keith,
My greatest concern was the time the OS will take on a reboot.
(And do diskchecks or other time consuming matters).
As you both indicate that you are not concerned, I'll drop this
concern.
Thanks for your answers,
Ben Brugman
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OMKoDB9IEHA.3968@.TK2MSFTNGP12.phx.gbl...
The recovery interval setting can impact the speed at which the databases
will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL Server
program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know if
it could happen or not.
In my experience the databases are available shortly after the machine comes
online. I have experienced a few hardware related machine crashes that have
caused a server to black screen (become totally unresponsive). We had to
press the power button to turn the machine off and back on again. Upon
restarting the database was available almost right away. This was an OLTP
database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range that
the machine became unplugged from the UPS. Upon power-up SQL Server was
running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to
backup and restore databases. Books Online has lots of good information in
this area.
Keith
"ben brugman" <ben@.niethier.nl> wrote in message
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> How long will it take SQL-server to 'recover', from
> a crash ?
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> thanks for your attention,
> ben brugman.
>

Availability after an instance crash.

Up to now I have no experience with an instance failure of
a large database.
If you have a database say 150 Gb, and the machine has
to 'reboot', what would be a 'normal' time before the
database is available again ?
And in a worse case scenario ?
What if the OS does start a diskcheck before booting ?
(On home pc's I have seen this happening, but never
on a serious server, can this happen on a server ?)
How long will it take SQL-server to 'recover', from
a crash ?
(I am thinking of failures like a power failure or
an OS crash, not a hardware failure).
On a small testsystem where I did try this kind of
scenario's, after the OS boot, it took SQL-server only
a small time to recover (seconds).
thanks for your attention,
ben brugman.Ben
It really depends on the perfomance of your hardware. There is no set rule
that I know of for time. It also depends on what needs to be recovered and
what was in the log at the time of the crash. The more of the work in the l
og the longer the time. It
could take anywhere from 1 minute to 45 minutes depending on how much work i
t has to do.
Sorry it is so vague
Jeff
MCDBA, MCSE+I|||The recovery interval setting can impact the speed at which the =
databases will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL =
Server program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know =
if it could happen or not. =20
In my experience the databases are available shortly after the machine =
comes online. I have experienced a few hardware related machine crashes =
that have caused a server to black screen (become totally unresponsive). =
We had to press the power button to turn the machine off and back on =
again. Upon restarting the database was available almost right away. =
This was an OLTP database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range =
that the machine became unplugged from the UPS. Upon power-up SQL =
Server was running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to =
backup and restore databases. Books Online has lots of good information =
in this area.
--=20
Keith
"ben brugman" <ben@.niethier.nl> wrote in message =
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
>=20
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
>=20
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
>=20
> How long will it take SQL-server to 'recover', from
> a crash ?
>=20
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
>=20
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
>=20
> thanks for your attention,
> ben brugman.
>=20
>|||Thanks Jeff and Keith,
My greatest concern was the time the OS will take on a reboot.
(And do diskchecks or other time consuming matters).
As you both indicate that you are not concerned, I'll drop this
concern.
Thanks for your answers,
Ben Brugman
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OMKoDB9IEHA.3968@.TK2MSFTNGP12.phx.gbl...
The recovery interval setting can impact the speed at which the databases
will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL Server
program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know if
it could happen or not.
In my experience the databases are available shortly after the machine comes
online. I have experienced a few hardware related machine crashes that have
caused a server to black screen (become totally unresponsive). We had to
press the power button to turn the machine off and back on again. Upon
restarting the database was available almost right away. This was an OLTP
database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range that
the machine became unplugged from the UPS. Upon power-up SQL Server was
running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to
backup and restore databases. Books Online has lots of good information in
this area.
Keith
"ben brugman" <ben@.niethier.nl> wrote in message
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> How long will it take SQL-server to 'recover', from
> a crash ?
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> thanks for your attention,
> ben brugman.
>