Mirror of master, msdb, tempdb, or model databases.

Hi Guys,

Since,You cannot mirror the master, msdb, tempdb, or model databases.What will happen if i create new login,change existing security profile and new jobs,change of existing job on princicpal db. how these will be mirrored to other server and in case failover, how it will treat.

Thaks

Mirroring works at the database level only. dts/ssis packages, jobs etc are not mirrored. Database users are mirrored but not their corresponding SQL Server login.

You need to keep a copy of dts/ssis packages, jobs etc on the mirror server and whatever changes you make to them on the principal server, you will also have to make on the mirror server if you want to keep the servers exactly in sync.

hope this helps

Minimum Date in DateStamp field is not 1-Jan-100 (Silly by SQLServer guys)

Minimum Date in DateStamp field is not 1-Jan-100. Wouldn't we expect that.
In SQLServer2000 is it "1-Jan-100", if not WHY ??,
In the previous versions it is "01-Jan-1753"
bikramjeetThe problem lies with the Gregorian calendar, not the SQL software.

If you go back before 1753, you start to run into all kinds of problems with dates. Finding out which day corresponds to a particular date means that the algorithm needs to know where you want the date resolved from a political standpoint as well as a chronological standpoint. There are also a number of other problems that crop up, such as certain calender days occuring more than once. It gets ugly.

See Wolfram (http://scienceworld.wolfram.com/astronomy/GregorianCalendar.html) for more explaination.

-PatP|||The problem lies with the Gregorian calendar, not the SQL software.

If you go back before 1753, you start to run into all kinds of problems with dates. Finding out which day corresponds to a particular date means that the algorithm needs to know where you want the date resolved from a political standpoint as well as a chronological standpoint. There are also a number of other problems that crop up, such as certain calender days occuring more than once. It gets ugly.

See Wolfram (http://scienceworld.wolfram.com/astronomy/GregorianCalendar.html) for more explaination.

-PatP

I always found that to be a lame excuse...

As if there aren't enough...what the technical word they use...oh well, kludges in M$ internal code anyway...

If they really wanted to..(and who the hell said ancient dates are precise anyway)...bulid it in tho the damn date functions...why limit it?

Next they'll be telling us you can't travel fatser than the speed of light...

The Church of M$|||I always found that to be a lame excuse...Yeah, but Convert() would be kind of funky if it had arguments for your location, political affiliation, and religion!

Oracle solves this problem by simply ignoring the Gregorian reformation altogether... You simply learn to live on Oracle time!

There are other client programming languages that have library code that detects the PC's locale and uses that to make the decisions (which are frequently incorrect) about date conversions. This can be a real hoot to debug, since the exact same binary running on the exact same release of windoze would change its results based on where it thought the PC was!

That leads to the issue of lunar calendars that have leap months added on a tough to predict basis... That's even more fun!

I don't remember if you were involved in the discussion of database normalization that Rudy invited me to that was the first time I'd posted here... The issue of dates is a lot like the issue of normalization: if you know nothing about it, no problem; if you know a little bit about it, not a big problem; as you learn more than just a little, the problem gets bigger and uglier really fast. The whole thing becomes a slippery slope, so it gets tough to dance!

-PatP

MIN() + MAX() Deadlock ?

Hi guys
The only difference between the following 2 queries...
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
Where UnitID = '1720200022285010001407'
Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
with (nolock) Where UnitID = '1720200022285010001419'
...is that the first one takes only 36 seconds in a 21 million table
database, but the second one takes forever (last time I tried, the query
took 3 minutes and had to stop it because of blocking)
Note that both queries are exactly the same (except for the where
clause), and both records have more or less the same amount of records
(about 100,000)
The funny thing is that, if I get the MIN() first, and then the MAX()
for the same where clause:
Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
UnitID = '1720200022285010001419'
I get the value. However, it does not work in combination.
Also note that the problem is only with UnitId = '1720200022285010001419' (The others work just fine)
I checked the nulls (no nulls at all), the indices (dropped and
re-created them with fill factor of 90%, althhough it is under heavy
"insert" stress), and there seems to be no reason for this deadlock
Any clues
EvalPleae don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"eval" <eval@.eval.com> wrote in message
news:OHg3EdP#EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Hi guys
>
> The only difference between the following 2 queries...
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock) Where UnitID = '1720200022285010001419'
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
> took 3 minutes and had to stop it because of blocking)
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
> Select min(GpsDate) minTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> Select max(GpsDate) maxTime From TblAvlTrans with (nolock) Where
> UnitID = '1720200022285010001419'
> I get the value. However, it does not work in combination.
> Also note that the problem is only with UnitId => '1720200022285010001419' (The others work just fine)
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
> Any clues
>
> Eval|||Aaron [SQL Server MVP] wrote:
> Pleae don't multi-post.
>
Sorry for the multipost.
My mistake :+)

Migration Task

Hi guys.

I have a hard task to solve. I need to migrate 3 databases from an old SQL 6.0 (yes, it still exists!) to a new SQL 2000. These 3 dbs contain only tables and stores procedures. The sizes are:

