Sql Auto grow: standard

Showing posts with label standard. Show all posts

Thursday, March 29, 2012

Automation of database restoration

We have sql server standard edition , I want to automate the task of data
restoration on a daily basis .
How can I automate copying and restoration of the data on standalone PC .
Kindly suggest me the best option for the same .
Regards,
SwatiI assume that you have two database servers and you want to keep them in
synch.
You have to do two things
BACKUP your database(s) on your primary server
RESTORE your backups to your standby server.
It is simple to set up a job which issues BACKUP commands. In fact you
should be doing that now as part of your backup routine.
The RESTORE is easy. Assuming that the account that SQL Server is running
as on your second server has rights to the machine where your primary
backups are stored you can do something like this: RESTORE DATABASE foo FROM
DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
If you have a share on the server you can access the share:
RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak' WITH
REPLACE
If your database servers have a different disk layout you might have to use
the WITH MOVE option to move the physical files to a location that works on
your secondary machine.
Once you are able to issue the appropriate BACKUP and RESTORE commands the
next thing you have to do is create a couple of jobs which will automate the
process for you. You can start a job (on another server) from your main
server, but the easiest method might be to have the jobs be independent of
each other. Schedule your backup job to run at, say 10pm. Lets say that it
takes 30 minutes. You could schedule the restore job on the other server to
start at 10:45pm.
You may find these articles helpful:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
Keith
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> We have sql server standard edition , I want to automate the task of data
> restoration on a daily basis .
> How can I automate copying and restoration of the data on standalone PC .
> Kindly suggest me the best option for the same .
>
> Regards,
> Swati
>
>|||Keith,
Thanks for response.
I agree that we can write manual commands for restoring the database on
standby server . Let me explain you our backup stategy
Full backup : 12:00 AM
Diff backup : evry 2 hours
Txn log backup : every 15 minute .this is done using DB maintenance plan .
so every time we get a new file. e.g mydb_db_200410260000.bak
Now I want to restore my full backup and diff backup which is done at
interval of 2 hours . How can I restore my full db backup and diff backup.
Regards,
Swati
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> I assume that you have two database servers and you want to keep them in
> synch.
> You have to do two things
> BACKUP your database(s) on your primary server
> RESTORE your backups to your standby server.
> It is simple to set up a job which issues BACKUP commands. In fact you
> should be doing that now as part of your backup routine.
> The RESTORE is easy. Assuming that the account that SQL Server is running
> as on your second server has rights to the machine where your primary
> backups are stored you can do something like this: RESTORE DATABASE foo
FROM
> DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> If you have a share on the server you can access the share:
> RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak' WITH
> REPLACE
> If your database servers have a different disk layout you might have to
use
> the WITH MOVE option to move the physical files to a location that works
on
> your secondary machine.
> Once you are able to issue the appropriate BACKUP and RESTORE commands the
> next thing you have to do is create a couple of jobs which will automate
the
> process for you. You can start a job (on another server) from your main
> server, but the easiest method might be to have the jobs be independent of
> each other. Schedule your backup job to run at, say 10pm. Lets say that
it
> takes 30 minutes. You could schedule the restore job on the other server
to
> start at 10:45pm.
> You may find these articles helpful:
>
> 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> http://support.microsoft.com/?id=314515
> 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
> Paper)
> http://support.microsoft.com/?id=323135
> 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> http://support.microsoft.com/?id=325220
> 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> http://support.microsoft.com/?id=821786
> 321247 HOW TO: Configure Security for Log Shipping
> http://support.microsoft.com/?id=321247
> 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> Errors
> http://support.microsoft.com/?id=329133
>
> --
> Keith
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> > We have sql server standard edition , I want to automate the task of
data
> > restoration on a daily basis .
> > How can I automate copying and restoration of the data on standalone PC
.
> > Kindly suggest me the best option for the same .
> >
> >
> > Regards,
> > Swati
> >
> >
> >
> >
>|||swati
Create a job that does RESTORE full and DIFF database
For more details please refer to the BOL
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:upWyC21uEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Keith,
> Thanks for response.
> I agree that we can write manual commands for restoring the database on
> standby server . Let me explain you our backup stategy
> Full backup : 12:00 AM
> Diff backup : evry 2 hours
> Txn log backup : every 15 minute .this is done using DB maintenance plan .
> so every time we get a new file. e.g mydb_db_200410260000.bak
> Now I want to restore my full backup and diff backup which is done at
> interval of 2 hours . How can I restore my full db backup and diff backup.
> Regards,
> Swati
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> > I assume that you have two database servers and you want to keep them in
> > synch.
> >
> > You have to do two things
> > BACKUP your database(s) on your primary server
> > RESTORE your backups to your standby server.
> >
> > It is simple to set up a job which issues BACKUP commands. In fact you
> > should be doing that now as part of your backup routine.
> > The RESTORE is easy. Assuming that the account that SQL Server is
running
> > as on your second server has rights to the machine where your primary
> > backups are stored you can do something like this: RESTORE DATABASE foo
> FROM
> > DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> > If you have a share on the server you can access the share:
> > RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak'
WITH
> > REPLACE
> >
> > If your database servers have a different disk layout you might have to
> use
> > the WITH MOVE option to move the physical files to a location that works
> on
> > your secondary machine.
> >
> > Once you are able to issue the appropriate BACKUP and RESTORE commands
the
> > next thing you have to do is create a couple of jobs which will automate
> the
> > process for you. You can start a job (on another server) from your main
> > server, but the easiest method might be to have the jobs be independent
of
> > each other. Schedule your backup job to run at, say 10pm. Lets say
that
> it
> > takes 30 minutes. You could schedule the restore job on the other
server
> to
> > start at 10:45pm.
> >
> > You may find these articles helpful:
> >
> >
> > 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> > http://support.microsoft.com/?id=314515
> >
> > 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping
(White
> > Paper)
> > http://support.microsoft.com/?id=323135
> >
> > 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> > http://support.microsoft.com/?id=325220
> >
> > 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> > http://support.microsoft.com/?id=821786
> >
> > 321247 HOW TO: Configure Security for Log Shipping
> > http://support.microsoft.com/?id=321247
> >
> > 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> > Errors
> > http://support.microsoft.com/?id=329133
> >
> >
> > --
> > Keith
> >
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> > > We have sql server standard edition , I want to automate the task of
> data
> > > restoration on a daily basis .
> > > How can I automate copying and restoration of the data on standalone
PC
> .
> > > Kindly suggest me the best option for the same .
> > >
> > >
> > > Regards,
> > > Swati
> > >
> > >
> > >
> > >
> >
>|||If you want to use the second server for Disaster Recovery, you should also
write a script to export your logins from the source SQL Server to the
Destination SQL Server.
--
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Uri Dimant" wrote:
> swati
> Create a job that does RESTORE full and DIFF database
> For more details please refer to the BOL
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:upWyC21uEHA.3276@.TK2MSFTNGP15.phx.gbl...
> >
> > Keith,
> > Thanks for response.
> >
> > I agree that we can write manual commands for restoring the database on
> > standby server . Let me explain you our backup stategy
> > Full backup : 12:00 AM
> > Diff backup : evry 2 hours
> > Txn log backup : every 15 minute .this is done using DB maintenance plan .
> > so every time we get a new file. e.g mydb_db_200410260000.bak
> >
> > Now I want to restore my full backup and diff backup which is done at
> > interval of 2 hours . How can I restore my full db backup and diff backup.
> >
> > Regards,
> > Swati
> >
> > "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> > news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> > > I assume that you have two database servers and you want to keep them in
> > > synch.
> > >
> > > You have to do two things
> > > BACKUP your database(s) on your primary server
> > > RESTORE your backups to your standby server.
> > >
> > > It is simple to set up a job which issues BACKUP commands. In fact you
> > > should be doing that now as part of your backup routine.
> > > The RESTORE is easy. Assuming that the account that SQL Server is
> running
> > > as on your second server has rights to the machine where your primary
> > > backups are stored you can do something like this: RESTORE DATABASE foo
> > FROM
> > > DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> > > If you have a share on the server you can access the share:
> > > RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak'
> WITH
> > > REPLACE
> > >
> > > If your database servers have a different disk layout you might have to
> > use
> > > the WITH MOVE option to move the physical files to a location that works
> > on
> > > your secondary machine.
> > >
> > > Once you are able to issue the appropriate BACKUP and RESTORE commands
> the
> > > next thing you have to do is create a couple of jobs which will automate
> > the
> > > process for you. You can start a job (on another server) from your main
> > > server, but the easiest method might be to have the jobs be independent
> of
> > > each other. Schedule your backup job to run at, say 10pm. Lets say
> that
> > it
> > > takes 30 minutes. You could schedule the restore job on the other
> server
> > to
> > > start at 10:45pm.
> > >
> > > You may find these articles helpful:
> > >
> > >
> > > 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> > > http://support.microsoft.com/?id=314515
> > >
> > > 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping
> (White
> > > Paper)
> > > http://support.microsoft.com/?id=323135
> > >
> > > 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> > > http://support.microsoft.com/?id=325220
> > >
> > > 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> > > http://support.microsoft.com/?id=821786
> > >
> > > 321247 HOW TO: Configure Security for Log Shipping
> > > http://support.microsoft.com/?id=321247
> > >
> > > 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> > > Errors
> > > http://support.microsoft.com/?id=329133
> > >
> > >
> > > --
> > > Keith
> > >
> > >
> > > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > > news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> > > > We have sql server standard edition , I want to automate the task of
> > data
> > > > restoration on a daily basis .
> > > > How can I automate copying and restoration of the data on standalone
> PC
> > .
> > > > Kindly suggest me the best option for the same .
> > > >
> > > >
> > > > Regards,
> > > > Swati
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
>sql

