Migrating to SQL 2005: box

Showing posts with label box. Show all posts

Friday, March 30, 2012

Mirroring confusion.

SQL2K5
SP1
I am testing out Synchronous Mirroring and have a question about failover.
It seems the only way I can failover to the Mirror box is to log onto the
Principal box and hit the Failover button? Isn't the whole point of
mirroring to be able to handle the Principal not being available? Is there
another way? To clarify, I have no witness and am attempting a manual
failover.
TIA, ChrisRHi Chris,
I believe the option you are looking for is as follows from bol:
1.. Connect to the mirror server.
2.. Issue the following statement:
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
where <database_name> is the mirrored database.
The mirror server immediately transitions to principal server, and
mirroring is suspended.
Regards
Jamie
"ChrisR" <NotAChance@.ms.com> wrote in message
news:O9tTWmHyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> SQL2K5
> SP1
> I am testing out Synchronous Mirroring and have a question about failover.
> It seems the only way I can failover to the Mirror box is to log onto the
> Principal box and hit the Failover button? Isn't the whole point of
> mirroring to be able to handle the Principal not being available? Is there
> another way? To clarify, I have no witness and am attempting a manual
> failover.
> TIA, ChrisR
>sql

Mirroring confusion.

I am testing out Synchronous Mirroring and have a question about failover. It seems the only way I can failover to the Mirror box is to log onto the Principal box and hit the Failover button? Is the whole point of mirroring to be able to handle the Principal not being available? Is there another way?

TIA, ChrisRIn the event of a failure, the "witness" does that automagically.

-PatP|||Yes, but I am using "no witness" mirroring.|||You have to have a witness server to automatic failover.
The witness can be a stripped down server running SQL Server Express. It does not need to be a powerful platform or have a licensed install.|||Yes, but I am attempting a manual failover.

Mirroring confusion.

Monday, March 26, 2012

mirror