DB_INews : 6.0GB
DB_Balanco : 600MB
DB_Invest : 100MB

I think I will have problems with DB_INEWS, because it contains tables with 2 million lines.

What is the best option to do this migration?

Cheers,

FabianoIF the databases are in SQL 6.0 version the only way is to upgrade it to 6.5 version and then choose UPGRADE WIZARD from 6.5 to SQL 2000. There is no other go to migrate data directly.

Migration of SQLServer user databases to another SQLServer

Guys,
I would like to migrate a number of user databases from one SQLSERVER (name:
LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to another (na
me: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3)).
Please can you outline the steps involved and any pitfalls I should watch fo
r? I am new to SQLServer and this is live data. This needs completing today
as well!!
TIA, KenKen,
Have a look at:
INF: Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/defaul...kb;EN-US;224071
Mark Allison, SQL Server MVP
Ken Jones wrote:

> Guys,
> I would like to migrate a number of user databases from one SQLSERVER (nam
e: LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to another (
name: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3)).
> Please can you outline the steps involved and any pitfalls I should watch
for? I am new to SQLServer and this is live data. This needs completing toda
y as well!!
> TIA, Ken
>
Mark Allison, SQL Server MVP
http://www.allisonmitchell.com|||Hi,
Since you need to upgrade only the user databases you can do one of the
folowing
1. Detach and Attach databases
2. Backup and Restore the databases
1. Detach and Attach
a. Use SP_DETACH_DB to detach the MDF and LDF files
b. Copy the files to Destination server
c. Use SP_ATTACH_DB to attach the databases
(Refer books online for information)
2. Backup and Restore
a. Backup the source databases using BACKUP DATABASE command
b. Copy the .BAK files to destination
c. Restore the database using RESTORE DATABASE command
After this steps you may need to move the logins from source server to
destination server.
Have a look into the below link for information.
http://www.databasejournal.com/feat...cle.php/2228611
Thanks
Hari
MCDBA
"Ken Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:1442724A-12BF-4AF2-8A02-B558DCA60658@.microsoft.com...
> Guys,
> I would like to migrate a number of user databases from one SQLSERVER
(name: LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to
another (name: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3)).
> Please can you outline the steps involved and any pitfalls I should watch
for? I am new to SQLServer and this is live data. This needs completing
today as well!!
> TIA, Ken
>|||Mark,
So I can detach (using sp_detach) from the source database, ftp to new serve
r and then attach (using sp_attach)'
Is that all there is to it? Will the sp_attach alert master as to the appear
ance of x number of new user databases as I am moving from one SQLServer (li
ve) to a completely different, functioning SQLServer(live)?
TIA, Ken.|||Thanks for your swift replies - WOW!!|||Hi Hari,
Some of my files are .DAT files. Will this affect the detach and attach proc
ess?
TIA, Ken|||DAT files could mean they are 6.5 database files. You can't move such across
to 7.0/2000. You need to have those databases available in a 6.5 install and
use the upgrade wizard from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ken Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:40772738-BE5B-4C98-86D9-DE1DB7959FFB@.microsoft.com...
> Hi Hari,
> Some of my files are .DAT files. Will this affect the detach and attach
process?
> TIA, Ken|||"I would like to migrate a number of user databases from one SQLSERVER (name
: LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to another (n
ame: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3))."
Both are SQLServer8 servers. I am going to try the sp_detach_db and sp_attac
h_db by ftp'ing the LDF and MDF files. I'll see if this works.
Ken|||OK I just mentioned the 6.5 stuff because in 6.5 DAT were the default
extension for both database and backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ken Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:63CF9224-D4BF-4550-AF7E-4B139C9C1E69@.microsoft.com...
> "I would like to migrate a number of user databases from one SQLSERVER
(name: LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to
another (name: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3))."
> Both are SQLServer8 servers. I am going to try the sp_detach_db and
sp_attach_db by ftp'ing the LDF and MDF files. I'll see if this works.
> Ken

migrating to 2005 troubles :(

hey guys
just to do a test run i wanted to migrate the northwind database from
2000 to 2005
when i do a backup and try to restore on the new machine ... i receive
this message
i know its probably something trivial ... but can anyone give me a
hand with this ?
TITLE: Microsoft SQL Server Management Studio
--
Restore failed for Server 'NAMECLapps\ins1'.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot use file 'C:\test\northwnd.mdf'
for clustered server. Only formatted files on which the cluster
resource of the server has a dependency can be used.
(Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476You can't put a user db on the local drive of a cluster. It must be one of
the drives in the cluster resource group.
--
Andrew J. Kelly SQL MVP
<glombica@.hotmail.com> wrote in message
news:1185286074.533480.16750@.m3g2000hsh.googlegroups.com...
> hey guys
> just to do a test run i wanted to migrate the northwind database from
> 2000 to 2005
> when i do a backup and try to restore on the new machine ... i receive
> this message
> i know its probably something trivial ... but can anyone give me a
> hand with this ?
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'NAMECLapps\ins1'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: Cannot use file 'C:\test\northwnd.mdf'
> for clustered server. Only formatted files on which the cluster
> resource of the server has a dependency can be used.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
>