Automating deployment of maintenance plans

Hello,

I have created a Maintenance Plan on our development SQL Server 2005 Standard using the designer in SQL Server Management Studio. The plan backs up databases and transaction logs to a hard disk and does some cleanup too. It is scheduled to run nightly. This plan needs to be deployed to 13 production sites by someone else not familiar with SQL Server.

Can I use some combination of a SQL script, an export of the maintenance plan, and/or a batch file to automate the deployment of this plan and it's schedule to servers at several different sites? The deployment team will have admin remote desktop access to the production SQL Servers, which also have SQL Management Studio installed but we cannot expect the team to recreate the plan manually on each site.

I haven't been able to find much documentation on doing this automatically. Any help will be appreciated.

Thank you,

- Jason

Create a SSIS package to perform this maintenance plan task and use DTUTIL to deploy on multiple servers.

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE fyi.

sql

Thursday, March 22, 2012

Automatic XSD generation

Hello everyone,
Is there standard functionality to generate XSD files, which i can use
from within a program?
If so, please let me know where i can find more information.
Thanks
'I'm a raven, aren't I?' it said. 'One of the few birds who speak. The first
thing people say is, oh, you're a raven, go on, say the N word... If I had a
penny for every time that's happened, I'd-'
(Soul Music)
There are quite a few ways to handle XSDs.
From SQL, using XMLDATA attaches the Schema to your results. If you run a
query like:
SELECT * FROM Customers
FOR XML AUTO, XMLDATA
you will have a schema attached that is attribute centric. You can change to
elements like:
SELECT * FROM Customers
FOR XML AUTO, ELEMENTS, XMLDATA
But, you still have a schema attached to the top of an XML snippet (no root
tag). If you just want schema, you end up having to remove the records.
Something like:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
You are still missing root tag, however, which makes the XSD only
semi-useful, IMO.
With .NET, you can create a DataSet and return the XML Schema. This creates
an XSD that is valid. BUT, you end up with the extra weight of the DataSet
tags that surround your table of records. This is not a huge burden, overall,
but it can add a bit of extra work to those consuming your XML outside of the
..NET platform. It is, overall, easier than working directly with the SQL
"autogen" capabilities.

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Mark Pustjens" wrote:

> Hello everyone,
> Is there standard functionality to generate XSD files, which i can use
> from within a program?
> If so, please let me know where i can find more information.
> Thanks
> --
> 'I'm a raven, aren't I?' it said. 'One of the few birds who speak. The first
> thing people say is, oh, you're a raven, go on, say the N word... If I had a
> penny for every time that's happened, I'd-'
> (Soul Music)
>
|||Note that the XMLDATA directive generates the older XDR format and not the
XSD format.
Best regards
Michael
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message news:0888E260-B1EC-49D5-B7BE-0FD4A383156E@.microsoft.com...[vbcol=seagreen]
> There are quite a few ways to handle XSDs.
> From SQL, using XMLDATA attaches the Schema to your results. If you run a
> query like:
> SELECT * FROM Customers
> FOR XML AUTO, XMLDATA
> you will have a schema attached that is attribute centric. You can change
> to
> elements like:
> SELECT * FROM Customers
> FOR XML AUTO, ELEMENTS, XMLDATA
> But, you still have a schema attached to the top of an XML snippet (no
> root
> tag). If you just want schema, you end up having to remove the records.
> Something like:
> SELECT * FROM Customers
> WHERE 1 = 0
> FOR XML AUTO, ELEMENTS, XMLDATA
> You are still missing root tag, however, which makes the XSD only
> semi-useful, IMO.
> With .NET, you can create a DataSet and return the XML Schema. This
> creates
> an XSD that is valid. BUT, you end up with the extra weight of the DataSet
> tags that surround your table of records. This is not a huge burden,
> overall,
> but it can add a bit of extra work to those consuming your XML outside of
> the
> .NET platform. It is, overall, easier than working directly with the SQL
> "autogen" capabilities.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
> "Mark Pustjens" wrote:

Automatic XSD generation

Hello everyone,
Is there standard functionality to generate XSD files, which i can use
from within a program?
If so, please let me know where i can find more information.
Thanks
'I'm a raven, aren't I?' it said. 'One of the few birds who speak. The first
thing people say is, oh, you're a raven, go on, say the N word... If I had a
penny for every time that's happened, I'd-'
(Soul Music)There are quite a few ways to handle XSDs.
From SQL, using XMLDATA attaches the Schema to your results. If you run a
query like:
SELECT * FROM Customers
FOR XML AUTO, XMLDATA
you will have a schema attached that is attribute centric. You can change to
elements like:
SELECT * FROM Customers
FOR XML AUTO, ELEMENTS, XMLDATA
But, you still have a schema attached to the top of an XML snippet (no root
tag). If you just want schema, you end up having to remove the records.
Something like:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
You are still missing root tag, however, which makes the XSD only
semi-useful, IMO.
With .NET, you can create a DataSet and return the XML Schema. This creates
an XSD that is valid. BUT, you end up with the extra weight of the DataSet
tags that surround your table of records. This is not a huge burden, overall
,
but it can add a bit of extra work to those consuming your XML outside of th
e
.NET platform. It is, overall, easier than working directly with the SQL
"autogen" capabilities.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Mark Pustjens" wrote:

> Hello everyone,
> Is there standard functionality to generate XSD files, which i can use
> from within a program?
> If so, please let me know where i can find more information.
> Thanks
> --
> 'I'm a raven, aren't I?' it said. 'One of the few birds who speak. The fir
st
> thing people say is, oh, you're a raven, go on, say the N word... If I had
a
> penny for every time that's happened, I'd-'
> (Soul Music)
>|||Note that the XMLDATA directive generates the older XDR format and not the
XSD format.
Best regards
Michael
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message news:0888E260-B1EC-49D5-B7BE-0FD4A383156E@.microsoft.com...
> There are quite a few ways to handle XSDs.
> From SQL, using XMLDATA attaches the Schema to your results. If you run a
> query like:
> SELECT * FROM Customers
> FOR XML AUTO, XMLDATA
> you will have a schema attached that is attribute centric. You can change
> to
> elements like:
> SELECT * FROM Customers
> FOR XML AUTO, ELEMENTS, XMLDATA
> But, you still have a schema attached to the top of an XML snippet (no
> root
> tag). If you just want schema, you end up having to remove the records.
> Something like:
> SELECT * FROM Customers
> WHERE 1 = 0
> FOR XML AUTO, ELEMENTS, XMLDATA
> You are still missing root tag, however, which makes the XSD only
> semi-useful, IMO.
> With .NET, you can create a DataSet and return the XML Schema. This
> creates
> an XSD that is valid. BUT, you end up with the extra weight of the DataSet
> tags that surround your table of records. This is not a huge burden,
> overall,
> but it can add a bit of extra work to those consuming your XML outside of
> the
> .NET platform. It is, overall, easier than working directly with the SQL
> "autogen" capabilities.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
> "Mark Pustjens" wrote:
>

Sunday, March 11, 2012

Automatic failover with SQL Server Standard?