I would like to set up a new SQL server on a completely different box in a
different location in the company (perhaps even offsite). I want this new
server to be a mirror of the original SQL server, such that when data in the
original server is updated the data in the new one is also updated etc...
I would like to do this for security reasons, such that if the original
server goes up in flames, I can recover all the data and quickly switch to
the new server.
I have only ever had to deal with one SQL server at once before, so what I
want to know is:
Is this the best way to ensure against catastrophic failure of the server.
What do I need to read up on to achieve this.
Thanks.Hi
There are different options available depending on which version of SQL
Server you are running and what your system configuration is. With SQL 7 -
2005 you can use log shipping or replication. SQL 2005 has database
mirroring. There are also options that require specific hardware such as
failover clustering and SAN replication.
You may want to check out
http://www.microsoft.com/sql/technologies/highavailability/default.mspx for
an introduction.
John
"JosephByrns" wrote:
> I would like to set up a new SQL server on a completely different box in a
> different location in the company (perhaps even offsite). I want this new
> server to be a mirror of the original SQL server, such that when data in the
> original server is updated the data in the new one is also updated etc...
> I would like to do this for security reasons, such that if the original
> server goes up in flames, I can recover all the data and quickly switch to
> the new server.
> I have only ever had to deal with one SQL server at once before, so what I
> want to know is:
> Is this the best way to ensure against catastrophic failure of the server.
> What do I need to read up on to achieve this.
> Thanks.
>
>|||Bear in mind that there is a Microsoft article on Mirroring that stipulates
that they do not recommend database mirroring in a production environment,
and should only be used in development and test environments.
Please see this article.
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
AndyP,
Sr. Database Administrator,
MCDBA 2003
"John Bell" wrote:
> Hi
> There are different options available depending on which version of SQL
> Server you are running and what your system configuration is. With SQL 7 -
> 2005 you can use log shipping or replication. SQL 2005 has database
> mirroring. There are also options that require specific hardware such as
> failover clustering and SAN replication.
> You may want to check out
> http://www.microsoft.com/sql/technologies/highavailability/default.mspx for
> an introduction.
> John
> "JosephByrns" wrote:
> > I would like to set up a new SQL server on a completely different box in a
> > different location in the company (perhaps even offsite). I want this new
> > server to be a mirror of the original SQL server, such that when data in the
> > original server is updated the data in the new one is also updated etc...
> > I would like to do this for security reasons, such that if the original
> > server goes up in flames, I can recover all the data and quickly switch to
> > the new server.
> >
> > I have only ever had to deal with one SQL server at once before, so what I
> > want to know is:
> >
> > Is this the best way to ensure against catastrophic failure of the server.
> > What do I need to read up on to achieve this.
> >
> > Thanks.
> >
> >
> >|||Thank you both for the info, this is on SQL Server 2000, so I guess
mirriring doesn't apply here.
"AndyP" <AndyP@.discussions.microsoft.com> wrote in message
news:16EFB1CD-473F-4FE6-92FF-9B8D7979D1F9@.microsoft.com...
> Bear in mind that there is a Microsoft article on Mirroring that
> stipulates
> that they do not recommend database mirroring in a production environment,
> and should only be used in development and test environments.
> Please see this article.
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "John Bell" wrote:
>> Hi
>> There are different options available depending on which version of SQL
>> Server you are running and what your system configuration is. With SQL
>> 7 -
>> 2005 you can use log shipping or replication. SQL 2005 has database
>> mirroring. There are also options that require specific hardware such as
>> failover clustering and SAN replication.
>> You may want to check out
>> http://www.microsoft.com/sql/technologies/highavailability/default.mspx
>> for
>> an introduction.
>> John
>> "JosephByrns" wrote:
>> > I would like to set up a new SQL server on a completely different box
>> > in a
>> > different location in the company (perhaps even offsite). I want this
>> > new
>> > server to be a mirror of the original SQL server, such that when data
>> > in the
>> > original server is updated the data in the new one is also updated
>> > etc...
>> > I would like to do this for security reasons, such that if the original
>> > server goes up in flames, I can recover all the data and quickly switch
>> > to
>> > the new server.
>> >
>> > I have only ever had to deal with one SQL server at once before, so
>> > what I
>> > want to know is:
>> >
>> > Is this the best way to ensure against catastrophic failure of the
>> > server.
>> > What do I need to read up on to achieve this.
>> >
>> > Thanks.
>> >
>> >
>> >|||OK, it looks like log shipping will do what I want, unfortunately it appears
that Log Shipping is only available to SQL 2000 Enterprise (I have
Professional), can you confirm this is true? Will I require enterprise for
the replication approach?
Thanks.
"AndyP" <AndyP@.discussions.microsoft.com> wrote in message
news:16EFB1CD-473F-4FE6-92FF-9B8D7979D1F9@.microsoft.com...
> Bear in mind that there is a Microsoft article on Mirroring that
> stipulates
> that they do not recommend database mirroring in a production environment,
> and should only be used in development and test environments.
> Please see this article.
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "John Bell" wrote:
>> Hi
>> There are different options available depending on which version of SQL
>> Server you are running and what your system configuration is. With SQL
>> 7 -
>> 2005 you can use log shipping or replication. SQL 2005 has database
>> mirroring. There are also options that require specific hardware such as
>> failover clustering and SAN replication.
>> You may want to check out
>> http://www.microsoft.com/sql/technologies/highavailability/default.mspx
>> for
>> an introduction.
>> John
>> "JosephByrns" wrote:
>> > I would like to set up a new SQL server on a completely different box
>> > in a
>> > different location in the company (perhaps even offsite). I want this
>> > new
>> > server to be a mirror of the original SQL server, such that when data
>> > in the
>> > original server is updated the data in the new one is also updated
>> > etc...
>> > I would like to do this for security reasons, such that if the original
>> > server goes up in flames, I can recover all the data and quickly switch
>> > to
>> > the new server.
>> >
>> > I have only ever had to deal with one SQL server at once before, so
>> > what I
>> > want to know is:
>> >
>> > Is this the best way to ensure against catastrophic failure of the
>> > server.
>> > What do I need to read up on to achieve this.
>> >
>> > Thanks.
>> >
>> >
>> >|||I mean I have standard edition.
"AndyP" <AndyP@.discussions.microsoft.com> wrote in message
news:16EFB1CD-473F-4FE6-92FF-9B8D7979D1F9@.microsoft.com...
> Bear in mind that there is a Microsoft article on Mirroring that
> stipulates
> that they do not recommend database mirroring in a production environment,
> and should only be used in development and test environments.
> Please see this article.
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "John Bell" wrote:
>> Hi
>> There are different options available depending on which version of SQL
>> Server you are running and what your system configuration is. With SQL
>> 7 -
>> 2005 you can use log shipping or replication. SQL 2005 has database
>> mirroring. There are also options that require specific hardware such as
>> failover clustering and SAN replication.
>> You may want to check out
>> http://www.microsoft.com/sql/technologies/highavailability/default.mspx
>> for
>> an introduction.
>> John
>> "JosephByrns" wrote:
>> > I would like to set up a new SQL server on a completely different box
>> > in a
>> > different location in the company (perhaps even offsite). I want this
>> > new
>> > server to be a mirror of the original SQL server, such that when data
>> > in the
>> > original server is updated the data in the new one is also updated
>> > etc...
>> > I would like to do this for security reasons, such that if the original
>> > server goes up in flames, I can recover all the data and quickly switch
>> > to
>> > the new server.
>> >
>> > I have only ever had to deal with one SQL server at once before, so
>> > what I
>> > want to know is:
>> >
>> > Is this the best way to ensure against catastrophic failure of the
>> > server.
>> > What do I need to read up on to achieve this.
>> >
>> > Thanks.
>> >
>> >
>> >|||Log shipping is only backup and restore. Can be done with any edition. But if you don't have EE, you
have to write your own jobs (or Google if someone has shared).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JosephByrns" <josephbyrns@.yahoo.com> wrote in message news:uCkLgGdTGHA.4952@.TK2MSFTNGP09.phx.gbl...
> OK, it looks like log shipping will do what I want, unfortunately it appears that Log Shipping is
> only available to SQL 2000 Enterprise (I have Professional), can you confirm this is true? Will I
> require enterprise for the replication approach?
> Thanks.
> "AndyP" <AndyP@.discussions.microsoft.com> wrote in message
> news:16EFB1CD-473F-4FE6-92FF-9B8D7979D1F9@.microsoft.com...
>> Bear in mind that there is a Microsoft article on Mirroring that stipulates
>> that they do not recommend database mirroring in a production environment,
>> and should only be used in development and test environments.
>> Please see this article.
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>>
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "John Bell" wrote:
>> Hi
>> There are different options available depending on which version of SQL
>> Server you are running and what your system configuration is. With SQL 7 -
>> 2005 you can use log shipping or replication. SQL 2005 has database
>> mirroring. There are also options that require specific hardware such as
>> failover clustering and SAN replication.
>> You may want to check out
>> http://www.microsoft.com/sql/technologies/highavailability/default.mspx for
>> an introduction.
>> John
>> "JosephByrns" wrote:
>> > I would like to set up a new SQL server on a completely different box in a
>> > different location in the company (perhaps even offsite). I want this new
>> > server to be a mirror of the original SQL server, such that when data in the
>> > original server is updated the data in the new one is also updated etc...
>> > I would like to do this for security reasons, such that if the original
>> > server goes up in flames, I can recover all the data and quickly switch to
>> > the new server.
>> >
>> > I have only ever had to deal with one SQL server at once before, so what I
>> > want to know is:
>> >
>> > Is this the best way to ensure against catastrophic failure of the server.
>> > What do I need to read up on to achieve this.
>> >
>> > Thanks.
>> >
>> >
>> >
>|||Great, thanks, I'll look into it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23nDpl8lTGHA.4308@.TK2MSFTNGP10.phx.gbl...
> Log shipping is only backup and restore. Can be done with any edition. But
> if you don't have EE, you have to write your own jobs (or Google if
> someone has shared).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JosephByrns" <josephbyrns@.yahoo.com> wrote in message
> news:uCkLgGdTGHA.4952@.TK2MSFTNGP09.phx.gbl...
>> OK, it looks like log shipping will do what I want, unfortunately it
>> appears that Log Shipping is only available to SQL 2000 Enterprise (I
>> have Professional), can you confirm this is true? Will I require
>> enterprise for the replication approach?
>> Thanks.
>> "AndyP" <AndyP@.discussions.microsoft.com> wrote in message
>> news:16EFB1CD-473F-4FE6-92FF-9B8D7979D1F9@.microsoft.com...
>> Bear in mind that there is a Microsoft article on Mirroring that
>> stipulates
>> that they do not recommend database mirroring in a production
>> environment,
>> and should only be used in development and test environments.
>> Please see this article.
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>>
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "John Bell" wrote:
>> Hi
>> There are different options available depending on which version of SQL
>> Server you are running and what your system configuration is. With SQL
>> 7 -
>> 2005 you can use log shipping or replication. SQL 2005 has database
>> mirroring. There are also options that require specific hardware such
>> as
>> failover clustering and SAN replication.
>> You may want to check out
>> http://www.microsoft.com/sql/technologies/highavailability/default.mspx
>> for
>> an introduction.
>> John
>> "JosephByrns" wrote:
>> > I would like to set up a new SQL server on a completely different box
>> > in a
>> > different location in the company (perhaps even offsite). I want
>> > this new
>> > server to be a mirror of the original SQL server, such that when data
>> > in the
>> > original server is updated the data in the new one is also updated
>> > etc...
>> > I would like to do this for security reasons, such that if the
>> > original
>> > server goes up in flames, I can recover all the data and quickly
>> > switch to
>> > the new server.
>> >
>> > I have only ever had to deal with one SQL server at once before, so
>> > what I
>> > want to know is:
>> >
>> > Is this the best way to ensure against catastrophic failure of the
>> > server.
>> > What do I need to read up on to achieve this.
>> >
>> > Thanks.
>> >
>> >
>> >
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0021_01C67CF9.86E39300
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
This is an outdated article.
Since Microsoft didn't strictly test data mirror on SQL 2005 RTM, so, =Microsoft doesn't recommend you carry out mirroring in a product =environment.
But now, as you know, SQL 2005 SP1 has been release on 18th, April. We =can complete implement
it in your product server.
"AndyP" <AndyP@.discussions.microsoft.com> wrote in message =news:16EFB1CD-473F-4FE6-92FF-9B8D7979D1F9@.microsoft.com...
Bear in mind that there is a Microsoft article on Mirroring that =stipulates that they do not recommend database mirroring in a production =environment, and should only be used in development and test environments.
Please see this article.
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
-- AndyP,
Sr. Database Administrator,
MCDBA 2003
"John Bell" wrote:
> Hi
> > There are different options available depending on which version of =SQL > Server you are running and what your system configuration is. With =SQL 7 - > 2005 you can use log shipping or replication. SQL 2005 has database > mirroring. There are also options that require specific hardware =such as > failover clustering and SAN replication.
> > You may want to check out > =http://www.microsoft.com/sql/technologies/highavailability/default.mspx =for > an introduction.
> > John
> > "JosephByrns" wrote:
> > > I would like to set up a new SQL server on a completely different =box in a > > different location in the company (perhaps even offsite). I want =this new > > server to be a mirror of the original SQL server, such that when =data in the > > original server is updated the data in the new one is also updated =etc... > > I would like to do this for security reasons, such that if the =original > > server goes up in flames, I can recover all the data and quickly =switch to > > the new server.
> > > > I have only ever had to deal with one SQL server at once before, =so what I > > want to know is:
> > > > Is this the best way to ensure against catastrophic failure of the =server.
> > What do I need to read up on to achieve this.
> > > > Thanks. > > > > > >
--=_NextPart_000_0021_01C67CF9.86E39300
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

This is an outdated article.
Since Microsoft =didn't strictly test data mirror on SQL 2005 RTM, so, Microsoft doesn't recommend you =carry out mirroring in a product environment.
But now, as you know, =SQL 2005 SP1 has been release on 18th, April. We can complete implement
it in your product server.
"AndyP" wrote in message news:16E=FB1CD-473F-4FE6-92FF-9B8D7979D1F9@.microsoft.com...Bear in mind that there is a Microsoft article on Mirroring that stipulates = that they do not recommend database mirroring in a production =environment, and should only be used in development and test environments.Please see this article.-- AndyP,Sr. Database Administrator,MCDBA =2003"John Bell" wrote:> Hi> > There are different options =available depending on which version of SQL > Server you are running and =what your system configuration is. With SQL 7 - > 2005 you can use =log shipping or replication. SQL 2005 has database > =mirroring. There are also options that require specific hardware such as > =failover clustering and SAN replication.> > You may want to check =out >http://www.microsoft.com/sql/technologies/highavailability/defaul=t.mspx for > an introduction.> > John> > "JosephByrns" wrote:> > > I would like to set up a =new SQL server on a completely different box in a > > different =location in the company (perhaps even offsite). I want this new > => server to be a mirror of the original SQL server, such that when data in the => > original server is updated the data in the new one is also =updated etc... > > I would like to do this for security reasons, =such that if the original > > server goes up in flames, I can recover =all the data and quickly switch to > > the new server.> > => > I have only ever had to deal with one SQL server at once before, =so what I > > want to know is:> > > > Is this =the best way to ensure against catastrophic failure of the server.> > =What do I need to read up on to achieve this.> > > > =Thanks. > > > > > >