Hi,
We are trying to implement SQL server failover by using
Windows 2003 "Enterprise" clustering and SQL
Server "Standard" to try to save the cost of SQL Server
Etnerprise. From what I understand we cannot have
automatic failover of the SQL server service with this
configuration.
Could someone please explaing to me techinically why we
cannot do this:
- Implement W2003 Enterprise OS clustering.
- Install SQL server "standard" installed on both machines
in the OS cluster.
- Have the SQL server service start on the primary node in
the OS cluster and manage the database. Then ...
as part of the OS cluster confifuration, if the primary
node goes down, request that the SQL server service starts
up on the backup node as part of the OS failover.
Can't the clients continue to talk to the SQL server
database since it is not started up on the backup node and
has the same IP address?
Thanks!
As you rightly said, SQL Server 2000 Failover Clustering is NOT supported with SQL Server 2000 Standard Edition. It is fully supported on SQL Server 2000 Enterprise Edition.
You can install and configure any non-cluster aware application as a generic service on a cluster. You can even make notepad clustered as a generic service in a cluster if you like.
I have not seen anybody doing following this approach (using sql server 2000 std edition as a generic service). I wouldn't recommend it. This will be like a custom solution and if you have any issues with the failover
working properly, the support that you will get will be very limited. If you have to work with SQL Server 2000 Standard Edition then I would recommend exploring SQL Server Replication.
To get an idea of what SQL Server 2000 EE Failover Clustering does please refer to the following whitepaper.
Microsoft Whitepaper - SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/pro.../failclus.mspx
Cluster Support Policy
Microsoft's Cluster support policy is below
Cluster (General): http://support.microsoft.com/default...b;en-us;309395
Cluster (SQL): http://support.microsoft.com/default...b;en-us;327518
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Note that Veritas support clustering SQL2000 Standard. When I've used it,
it's been just using VCS and not involving MCS at all but I believe it can
sit on top if it as well. I think they've rebranded recently so its called
Storage Foundation High Availability rather than Veritas Cluster Server to
reflect its reliance on Veritas Volume Manager (which is now called Storage
Foundation). I actually prefer the Veritas cluster management tools to the
MS tools and it's a lot cheaper than going to Enterprise Edition if the only
reason you need Enterprise is clustering (and not performance/large memory
support etc).
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:2ec3601c46d96$f335fc60$a601280a@.phx.gbl...
> Hi,
> We are trying to implement SQL server failover by using
> Windows 2003 "Enterprise" clustering and SQL
> Server "Standard" to try to save the cost of SQL Server
> Etnerprise. From what I understand we cannot have
> automatic failover of the SQL server service with this
> configuration.
> Could someone please explaing to me techinically why we
> cannot do this:
> - Implement W2003 Enterprise OS clustering.
> - Install SQL server "standard" installed on both machines
> in the OS cluster.
> - Have the SQL server service start on the primary node in
> the OS cluster and manage the database. Then ...
> as part of the OS cluster confifuration, if the primary
> node goes down, request that the SQL server service starts
> up on the backup node as part of the OS failover.
> Can't the clients continue to talk to the SQL server
> database since it is not started up on the backup node and
> has the same IP address?
> Thanks!
>

Automatic deletion to allow import

Greetings All
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won'
t
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:

>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won
't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs wil
l
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:

> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:

>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:

> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:

>Well we have successfully done a copy already and found that if we wanted t
o
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQ
L
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:

> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>

Automatic deletion to allow import

Greetings All
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won't
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs will
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Greetings All
> >
> >I'm new to SQL. I'm using Standard edition (SP3)
> >I have recently had to sort out the import of a Unisys dbs into our SQL
> >server. This dbs needs to be imported a minimum of 3 times per week. It won't
> >overwrite an existing dbs so the only way I can think to do this is by
> >deleting the existing dbs before the scheduled import. I know Unisys can
> >provide a componant that allows synchronisation but as with everything that
> >costs mucho money.
> >
> >Is there a was to delete the existing dbs automatically so that the dbs will
> >be imported through Data Transformation Services, local packages?
> >
> >Or am I, due to my lack of knowledge going about this the wrong way.
> >
> >Andy
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Thanks Roy
> >
> >dbs = database (Sorry for confusing you)
> >
> >The problem is we have at least 150 tables.
> >A coleague found the "Drop" command .. do you know if this could be used?
> >
> >Andy
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Well we have successfully done a copy already and found that if we wanted to
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQL
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:
> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Well we have successfully done a copy already and found that if we wanted to
> >keep the database uptodate we needed to copy 3 times per week (for our
> >purposes) If the database exists it fails to copy. So we had looked at
> >deleting manually but then someone suggested that if we could incorporate
> >this in the import then that would solve it all in one go.
> >
> >Basically I'm just trying to achive a copy of the Unisys database to the SQL
> >server using DTS on a schedual.
> >
> >As for the table definitions I have a colleague who may have the skills
> >required. If we achive this can the import into existing tables be done via
> >DTS?
> >
> >Andy
>

Thursday, March 29, 2012

Automation of database restoration

Automation of database restoration

Automation of database restoration

Automating deployment of maintenance plans

Thursday, March 22, 2012

Automatic XSD generation

Automatic XSD generation

Sunday, March 11, 2012

Automatic failover with SQL Server Standard?

Automatic deletion to allow import

Automatic deletion to allow import

Sql Auto grow

Blog Archive

About Me