--=_NextPart_000_0021_01C67CF9.86E39300--|||Hi
That was prior to SP1.
John
"AndyP" wrote:
> Bear in mind that there is a Microsoft article on Mirroring that stipulates
> that they do not recommend database mirroring in a production environment,
> and should only be used in development and test environments.
> Please see this article.
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "John Bell" wrote:
> > Hi
> >
> > There are different options available depending on which version of SQL
> > Server you are running and what your system configuration is. With SQL 7 -
> > 2005 you can use log shipping or replication. SQL 2005 has database
> > mirroring. There are also options that require specific hardware such as
> > failover clustering and SAN replication.
> >
> > You may want to check out
> > http://www.microsoft.com/sql/technologies/highavailability/default.mspx for
> > an introduction.
> >
> > John
> >
> > "JosephByrns" wrote:
> >
> > > I would like to set up a new SQL server on a completely different box in a
> > > different location in the company (perhaps even offsite). I want this new
> > > server to be a mirror of the original SQL server, such that when data in the
> > > original server is updated the data in the new one is also updated etc...
> > > I would like to do this for security reasons, such that if the original
> > > server goes up in flames, I can recover all the data and quickly switch to
> > > the new server.
> > >
> > > I have only ever had to deal with one SQL server at once before, so what I
> > > want to know is:
> > >
> > > Is this the best way to ensure against catastrophic failure of the server.
> > > What do I need to read up on to achieve this.
> > >
> > > Thanks.
> > >
> > >
> > >|||Hi
I believe there is a method of log shipping in the resource kit and there
has also been several articles on rolling your own log shipping in SQL Server
Magazine e.g.
http://www.sqlmag.com/Article/ArticleID/15805/Log_Shipping_with_SQL_Server_2000.html
John
"JosephByrns" wrote:
> Great, thanks, I'll look into it.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23nDpl8lTGHA.4308@.TK2MSFTNGP10.phx.gbl...
> > Log shipping is only backup and restore. Can be done with any edition. But
> > if you don't have EE, you have to write your own jobs (or Google if
> > someone has shared).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "JosephByrns" <josephbyrns@.yahoo.com> wrote in message
> > news:uCkLgGdTGHA.4952@.TK2MSFTNGP09.phx.gbl...
> >> OK, it looks like log shipping will do what I want, unfortunately it
> >> appears that Log Shipping is only available to SQL 2000 Enterprise (I
> >> have Professional), can you confirm this is true? Will I require
> >> enterprise for the replication approach?
> >>
> >> Thanks.
> >>
> >> "AndyP" <AndyP@.discussions.microsoft.com> wrote in message
> >> news:16EFB1CD-473F-4FE6-92FF-9B8D7979D1F9@.microsoft.com...
> >> Bear in mind that there is a Microsoft article on Mirroring that
> >> stipulates
> >> that they do not recommend database mirroring in a production
> >> environment,
> >> and should only be used in development and test environments.
> >>
> >> Please see this article.
> >>
> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
> >>
> >>
> >> --
> >> AndyP,
> >> Sr. Database Administrator,
> >> MCDBA 2003
> >>
> >>
> >> "John Bell" wrote:
> >>
> >> Hi
> >>
> >> There are different options available depending on which version of SQL
> >> Server you are running and what your system configuration is. With SQL
> >> 7 -
> >> 2005 you can use log shipping or replication. SQL 2005 has database
> >> mirroring. There are also options that require specific hardware such
> >> as
> >> failover clustering and SAN replication.
> >>
> >> You may want to check out
> >> http://www.microsoft.com/sql/technologies/highavailability/default.mspx
> >> for
> >> an introduction.
> >>
> >> John
> >>
> >> "JosephByrns" wrote:
> >>
> >> > I would like to set up a new SQL server on a completely different box
> >> > in a
> >> > different location in the company (perhaps even offsite). I want
> >> > this new
> >> > server to be a mirror of the original SQL server, such that when data
> >> > in the
> >> > original server is updated the data in the new one is also updated
> >> > etc...
> >> > I would like to do this for security reasons, such that if the
> >> > original
> >> > server goes up in flames, I can recover all the data and quickly
> >> > switch to
> >> > the new server.
> >> >
> >> > I have only ever had to deal with one SQL server at once before, so
> >> > what I
> >> > want to know is:
> >> >
> >> > Is this the best way to ensure against catastrophic failure of the
> >> > server.
> >> > What do I need to read up on to achieve this.
> >> >
> >> > Thanks.
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Friday, March 23, 2012

Minimum permissions for SQL2005 development?

Hi
I'm trying to get started developing on this 2005 box, but I'm running
into big security problems, because this is one of those shops where
getting the most minimal level of access to the dev server (even as a
developer who should supposedly be doing something productive for the
daily rate) is harder than getting Tony Blair to tell you the Trident
launch codes.
So at the moment even Management Studio won't work. And the security
administrators are not SQL DBAs - so it's basically up to me to work
out what access I need (db_owner/sa? For a developer, on the dev
box? Forget it!), tell them this, and then go through whatever
paperwork/string-pulling/bribery/black ops are necessary to actually
get it granted.
If it was 2000 I'd have some idea, but I'm relatively new to 2005 -
developed on a "friendly" box as sa before, but never run into this.
Anyone know what permissions I'd need to:
- Create/drop/execute objects in a DB (obviously, db_datawriter and
db_datareader as well)
- Execute system stored procedures (especially sp_executesql)
- Develop, test and run SS Integration Services?
I suspect that what I'm running into at the moment is lack of
permissions in master and msdb - but it's hard to work out, when my
effective server role is something like "Unauthenticated hacker".
Apologies for my ignorance - I've never run into this level of
paranoia before.
thanks for any ideas.
seb> Minimum permissions for SQL2005 development?
The obvious answer is that you need the minimal level of permissions need to
do your job :-)

> And the security
> administrators are not SQL DBAs - so it's basically up to me to work
> out what access I need (db_owner/sa?
One would think the security admins would need to know something about SQL
security if managing SQL security is part of their job. You should only
need to tell them what you need to do and they ought to be able to know
enough to give you those permissions and nothing more.
Sysadmin role members have full control over the entire SQL Server and will
provide full control over all databases. If you don't need to change server
configuration options, install service packs and that sort of thing,
sysadmin is probably overkill. db_owner is also a powerful role but may be
appropriate if you need *complete* control over some, but not all, databases
on the server.

> Anyone know what permissions I'd need to:
> - Create/drop/execute objects in a DB (obviously, db_datawriter and
> db_datareader as well)
You'll need CREATE permissions on the target object type and ALTER on the
object's schema. If you have CONTROL on the schema, you won't need
db_datawriter and db_datareader membership to data access in that schema.

> - Execute system stored procedures (especially sp_executesql)
You should already have execute permissions on sp_executesql because execute
permissions default to public and you access master via the guest user
context. If you don't have execute permissions, the default SQL Server
permissions were changed and permissions will need to be granted via other
means.

> - Develop, test and run SS Integration Services?
Like any application, you'll SQL permissions to access the objects used by
the package. If you use msdb as the package store, you'll need to be a
member of the appropriate role (db_dtsadmin or db_dtsltduser).
Hope this helps.
Dan Guzman
SQL Server MVP
"sebt" <sebthirlway@.hotmail.com> wrote in message
news:1175679014.583549.224630@.b75g2000hsg.googlegroups.com...
> Hi
> I'm trying to get started developing on this 2005 box, but I'm running
> into big security problems, because this is one of those shops where
> getting the most minimal level of access to the dev server (even as a
> developer who should supposedly be doing something productive for the
> daily rate) is harder than getting Tony Blair to tell you the Trident
> launch codes.
> So at the moment even Management Studio won't work. And the security
> administrators are not SQL DBAs - so it's basically up to me to work
> out what access I need (db_owner/sa? For a developer, on the dev
> box? Forget it!), tell them this, and then go through whatever
> paperwork/string-pulling/bribery/black ops are necessary to actually
> get it granted.
> If it was 2000 I'd have some idea, but I'm relatively new to 2005 -
> developed on a "friendly" box as sa before, but never run into this.
> Anyone know what permissions I'd need to:
> - Create/drop/execute objects in a DB (obviously, db_datawriter and
> db_datareader as well)
> - Execute system stored procedures (especially sp_executesql)
> - Develop, test and run SS Integration Services?
> I suspect that what I'm running into at the moment is lack of
> permissions in master and msdb - but it's hard to work out, when my
> effective server role is something like "Unauthenticated hacker".
> Apologies for my ignorance - I've never run into this level of
> paranoia before.
> thanks for any ideas.
>
> seb
>|||Hello Seb,
You need minimum level permission of db_ddladmin fixed database role so that
you will able to create objects in respective database, and always create
objects as DBO so that it won't result into broken ownership chain.
Goodluck!
Cheers,
MB
"sebt" <sebthirlway@.hotmail.com> wrote in message
news:1175679014.583549.224630@.b75g2000hsg.googlegroups.com...
> Hi
> I'm trying to get started developing on this 2005 box, but I'm running
> into big security problems, because this is one of those shops where
> getting the most minimal level of access to the dev server (even as a
> developer who should supposedly be doing something productive for the
> daily rate) is harder than getting Tony Blair to tell you the Trident
> launch codes.
> So at the moment even Management Studio won't work. And the security
> administrators are not SQL DBAs - so it's basically up to me to work
> out what access I need (db_owner/sa? For a developer, on the dev
> box? Forget it!), tell them this, and then go through whatever
> paperwork/string-pulling/bribery/black ops are necessary to actually
> get it granted.
> If it was 2000 I'd have some idea, but I'm relatively new to 2005 -
> developed on a "friendly" box as sa before, but never run into this.
> Anyone know what permissions I'd need to:
> - Create/drop/execute objects in a DB (obviously, db_datawriter and
> db_datareader as well)
> - Execute system stored procedures (especially sp_executesql)
> - Develop, test and run SS Integration Services?
> I suspect that what I'm running into at the moment is lack of
> permissions in master and msdb - but it's hard to work out, when my
> effective server role is something like "Unauthenticated hacker".
> Apologies for my ignorance - I've never run into this level of
> paranoia before.
> thanks for any ideas.
>
> seb
>

Friday, March 9, 2012

Migration replication jobs

Hi all,
I will be migrating a production database to a new box. I will be using
detach and attach db method to migrate, but I forsee that my replication job
will break. Can anyone advise me on how to migrate the replication jobs over
to the new db? Here is the sencario:
DB-1 in Box A detach and move over to Box B to be reattached,
DB-1 in Box A has some publications and subscriptions, and the distribution
is in Box C.
Let say Box B will resume the ip of Box A but different hostname.
You will be unable to detach replicated databases. You have to script out
the publications, edit them for the new server, remove all job names, and
then drop the publications, unpublish the databases and detach them. Attach
them on the new server, run the publication script and do a non sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"YK" <YK@.discussions.microsoft.com> wrote in message
news:25A6D0F1-6C2C-4B95-84F5-C96B963335D0@.microsoft.com...
> Hi all,
> I will be migrating a production database to a new box. I will be using
> detach and attach db method to migrate, but I forsee that my replication
job
> will break. Can anyone advise me on how to migrate the replication jobs
over
> to the new db? Here is the sencario:
> DB-1 in Box A detach and move over to Box B to be reattached,
> DB-1 in Box A has some publications and subscriptions, and the
distribution
> is in Box C.
> Let say Box B will resume the ip of Box A but different hostname.
>

Wednesday, March 7, 2012

Migration from SQL7 to SQL2000

Hi, here is my delema, I have SQL 7 running on a NT4 box and I want to move my databases over to a SQL 2000 Windows 2003 server. What is the best method. These are two different boxes. Any help is greatly appreciated.
Scott KeeganHave a look here:
http://msdn.microsoft.com/SQL/sqlmigratingdata/default.aspx
--
Andrew J. Kelly SQL MVP
"Scott Keegan" <Scott Keegan@.discussions.microsoft.com> wrote in message
news:3097FD7A-8F81-4BB9-A161-DE24A2FD40F6@.microsoft.com...
> Hi, here is my delema, I have SQL 7 running on a NT4 box and I want to
move my databases over to a SQL 2000 Windows 2003 server. What is the best
method. These are two different boxes. Any help is greatly appreciated.
> Scott Keegan|||Scott
Install SQL Server on Widows 2003 and perfom RESTORE all users databases.
It might be a collation conflict but since SQL Server 2000 you can change it
even per column.
"Scott Keegan" <Scott Keegan@.discussions.microsoft.com> wrote in message
news:3097FD7A-8F81-4BB9-A161-DE24A2FD40F6@.microsoft.com...
> Hi, here is my delema, I have SQL 7 running on a NT4 box and I want to
move my databases over to a SQL 2000 Windows 2003 server. What is the best
method. These are two different boxes. Any help is greatly appreciated.
> Scott Keegan|||Hi,
Migration from SQl 7 to SQl 2000 is straight forward, you can BACKUP and
RESTORE or use
sp_detach_db and sp_attach_db.
You could also probably also use the upgrade wizard .
Look into the below KB articles related to migration:
move the data via backup and restore
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304692
sp_detach_db and sp_attach_db
http://support.microsoft.com/support/kb/articles/q224/0/71.asp
one more option -- the copy database wizard
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274463
PRB: User Logon and/or Permission Errors After Restoring Dump (Q168001)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q168001
Thanks
Hari
MCDBA
"Scott Keegan" <Scott Keegan@.discussions.microsoft.com> wrote in message
news:3097FD7A-8F81-4BB9-A161-DE24A2FD40F6@.microsoft.com...
> Hi, here is my delema, I have SQL 7 running on a NT4 box and I want to
move my databases over to a SQL 2000 Windows 2003 server. What is the best
method. These are two different boxes. Any help is greatly appreciated.
> Scott Keegan|||Is there a priority order on a where clause? Or does SQL check all of the
where clause conditions all of the time? I have a case where I can,
sometimes, hit non-numeric data in a column (caused by some bad
conversions). Right now we have the following where clause:
WHERE convert(integer, ?) BETWEEN convert(integer, low_street_num) AND
convert(integer, high_street_num)
AND (street_name = ? OR street_alias_name = ?)
AND city = ?
AND state_cd = ?
AND zip_cd LIKE ?
AND (juris_cd = ? OR juris_cd IS NULL)
AND (side = ? OR side = 'B')
AND status_cd = 'A'
AND (side = ? OR side = 'B')
AND status_cd = 'A'
I am wondering if we were to change to put the status_cd = 'A' first would
the query skip all entries whose status_cd is not 'A' and not even try to do
the convert(integer) statement at all? Or is it going to try to do the
convert(integer) regardless and my only solution is to make some routine to
fix the non-numeric data or adjust this sql to ensure the data is numeric
before trying to do the convert?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com|||You cannot trust SQL Server to not hit the bad rows. Not even by pushing the filter inside a derived
table. I've been there. Put the stuff in a temp table and work against that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed Fultz" <efultz@.yahoo.com> wrote in message news:O6blHRSWEHA.4056@.TK2MSFTNGP11.phx.gbl...
> Is there a priority order on a where clause? Or does SQL check all of the
> where clause conditions all of the time? I have a case where I can,
> sometimes, hit non-numeric data in a column (caused by some bad
> conversions). Right now we have the following where clause:
> WHERE convert(integer, ?) BETWEEN convert(integer, low_street_num) AND
> convert(integer, high_street_num)
> AND (street_name = ? OR street_alias_name = ?)
> AND city = ?
> AND state_cd = ?
> AND zip_cd LIKE ?
> AND (juris_cd = ? OR juris_cd IS NULL)
> AND (side = ? OR side = 'B')
> AND status_cd = 'A'
> AND (side = ? OR side = 'B')
> AND status_cd = 'A'
> I am wondering if we were to change to put the status_cd = 'A' first would
> the query skip all entries whose status_cd is not 'A' and not even try to do
> the convert(integer) statement at all? Or is it going to try to do the
> convert(integer) regardless and my only solution is to make some routine to
> fix the non-numeric data or adjust this sql to ensure the data is numeric
> before trying to do the convert?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>|||You should be careful to test such a migration thoroughly:
We had a system running on MSSQL7 (MSDE) that we migrated
to MSSQL2000 Ent.Ed.
Restore finished fine, program seemded to run, but after
some days it crashed. Then nothing and then again. We
found out the following:
We built up SQL statements so string values were given as
char(65)+char(66)+...
Now, this sometimes produced queries as big as 8-9kB,
which MSSQL7 handled fine.
MSSQL2000, however, gave sql parse error. This shouldn't
be a problem until the length of the query reaches ~100kB.
Local Microsoft wanted to make this work badly, but
couldn't do it. We had to backtrack to MSSQL7.
>--Original Message--
>Hi, here is my delema, I have SQL 7 running on a NT4 box
and I want to move my databases over to a SQL 2000 Windows
2003 server. What is the best method. These are two
different boxes. Any help is greatly appreciated.
>Scott Keegan
>.
>

Friday, March 30, 2012

Mirroring confusion.

Mirroring confusion.

Mirroring confusion.

Monday, March 26, 2012

mirror

mirror

mirror

Friday, March 23, 2012

Minimum permissions for SQL2005 development?

Friday, March 9, 2012

Migration replication jobs

Wednesday, March 7, 2012

Migration from SQL7 to SQL2000

Migration from SQL7 to SQL2000

Migration from SQL7 to SQL2000

Migrating to SQL 2005

Blog Archive

About Me