I am currently looking for a reporting solution for my company. I am tore
between RS and Crystal. We are primarily a MS shop with enterprise
licensing.
Business Objects (BO) has presented me with a really nice set of tools aside
from their reporting solution. Aside from reporting I would like to get
more involved with BI (dataming and cube analysis). At some point I would
like to have the end users browse the cube/universe data for quick adhoc
analysis.
BO presented me with really nice web intelligence software which is all
integrated with the BO product line. The products were: BO Web
Intelligence, BO Predictive Analysis, BO Set Analysis. These products seem
easy to use and the data can all be analyzed through the web once the
developer creates the middle layer.
I know MS has SSAS which contains the cube technology and data mining.
Do they offer a packaged product ready for enduser use similiar to BO?
Do you need to program your needs in the BI environment to develop what your
looking for?
Is there a way to get the SSAS cubes designed for end users to access
through the web/intranet?
Thanks.Basically you need to see what exactly you require. Both offer same things
but MS is cost effective solution and one stop solution. You also get
everything packged and moreover the SSRS comes free with Enterprise version
of SQL SERVER. So if you are planning for a small / mid size then this will
be ideal for you.
Amarnath
"Brian Shannon" wrote:
> I am currently looking for a reporting solution for my company. I am tore
> between RS and Crystal. We are primarily a MS shop with enterprise
> licensing.
> Business Objects (BO) has presented me with a really nice set of tools aside
> from their reporting solution. Aside from reporting I would like to get
> more involved with BI (dataming and cube analysis). At some point I would
> like to have the end users browse the cube/universe data for quick adhoc
> analysis.
> BO presented me with really nice web intelligence software which is all
> integrated with the BO product line. The products were: BO Web
> Intelligence, BO Predictive Analysis, BO Set Analysis. These products seem
> easy to use and the data can all be analyzed through the web once the
> developer creates the middle layer.
> I know MS has SSAS which contains the cube technology and data mining.
> Do they offer a packaged product ready for enduser use similiar to BO?
> Do you need to program your needs in the BI environment to develop what your
> looking for?
> Is there a way to get the SSAS cubes designed for end users to access
> through the web/intranet?
> Thanks.
>
>|||"Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
news:O2$FcrnDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> BO presented me with really nice web intelligence software which is all
> integrated with the BO product line. The products were: BO Web
> Intelligence, BO Predictive Analysis, BO Set Analysis. These products
> seem easy to use and the data can all be analyzed through the web once the
> developer creates the middle layer.
> I know MS has SSAS which contains the cube technology and data mining.
> Do they offer a packaged product ready for enduser use similiar to BO?
> Do you need to program your needs in the BI environment to develop what
> your looking for?
Take a look at Microsoft Performance Point 2007, as it will help you do much
of what you're looking for.
http://office.microsoft.com/en-us/performancepoint/FX101680481033.aspx
Hope this helps.
Ted|||Hey, Brian
One of Microsoft's biggest ambitions is to be leading the Business
Intelligence (BI) arena. In 2007, they have announced the release of
several new products and integration with the Office suite (especially
Excel). I would really consider the Microsoft suite of BI offerings
that will be coming up in the next year. Like Ted said,
PerformancePoint will be a key product to get into BIPM, and will allow
users to view scorecard technology, data mining, cube analysis, etc.
They will be leveraging a lot of their BI offerings with Office and
SharePoint, in order to provide the end users access to data w/ tools
like Excel and their web browser (IE) which they're already familiar
with and comfortable using.
Since your company is a MS shop, and they already run SQL Server, the
license for Reporting Services is included, so you can have a pretty
good reporting engine up and running fairly quickly. Reporting
Services can connect to several data sources including OLTP and OLAP,
such as SQL Server DB, Analysis Services cubes, Oracle, XML web
services (RS 2005), and in the SP2 of SQL 2005, they will be adding
support for Hyperion. If you already have cubes setup in an Analysis
Services database, you can simply write the MDX (or use the MDX query
generator in RS 2005) for the report query, then design your report
layout, and the learning curve for Reporting Services is fairly
minimal.
I have seen a lot of companies that used Crystal reports as their main
reporting tool, move away from that and migrate all their reports to
Reporting Services because the licensing is much more cost effective --
it's included w/ their SQL Server license!
I would definitely recommend spending some time in the MS BI web site
to learn about their future offerings in that area.
(http://www.microsoft.com/bi)
Regards,
Thiago Silva, MCAD.NET
On Nov 22, 10:09 pm, "Ted Malone" <ted.nospam.mal...@.gmail.com> wrote:
> "Brian Shannon" <brian.shan...@.diamondjo.com> wrote in messagenews:O2$FcrnDHHA.1220@.TK2MSFTNGP04.phx.gbl...
> > BO presented me with really nice web intelligence software which is all
> > integrated with the BO product line. The products were: BO Web
> > Intelligence, BO Predictive Analysis, BO Set Analysis. These products
> > seem easy to use and the data can all be analyzed through the web once the
> > developer creates the middle layer.
> > I know MS has SSAS which contains the cube technology and data mining.
> > Do they offer a packaged product ready for enduser use similiar to BO?
> > Do you need to program your needs in the BI environment to develop what
> > your looking for?Take a look at Microsoft Performance Point 2007, as it will help you do much
> of what you're looking for.
> http://office.microsoft.com/en-us/performancepoint/FX101680481033.aspx
> Hope this helps.
> Ted
Showing posts with label company. Show all posts
Showing posts with label company. Show all posts
Sunday, March 11, 2012
BI Vendors
My company is looking at several vendors of BI software for our data
warehouse. We have never had a data warehouse before and are learning
everything from scratch. Does anyone have any opinions on the
following vendors...
Panorama
Proclarity
Cognos
Business Objects
Information Builders
We are hoping to bring in someone who has lots of DW experience to
help us decide on one of these vendors. But, we are interested in
opinions of people who have experience with these vendors.
Thanks for the help!!
--Lori
Hi Lori,
ProClarity & Panorama are very similar products but given the 2 I would
always go with Panorama. I prefer the look-and-feel and has some nice
functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
Did you get all the information you wanted from your same post on
sqlservercentral.com?
cheers
Jamie
"LoriB" wrote:
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
>
|||Jamie,
Thanks for the info. And, I did get a lot of good opinions in my
other post. I wanted to try to get to as many people as I could so I
posted in several different places.
--Lori
"Jamie" <Jamie@.discussions.microsoft.com> wrote in message news:<1F3F614A-8EEF-4061-9159-2BA25277C12D@.microsoft.com>...
> Hi Lori,
> ProClarity & Panorama are very similar products but given the 2 I would
> always go with Panorama. I prefer the look-and-feel and has some nice
> functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
> Did you get all the information you wanted from your same post on
> sqlservercentral.com?
> cheers
> Jamie
|||the answer is never clear, this depend on budget, functionalities required,
number of users...
if you have 100 users and 2 analysts the choice is different from 98
analysts and 2 viewers!
do you use Analysis Services cubes? (so focus on AS compatible applications)
or there is only an SQL Server database for the moment?
AS is required for Proclarity and Panorama. and optional for Cognos, BO and
Info Builders.
do you need to support AS features like actions, drill through, writeback?
* focus on Proclarity, panorama, and some of the AS compatible applications
what type of calculation do you need?
* distinct count are difficult to support outside AS and Cognos Cubes.
* complex calculations required (like: average of the parent level if the
user look at th day, but sum at the year level...) -> AS or Microstrategy
what is the expertise of your users?
* Very low : Info. Builder, Microstrategy (viewer) and Cognos Reportnet
(viewer only) (maybe BO, but the price is high for simply accessing a
report)
* Average : Proclarity / panorama / Cognos Powerplay
* High: Microstrategy / Proclarity / panorama / Bo (WebIntelligence) /
Cognos reportnet (designer role)
Do you need to create high quality and printable reports?
* Microstrategy / BO / Cognos. BO is very good for this, other the web or in
client/server.
The other tools are more analysis oriented than reporting oriented
what is the level of security required?
* Microstrategy and AS cubes are great, so its very easy to open the doors
to your users. Personally, I've never open the doors of BO and Cognos
because the data security is hard to setup. (except in simple viewer
accesses)
There is other interesting tools:
Reportportal; winsight (http://www.winsight.fr/en/index.htm); Arcplan
dynasight; Hyperion etc...
Test reportportal online to see what an AS tool can do. you'll find some of
these features in proclarity and panorama.
I've found the Microstratgy administration tools very good. deploying a
project, or comparing 2 projects is easy.
Cognos reportnet do a great job too.
The BO repository is bad. the organization of the reports and the management
of the published reports is not easy. all the other tools works like a
secure shared folder, where each item published can be secured.
other way to analyse: SDK and integration.
do you need to integrate your data warehouse in home made application?
calculate the price of the SDK if you need it ;-)
Cognos Powerplay cubes cannot be accessed by 3rd party tools.
Microstrategy provides an OLEDB for Olap driver which allow an Excel (or
procalrity, panorama...) users to access it.
Now I prefer to start a project with Excel as the end user tool. and after
I'll work with the users to find the right tool.
Because my users says : I need to analyse, to report to do everything!!! but
after they do nothing because its too complex!
Also each tool is very different from the concurrent, specially between BO
(ad-hoc reporting, scorecard with application fundation)/ Cognos (pushing,
web access, olap access) / Microstrategy (high performance, data security,
analysis capabilities), because each company provides its own OLAP/reporting
engine.
Also, think SQL 2005, the OLAP capabilites are great, so I recommand to
focus on a tools which support AS2005 in the future.
so... good luck in your job! :-)
and welcome in the DW world!
(its late, and my english starts to become very bad... so... ;-) )
"LoriB" <loribrown48@.hotmail.com> a crit dans le message de news:
848869d7.0408131126.46fce96c@.posting.google.com...
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
warehouse. We have never had a data warehouse before and are learning
everything from scratch. Does anyone have any opinions on the
following vendors...
Panorama
Proclarity
Cognos
Business Objects
Information Builders
We are hoping to bring in someone who has lots of DW experience to
help us decide on one of these vendors. But, we are interested in
opinions of people who have experience with these vendors.
Thanks for the help!!
--Lori
Hi Lori,
ProClarity & Panorama are very similar products but given the 2 I would
always go with Panorama. I prefer the look-and-feel and has some nice
functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
Did you get all the information you wanted from your same post on
sqlservercentral.com?
cheers
Jamie
"LoriB" wrote:
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
>
|||Jamie,
Thanks for the info. And, I did get a lot of good opinions in my
other post. I wanted to try to get to as many people as I could so I
posted in several different places.
--Lori
"Jamie" <Jamie@.discussions.microsoft.com> wrote in message news:<1F3F614A-8EEF-4061-9159-2BA25277C12D@.microsoft.com>...
> Hi Lori,
> ProClarity & Panorama are very similar products but given the 2 I would
> always go with Panorama. I prefer the look-and-feel and has some nice
> functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
> Did you get all the information you wanted from your same post on
> sqlservercentral.com?
> cheers
> Jamie
|||the answer is never clear, this depend on budget, functionalities required,
number of users...
if you have 100 users and 2 analysts the choice is different from 98
analysts and 2 viewers!
do you use Analysis Services cubes? (so focus on AS compatible applications)
or there is only an SQL Server database for the moment?
AS is required for Proclarity and Panorama. and optional for Cognos, BO and
Info Builders.
do you need to support AS features like actions, drill through, writeback?
* focus on Proclarity, panorama, and some of the AS compatible applications
what type of calculation do you need?
* distinct count are difficult to support outside AS and Cognos Cubes.
* complex calculations required (like: average of the parent level if the
user look at th day, but sum at the year level...) -> AS or Microstrategy
what is the expertise of your users?
* Very low : Info. Builder, Microstrategy (viewer) and Cognos Reportnet
(viewer only) (maybe BO, but the price is high for simply accessing a
report)
* Average : Proclarity / panorama / Cognos Powerplay
* High: Microstrategy / Proclarity / panorama / Bo (WebIntelligence) /
Cognos reportnet (designer role)
Do you need to create high quality and printable reports?
* Microstrategy / BO / Cognos. BO is very good for this, other the web or in
client/server.
The other tools are more analysis oriented than reporting oriented
what is the level of security required?
* Microstrategy and AS cubes are great, so its very easy to open the doors
to your users. Personally, I've never open the doors of BO and Cognos
because the data security is hard to setup. (except in simple viewer
accesses)
There is other interesting tools:
Reportportal; winsight (http://www.winsight.fr/en/index.htm); Arcplan
dynasight; Hyperion etc...
Test reportportal online to see what an AS tool can do. you'll find some of
these features in proclarity and panorama.
I've found the Microstratgy administration tools very good. deploying a
project, or comparing 2 projects is easy.
Cognos reportnet do a great job too.
The BO repository is bad. the organization of the reports and the management
of the published reports is not easy. all the other tools works like a
secure shared folder, where each item published can be secured.
other way to analyse: SDK and integration.
do you need to integrate your data warehouse in home made application?
calculate the price of the SDK if you need it ;-)
Cognos Powerplay cubes cannot be accessed by 3rd party tools.
Microstrategy provides an OLEDB for Olap driver which allow an Excel (or
procalrity, panorama...) users to access it.
Now I prefer to start a project with Excel as the end user tool. and after
I'll work with the users to find the right tool.
Because my users says : I need to analyse, to report to do everything!!! but
after they do nothing because its too complex!
Also each tool is very different from the concurrent, specially between BO
(ad-hoc reporting, scorecard with application fundation)/ Cognos (pushing,
web access, olap access) / Microstrategy (high performance, data security,
analysis capabilities), because each company provides its own OLAP/reporting
engine.
Also, think SQL 2005, the OLAP capabilites are great, so I recommand to
focus on a tools which support AS2005 in the future.
so... good luck in your job! :-)
and welcome in the DW world!
(its late, and my english starts to become very bad... so... ;-) )
"LoriB" <loribrown48@.hotmail.com> a crit dans le message de news:
848869d7.0408131126.46fce96c@.posting.google.com...
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
BI Vendors
My company is looking at several vendors of BI software for our data
warehouse. We have never had a data warehouse before and are learning
everything from scratch. Does anyone have any opinions on the
following vendors...
Panorama
Proclarity
Cognos
Business Objects
Information Builders
We are hoping to bring in someone who has lots of DW experience to
help us decide on one of these vendors. But, we are interested in
opinions of people who have experience with these vendors.
Thanks for the help!!
--LoriHi Lori,
ProClarity & Panorama are very similar products but given the 2 I would
always go with Panorama. I prefer the look-and-feel and has some nice
functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
Did you get all the information you wanted from your same post on
sqlservercentral.com?
cheers
Jamie
"LoriB" wrote:
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
>|||Jamie,
Thanks for the info. And, I did get a lot of good opinions in my
other post. I wanted to try to get to as many people as I could so I
posted in several different places.
--Lori
"Jamie" <Jamie@.discussions.microsoft.com> wrote in message news:<1F3F614A-8EEF-4061-9159-2BA
25277C12D@.microsoft.com>...
> Hi Lori,
> ProClarity & Panorama are very similar products but given the 2 I would
> always go with Panorama. I prefer the look-and-feel and has some nice
> functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
> Did you get all the information you wanted from your same post on
> sqlservercentral.com?
> cheers
> Jamie|||the answer is never clear, this depend on budget, functionalities required,
number of users...
if you have 100 users and 2 analysts the choice is different from 98
analysts and 2 viewers!
do you use Analysis Services cubes? (so focus on AS compatible applications)
or there is only an SQL Server database for the moment?
AS is required for Proclarity and Panorama. and optional for Cognos, BO and
Info Builders.
do you need to support AS features like actions, drill through, writeback?
* focus on Proclarity, panorama, and some of the AS compatible applications
what type of calculation do you need?
* distinct count are difficult to support outside AS and Cognos Cubes.
* complex calculations required (like: average of the parent level if the
user look at th day, but sum at the year level...) -> AS or Microstrategy
what is the expertise of your users?
* Very low : Info. Builder, Microstrategy (viewer) and Cognos Reportnet
(viewer only) (maybe BO, but the price is high for simply accessing a
report)
* Average : Proclarity / panorama / Cognos Powerplay
* High: Microstrategy / Proclarity / panorama / Bo (WebIntelligence) /
Cognos reportnet (designer role)
Do you need to create high quality and printable reports?
* Microstrategy / BO / Cognos. BO is very good for this, other the web or in
client/server.
The other tools are more analysis oriented than reporting oriented
what is the level of security required?
* Microstrategy and AS cubes are great, so its very easy to open the doors
to your users. Personally, I've never open the doors of BO and Cognos
because the data security is hard to setup. (except in simple viewer
accesses)
There is other interesting tools:
Reportportal; winsight (http://www.winsight.fr/en/index.htm); Arcplan
dynasight; Hyperion etc...
Test reportportal online to see what an AS tool can do. you'll find some of
these features in proclarity and panorama.
I've found the Microstratgy administration tools very good. deploying a
project, or comparing 2 projects is easy.
Cognos reportnet do a great job too.
The BO repository is bad. the organization of the reports and the management
of the published reports is not easy. all the other tools works like a
secure shared folder, where each item published can be secured.
other way to analyse: SDK and integration.
do you need to integrate your data warehouse in home made application?
calculate the price of the SDK if you need it ;-)
Cognos Powerplay cubes cannot be accessed by 3rd party tools.
Microstrategy provides an OLEDB for Olap driver which allow an Excel (or
procalrity, panorama...) users to access it.
Now I prefer to start a project with Excel as the end user tool. and after
I'll work with the users to find the right tool.
Because my users says : I need to analyse, to report to do everything!!! but
after they do nothing because its too complex!
Also each tool is very different from the concurrent, specially between BO
(ad-hoc reporting, scorecard with application fundation)/ Cognos (pushing,
web access, olap access) / Microstrategy (high performance, data security,
analysis capabilities), because each company provides its own OLAP/reporting
engine.
Also, think SQL 2005, the OLAP capabilites are great, so I recommand to
focus on a tools which support AS2005 in the future.
so... good luck in your job! :-)
and welcome in the DW world!
(its late, and my english starts to become very bad... so... ;-) )
"LoriB" <loribrown48@.hotmail.com> a crit dans le message de news:
848869d7.0408131126.46fce96c@.posting.google.com...
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
warehouse. We have never had a data warehouse before and are learning
everything from scratch. Does anyone have any opinions on the
following vendors...
Panorama
Proclarity
Cognos
Business Objects
Information Builders
We are hoping to bring in someone who has lots of DW experience to
help us decide on one of these vendors. But, we are interested in
opinions of people who have experience with these vendors.
Thanks for the help!!
--LoriHi Lori,
ProClarity & Panorama are very similar products but given the 2 I would
always go with Panorama. I prefer the look-and-feel and has some nice
functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
Did you get all the information you wanted from your same post on
sqlservercentral.com?
cheers
Jamie
"LoriB" wrote:
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
>|||Jamie,
Thanks for the info. And, I did get a lot of good opinions in my
other post. I wanted to try to get to as many people as I could so I
posted in several different places.
--Lori
"Jamie" <Jamie@.discussions.microsoft.com> wrote in message news:<1F3F614A-8EEF-4061-9159-2BA
25277C12D@.microsoft.com>...
> Hi Lori,
> ProClarity & Panorama are very similar products but given the 2 I would
> always go with Panorama. I prefer the look-and-feel and has some nice
> functionality (e.g. Bubble-Up exceptions) that ProClarity doesn't.
> Did you get all the information you wanted from your same post on
> sqlservercentral.com?
> cheers
> Jamie|||the answer is never clear, this depend on budget, functionalities required,
number of users...
if you have 100 users and 2 analysts the choice is different from 98
analysts and 2 viewers!
do you use Analysis Services cubes? (so focus on AS compatible applications)
or there is only an SQL Server database for the moment?
AS is required for Proclarity and Panorama. and optional for Cognos, BO and
Info Builders.
do you need to support AS features like actions, drill through, writeback?
* focus on Proclarity, panorama, and some of the AS compatible applications
what type of calculation do you need?
* distinct count are difficult to support outside AS and Cognos Cubes.
* complex calculations required (like: average of the parent level if the
user look at th day, but sum at the year level...) -> AS or Microstrategy
what is the expertise of your users?
* Very low : Info. Builder, Microstrategy (viewer) and Cognos Reportnet
(viewer only) (maybe BO, but the price is high for simply accessing a
report)
* Average : Proclarity / panorama / Cognos Powerplay
* High: Microstrategy / Proclarity / panorama / Bo (WebIntelligence) /
Cognos reportnet (designer role)
Do you need to create high quality and printable reports?
* Microstrategy / BO / Cognos. BO is very good for this, other the web or in
client/server.
The other tools are more analysis oriented than reporting oriented
what is the level of security required?
* Microstrategy and AS cubes are great, so its very easy to open the doors
to your users. Personally, I've never open the doors of BO and Cognos
because the data security is hard to setup. (except in simple viewer
accesses)
There is other interesting tools:
Reportportal; winsight (http://www.winsight.fr/en/index.htm); Arcplan
dynasight; Hyperion etc...
Test reportportal online to see what an AS tool can do. you'll find some of
these features in proclarity and panorama.
I've found the Microstratgy administration tools very good. deploying a
project, or comparing 2 projects is easy.
Cognos reportnet do a great job too.
The BO repository is bad. the organization of the reports and the management
of the published reports is not easy. all the other tools works like a
secure shared folder, where each item published can be secured.
other way to analyse: SDK and integration.
do you need to integrate your data warehouse in home made application?
calculate the price of the SDK if you need it ;-)
Cognos Powerplay cubes cannot be accessed by 3rd party tools.
Microstrategy provides an OLEDB for Olap driver which allow an Excel (or
procalrity, panorama...) users to access it.
Now I prefer to start a project with Excel as the end user tool. and after
I'll work with the users to find the right tool.
Because my users says : I need to analyse, to report to do everything!!! but
after they do nothing because its too complex!
Also each tool is very different from the concurrent, specially between BO
(ad-hoc reporting, scorecard with application fundation)/ Cognos (pushing,
web access, olap access) / Microstrategy (high performance, data security,
analysis capabilities), because each company provides its own OLAP/reporting
engine.
Also, think SQL 2005, the OLAP capabilites are great, so I recommand to
focus on a tools which support AS2005 in the future.
so... good luck in your job! :-)
and welcome in the DW world!
(its late, and my english starts to become very bad... so... ;-) )
"LoriB" <loribrown48@.hotmail.com> a crit dans le message de news:
848869d7.0408131126.46fce96c@.posting.google.com...
> My company is looking at several vendors of BI software for our data
> warehouse. We have never had a data warehouse before and are learning
> everything from scratch. Does anyone have any opinions on the
> following vendors...
> Panorama
> Proclarity
> Cognos
> Business Objects
> Information Builders
> We are hoping to bring in someone who has lots of DW experience to
> help us decide on one of these vendors. But, we are interested in
> opinions of people who have experience with these vendors.
> Thanks for the help!!
> --Lori
BI Strategy
I have been charged with implementing a BI strategy for my company. As I am new to BI I have been researching BI quite a bit but still feel overwelmed by all the information out there. As far as I have researched, I found many front-end tools that my company can use. I would like to set up some sort of data warehouse. My company will be using scorecards, dashboards, mining, KPIs and analytics. Most of my data is in SQL 2000, some is in SQL 2005. I am looking for the best process to setup the data warehouse and am having a hard time finding the right approach. Some people suggest cubes and others suggest to stay away from cubes and use an OLAP process. I do need a way to keep the data up to date as well, preferably at least daily. Does anyone have thoughts on an approach or 2?
This site(http://www.kimballgroup.com/) is my primary reference for DW-Architechture.
The Kimball group have a book that combines their general project approach(that is above all suppliers) with the approach of how to implement it on the Microsoft BI-platform.
Have a look at "The Microsoft DataWareHouse Toolkit".
HTH
Thomas Ivarsson
|||Hello again Shawn. A cube and OLAP is the same. If you update your warehouse daily a cube can solve part of your business problem.
Never start a scorecard project without having a the experience of building a data warehouse before that.
HTH
Thomas Ivarsson
|||Shawn,
6 months ago I was pretty much in the same boat as you. I found that book that Thomas mentioned superb in helping me to implement our DW solution using Microsoft BI Toolkit. I can highly recommend it.
What you should also do is to (having gotten an overview from the Kimball book) buy books on the various specialist areas, i.e. SSAS, SSRS, SSIS - here i found the ones from WROX to be very useful..
Professional SQL Server 2005 Integration Services
Professional SQL Server 2005 Analysis Services
Professional SQL Server 2005 Reporting Services
As they tend to go in to alot more detail than Kimball's book (which is more conceptual) and provide code & examples to show you how it's done "step by step"
BI Strategy
I have been charged with implementing a BI strategy for my company. As I am new to BI I have been researching BI quite a bit but still feel overwelmed by all the information out there. As far as I have researched, I found many front-end tools that my company can use. I would like to set up some sort of data warehouse. My company will be using scorecards, dashboards, mining, KPIs and analytics. Most of my data is in SQL 2000, some is in SQL 2005. I am looking for the best process to setup the data warehouse and am having a hard time finding the right approach. Some people suggest cubes and others suggest to stay away from cubes and use an OLAP process. I do need a way to keep the data up to date as well, preferably at least daily. Does anyone have thoughts on an approach or 2?
This site(http://www.kimballgroup.com/) is my primary reference for DW-Architechture.
The Kimball group have a book that combines their general project approach(that is above all suppliers) with the approach of how to implement it on the Microsoft BI-platform.
Have a look at "The Microsoft DataWareHouse Toolkit".
HTH
Thomas Ivarsson
|||Hello again Shawn. A cube and OLAP is the same. If you update your warehouse daily a cube can solve part of your business problem.
Never start a scorecard project without having a the experience of building a data warehouse before that.
HTH
Thomas Ivarsson
|||Shawn,
6 months ago I was pretty much in the same boat as you. I found that book that Thomas mentioned superb in helping me to implement our DW solution using Microsoft BI Toolkit. I can highly recommend it.
What you should also do is to (having gotten an overview from the Kimball book) buy books on the various specialist areas, i.e. SSAS, SSRS, SSIS - here i found the ones from WROX to be very useful..
Professional SQL Server 2005 Integration Services
Professional SQL Server 2005 Analysis Services
Professional SQL Server 2005 Reporting Services
As they tend to go in to alot more detail than Kimball's book (which is more conceptual) and provide code & examples to show you how it's done "step by step"
Thursday, March 8, 2012
BI Accelerator: multiply source databeses
Hi All!
I'm trying to use MS BI Accelerator for creating DW which must contain data
from different sources. Every source is our company departmnet standard
database. I'm looking for best practice for collecting fact and dim data
from this databases in BI Acc staging database, i.e. creating Source Data
ETLM process using Master_Import and its sub - DTS packages with mimimal
re-writing of its. For example, I need to get customers for Dim_Customer_Std
dimension table from Department_1, then Department_2 and so on for other
dept's and dim's. Fact table must be populated the same way - sales data
from Department_1 must be consolidated with Department_2 ...
What is the best way to do so: create a different sub-packages for every
department and then add Execute Package task into Master_Import package or
exist another way?
If you read the PAG, you will see that the Master Import packages were
design as a convienence for customers wanting to load from flat files. We
fully expected that customers will need to load the staging database with
their own data (possibly from multiple data sources). You need to implement
that piece of the system yourself, i.e. come up with your own "Master
Import" where the data comes from your own data sources. Then you plug that
in place of Master Import. The PAG also discusses ways that you could make
simple changes to the Master Import packages if what you want to do is
similar to what Master Import does -- in your case, this does not seem to
apply, so you would just replace Master Import with your own system -- then
Master Update takes the data from the staging database and moves it through
the system from there.
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Hi All!
> I'm trying to use MS BI Accelerator for creating DW which must contain
data
> from different sources. Every source is our company departmnet standard
> database. I'm looking for best practice for collecting fact and dim data
> from this databases in BI Acc staging database, i.e. creating Source Data
> ETLM process using Master_Import and its sub - DTS packages with mimimal
> re-writing of its. For example, I need to get customers for
Dim_Customer_Std
> dimension table from Department_1, then Department_2 and so on for other
> dept's and dim's. Fact table must be populated the same way - sales data
> from Department_1 must be consolidated with Department_2 ...
> What is the best way to do so: create a different sub-packages for every
> department and then add Execute Package task into Master_Import package or
> exist another way?
>
|||Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
with MS BI Accelerator (Development, Deployment and Maintenance).
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
: news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
> If you read the PAG, you will see that the Master Import packages were
> design as a convienence for customers wanting to load from flat files. We
> fully expected that customers will need to load the staging database with
> their own data (possibly from multiple data sources). You need to
implement
> that piece of the system yourself, i.e. come up with your own "Master
> Import" where the data comes from your own data sources. Then you plug
that
> in place of Master Import. The PAG also discusses ways that you could make
> simple changes to the Master Import packages if what you want to do is
> similar to what Master Import does -- in your case, this does not seem to
> apply, so you would just replace Master Import with your own system --
then
> Master Update takes the data from the staging database and moves it
through
> the system from there.
> Hope that helps.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Eugene Frolov" <john@.alice.ru> wrote in message
> news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> data
data[vbcol=seagreen]
Data[vbcol=seagreen]
> Dim_Customer_Std
or
>
|||That is what we call the PAG (Prescriptive Architecture Guides).
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:OMgGTkztEHA.2788@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
> with MS BI Accelerator (Development, Deployment and Maintenance).
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
> : news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
We[vbcol=seagreen]
with[vbcol=seagreen]
> implement
> that
make[vbcol=seagreen]
to[vbcol=seagreen]
> then
> through
> rights.
standard[vbcol=seagreen]
> data
> Data
mimimal[vbcol=seagreen]
other[vbcol=seagreen]
data[vbcol=seagreen]
every[vbcol=seagreen]
package
> or
>
I'm trying to use MS BI Accelerator for creating DW which must contain data
from different sources. Every source is our company departmnet standard
database. I'm looking for best practice for collecting fact and dim data
from this databases in BI Acc staging database, i.e. creating Source Data
ETLM process using Master_Import and its sub - DTS packages with mimimal
re-writing of its. For example, I need to get customers for Dim_Customer_Std
dimension table from Department_1, then Department_2 and so on for other
dept's and dim's. Fact table must be populated the same way - sales data
from Department_1 must be consolidated with Department_2 ...
What is the best way to do so: create a different sub-packages for every
department and then add Execute Package task into Master_Import package or
exist another way?
If you read the PAG, you will see that the Master Import packages were
design as a convienence for customers wanting to load from flat files. We
fully expected that customers will need to load the staging database with
their own data (possibly from multiple data sources). You need to implement
that piece of the system yourself, i.e. come up with your own "Master
Import" where the data comes from your own data sources. Then you plug that
in place of Master Import. The PAG also discusses ways that you could make
simple changes to the Master Import packages if what you want to do is
similar to what Master Import does -- in your case, this does not seem to
apply, so you would just replace Master Import with your own system -- then
Master Update takes the data from the staging database and moves it through
the system from there.
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Hi All!
> I'm trying to use MS BI Accelerator for creating DW which must contain
data
> from different sources. Every source is our company departmnet standard
> database. I'm looking for best practice for collecting fact and dim data
> from this databases in BI Acc staging database, i.e. creating Source Data
> ETLM process using Master_Import and its sub - DTS packages with mimimal
> re-writing of its. For example, I need to get customers for
Dim_Customer_Std
> dimension table from Department_1, then Department_2 and so on for other
> dept's and dim's. Fact table must be populated the same way - sales data
> from Department_1 must be consolidated with Department_2 ...
> What is the best way to do so: create a different sub-packages for every
> department and then add Execute Package task into Master_Import package or
> exist another way?
>
|||Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
with MS BI Accelerator (Development, Deployment and Maintenance).
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
: news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
> If you read the PAG, you will see that the Master Import packages were
> design as a convienence for customers wanting to load from flat files. We
> fully expected that customers will need to load the staging database with
> their own data (possibly from multiple data sources). You need to
implement
> that piece of the system yourself, i.e. come up with your own "Master
> Import" where the data comes from your own data sources. Then you plug
that
> in place of Master Import. The PAG also discusses ways that you could make
> simple changes to the Master Import packages if what you want to do is
> similar to what Master Import does -- in your case, this does not seem to
> apply, so you would just replace Master Import with your own system --
then
> Master Update takes the data from the staging database and moves it
through
> the system from there.
> Hope that helps.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Eugene Frolov" <john@.alice.ru> wrote in message
> news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> data
data[vbcol=seagreen]
Data[vbcol=seagreen]
> Dim_Customer_Std
or
>
|||That is what we call the PAG (Prescriptive Architecture Guides).
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:OMgGTkztEHA.2788@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
> with MS BI Accelerator (Development, Deployment and Maintenance).
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
> : news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
We[vbcol=seagreen]
with[vbcol=seagreen]
> implement
> that
make[vbcol=seagreen]
to[vbcol=seagreen]
> then
> through
> rights.
standard[vbcol=seagreen]
> data
> Data
mimimal[vbcol=seagreen]
other[vbcol=seagreen]
data[vbcol=seagreen]
every[vbcol=seagreen]
package
> or
>
BI Accelerator: multiply source databeses
Hi All!
I'm trying to use MS BI Accelerator for creating DW which must contain data
from different sources. Every source is our company departmnet standard
database. I'm looking for best practice for collecting fact and dim data
from this databases in BI Acc staging database, i.e. creating Source Data
ETLM process using Master_Import and its sub - DTS packages with mimimal
re-writing of its. For example, I need to get customers for Dim_Customer_Std
dimension table from Department_1, then Department_2 and so on for other
dept's and dim's. Fact table must be populated the same way - sales data
from Department_1 must be consolidated with Department_2 ...
What is the best way to do so: create a different sub-packages for every
department and then add Execute Package task into Master_Import package or
exist another way?If you read the PAG, you will see that the Master Import packages were
design as a convienence for customers wanting to load from flat files. We
fully expected that customers will need to load the staging database with
their own data (possibly from multiple data sources). You need to implement
that piece of the system yourself, i.e. come up with your own "Master
Import" where the data comes from your own data sources. Then you plug that
in place of Master Import. The PAG also discusses ways that you could make
simple changes to the Master Import packages if what you want to do is
similar to what Master Import does -- in your case, this does not seem to
apply, so you would just replace Master Import with your own system -- then
Master Update takes the data from the staging database and moves it through
the system from there.
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Hi All!
> I'm trying to use MS BI Accelerator for creating DW which must contain
data
> from different sources. Every source is our company departmnet standard
> database. I'm looking for best practice for collecting fact and dim data
> from this databases in BI Acc staging database, i.e. creating Source Data
> ETLM process using Master_Import and its sub - DTS packages with mimimal
> re-writing of its. For example, I need to get customers for
Dim_Customer_Std
> dimension table from Department_1, then Department_2 and so on for other
> dept's and dim's. Fact table must be populated the same way - sales data
> from Department_1 must be consolidated with Department_2 ...
> What is the best way to do so: create a different sub-packages for every
> department and then add Execute Package task into Master_Import package or
> exist another way?
>|||Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
with MS BI Accelerator (Development, Deployment and Maintenance).
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
: news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
> If you read the PAG, you will see that the Master Import packages were
> design as a convienence for customers wanting to load from flat files. We
> fully expected that customers will need to load the staging database with
> their own data (possibly from multiple data sources). You need to
implement
> that piece of the system yourself, i.e. come up with your own "Master
> Import" where the data comes from your own data sources. Then you plug
that
> in place of Master Import. The PAG also discusses ways that you could make
> simple changes to the Master Import packages if what you want to do is
> similar to what Master Import does -- in your case, this does not seem to
> apply, so you would just replace Master Import with your own system --
then
> Master Update takes the data from the staging database and moves it
through
> the system from there.
> Hope that helps.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Eugene Frolov" <john@.alice.ru> wrote in message
> news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> data
data[vbcol=seagreen]
Data[vbcol=seagreen]
> Dim_Customer_Std
or[vbcol=seagreen]
>|||That is what we call the PAG (Prescriptive Architecture Guides).
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:OMgGTkztEHA.2788@.TK2MSFTNGP09.phx.gbl...
> Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
> with MS BI Accelerator (Development, Deployment and Maintenance).
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
> : news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
We[vbcol=seagreen]
with[vbcol=seagreen]
> implement
> that
make[vbcol=seagreen]
to[vbcol=seagreen]
> then
> through
> rights.
standard[vbcol=seagreen]
> data
> Data
mimimal[vbcol=seagreen]
other[vbcol=seagreen]
data[vbcol=seagreen]
every[vbcol=seagreen]
package[vbcol=seagreen]
> or
>
I'm trying to use MS BI Accelerator for creating DW which must contain data
from different sources. Every source is our company departmnet standard
database. I'm looking for best practice for collecting fact and dim data
from this databases in BI Acc staging database, i.e. creating Source Data
ETLM process using Master_Import and its sub - DTS packages with mimimal
re-writing of its. For example, I need to get customers for Dim_Customer_Std
dimension table from Department_1, then Department_2 and so on for other
dept's and dim's. Fact table must be populated the same way - sales data
from Department_1 must be consolidated with Department_2 ...
What is the best way to do so: create a different sub-packages for every
department and then add Execute Package task into Master_Import package or
exist another way?If you read the PAG, you will see that the Master Import packages were
design as a convienence for customers wanting to load from flat files. We
fully expected that customers will need to load the staging database with
their own data (possibly from multiple data sources). You need to implement
that piece of the system yourself, i.e. come up with your own "Master
Import" where the data comes from your own data sources. Then you plug that
in place of Master Import. The PAG also discusses ways that you could make
simple changes to the Master Import packages if what you want to do is
similar to what Master Import does -- in your case, this does not seem to
apply, so you would just replace Master Import with your own system -- then
Master Update takes the data from the staging database and moves it through
the system from there.
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Hi All!
> I'm trying to use MS BI Accelerator for creating DW which must contain
data
> from different sources. Every source is our company departmnet standard
> database. I'm looking for best practice for collecting fact and dim data
> from this databases in BI Acc staging database, i.e. creating Source Data
> ETLM process using Master_Import and its sub - DTS packages with mimimal
> re-writing of its. For example, I need to get customers for
Dim_Customer_Std
> dimension table from Department_1, then Department_2 and so on for other
> dept's and dim's. Fact table must be populated the same way - sales data
> from Department_1 must be consolidated with Department_2 ...
> What is the best way to do so: create a different sub-packages for every
> department and then add Execute Package task into Master_Import package or
> exist another way?
>|||Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
with MS BI Accelerator (Development, Deployment and Maintenance).
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
: news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
> If you read the PAG, you will see that the Master Import packages were
> design as a convienence for customers wanting to load from flat files. We
> fully expected that customers will need to load the staging database with
> their own data (possibly from multiple data sources). You need to
implement
> that piece of the system yourself, i.e. come up with your own "Master
> Import" where the data comes from your own data sources. Then you plug
that
> in place of Master Import. The PAG also discusses ways that you could make
> simple changes to the Master Import packages if what you want to do is
> similar to what Master Import does -- in your case, this does not seem to
> apply, so you would just replace Master Import with your own system --
then
> Master Update takes the data from the staging database and moves it
through
> the system from there.
> Hope that helps.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Eugene Frolov" <john@.alice.ru> wrote in message
> news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> data
data[vbcol=seagreen]
Data[vbcol=seagreen]
> Dim_Customer_Std
or[vbcol=seagreen]
>|||That is what we call the PAG (Prescriptive Architecture Guides).
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:OMgGTkztEHA.2788@.TK2MSFTNGP09.phx.gbl...
> Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
> with MS BI Accelerator (Development, Deployment and Maintenance).
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
> : news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
We[vbcol=seagreen]
with[vbcol=seagreen]
> implement
> that
make[vbcol=seagreen]
to[vbcol=seagreen]
> then
> through
> rights.
standard[vbcol=seagreen]
> data
> Data
mimimal[vbcol=seagreen]
other[vbcol=seagreen]
data[vbcol=seagreen]
every[vbcol=seagreen]
package[vbcol=seagreen]
> or
>
Sunday, February 19, 2012
Best way to secure an exposed SQL Server
Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pass
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications? I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!
"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)
> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications? I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / Hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)
> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.
|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%230eFHA.1404@.TK2MSFTNGP09.p hx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / Hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pass
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications? I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!
"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)
> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications? I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / Hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)
> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.
|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%230eFHA.1404@.TK2MSFTNGP09.p hx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / Hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>
Best way to secure an exposed SQL Server
Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pas
s
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications' I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)
> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications' I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)
> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%23
0eFHA.1404@.TK2MSFTNGP09.phx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pas
s
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications' I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)
> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications' I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)
> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%23
0eFHA.1404@.TK2MSFTNGP09.phx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>
Best way to secure an exposed SQL Server
Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pass
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications' I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)
> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications' I am able to
> design hardware solutions to assist with this too!
I wouldn´t code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / Hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)
> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l¥ó·s»D:Olhu%23%230eFHA.1404@.TK2MSFTNGP09.phx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
>> Hi experts,
>> I have an application that my company designed and sold to users years
>> ago
>> that requires SQL port 1433 to be open to the Internet. (Insert .. shame
>> a
>> you here!).
> There you are :-)
>> There is basically a straight NAT statement from our firewall to the SQL
>> server that allows ALL inbound traffic to SQL. I am looking for the best
>> method to secure this connection from the application side (it is under
>> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
>> front end server to autheticate the SQL users first, change the port and
>> pass
>> to the back-end sql server?... I dont know really... can someone
>> please
>> provide me with some design suggestions to take to my developers in order
>> to
>> secure and encrypt there SQL sessions for their appications' I am able
>> to
>> design hardware solutions to assist with this too!
> I wouldn´t code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / Hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>> I know its best practices to NOT have SQL open, but this late in the
>> game,
>> it would take a miracle to get all our customers to change ports. Thanks
>> for
>> your timely suggestions!!
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pass
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications' I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)
> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications' I am able to
> design hardware solutions to assist with this too!
I wouldn´t code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / Hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)
> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l¥ó·s»D:Olhu%23%230eFHA.1404@.TK2MSFTNGP09.phx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
>> Hi experts,
>> I have an application that my company designed and sold to users years
>> ago
>> that requires SQL port 1433 to be open to the Internet. (Insert .. shame
>> a
>> you here!).
> There you are :-)
>> There is basically a straight NAT statement from our firewall to the SQL
>> server that allows ALL inbound traffic to SQL. I am looking for the best
>> method to secure this connection from the application side (it is under
>> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
>> front end server to autheticate the SQL users first, change the port and
>> pass
>> to the back-end sql server?... I dont know really... can someone
>> please
>> provide me with some design suggestions to take to my developers in order
>> to
>> secure and encrypt there SQL sessions for their appications' I am able
>> to
>> design hardware solutions to assist with this too!
> I wouldn´t code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / Hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>> I know its best practices to NOT have SQL open, but this late in the
>> game,
>> it would take a miracle to get all our customers to change ports. Thanks
>> for
>> your timely suggestions!!
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>
Best way to push this data? (LONG)
There's a lot of explanation involved in this, so bear with the long
post:
Our company has offices in the US and China, and an AS400 (where the
data originates) in the US, and 3 SQL boxes (which mirror the AS400
data, with the odd extra field thrown in) - 2 in the US, and one in
China.
We're changing our item number scheme from a numeric field to a 25-
character char field. We're doing this over Memorial Day weekend...
we're dropping replication and everything on Friday, and then the AS400
starts its conversion process, and sometime late Saturday or early
Sunday the conversion process will be done on the AS400, at which point
we start copying the data over to our local SQL box, and then over to
China.
China starts its day Sunday night at 7pm. For that day, they will be in
read-only mode, so that they won't be writing changes to their old,
outdated tables. The downside to being read-only is we also cannot push
data to them (since they're using the data) until their end-of-day at
approximately 4am Monday. This gives us a very small window to push the
data over, re-establish replication, make sure everything works fine,
etc. By the way, I'm talking about several gig worth of data, at least
12gb and as much as 20gb.
One of the suggestions was to create a scratch-db containing all the
data, and disconnecting that db and transferring the mdf & ldf files
over the pond during the middle of our night, so at 5am they would be
sitting there ready for us. We could then detach our existing db, and
attach the new one in its place. The problem with that suggestion is
that once we re-establish replication, how would it know which data
already exists in China? Our fear is that replication will drop and re-
create the tables and start pushing from scratch, which defeats the
purpose of pushing the mdf/ldf files.
Another suggestion was to set up another box over there with SQL, and
putting the tables there and establishing replication to that machine...
and when China stopped for the day, changing the DNS mappings to point
to the new SQL box instead of the old box. However that suggestion
makes too much sense, and therefore will probably be shot down by
management.
I guess the nutshell version of my question is this: What's the fastest
way to get that large an amount of data over to China, and ready to
replicate without error?
Regards,
ScottI don't envy your job, but here goes a couple of
sugestions.
1) Don't send over your files. Back up the entire
database up, zip up the file then send that file over to
China. In China unzip it then use the restore from file
option.
Advantage - One file, reasonable safe transfer
Disadvantage - Restores are not the most stable of things.
Large file size being transfered. You will lose the extra
fields.
2) Change your replication to Transactional on the server
which is going to perform the upgrade. Ensure that the
location where the transactional files is accessable to
all servers.
Advantage - This will only log the changes to the data so
file size is not too big, it sould be faster to transfer.
Disadvantage - Its replication, and is sometimes not very
easy to do.
3) You can just send over the datafile, rather than both.
There is a command something on the lines of
db_attach_single_file (check it up on bol), again zip it
up and send it.
As you can see each has its advantages and disadvatages.
Personally I would go for 3 if there is time after the
update to send it to china before china comes on read-
write, otherwise 2, replication.
Anyway keep me posted on what happens. My email is
little_flowery_me@.hotmail.com.
J
>--Original Message--
>There's a lot of explanation involved in this, so bear
with the long
>post:
>Our company has offices in the US and China, and an
AS400 (where the
>data originates) in the US, and 3 SQL boxes (which
mirror the AS400
>data, with the odd extra field thrown in) - 2 in the US,
and one in
>China.
>We're changing our item number scheme from a numeric
field to a 25-
>character char field. We're doing this over Memorial
Day weekend...
>we're dropping replication and everything on Friday, and
then the AS400
>starts its conversion process, and sometime late
Saturday or early
>Sunday the conversion process will be done on the AS400,
at which point
>we start copying the data over to our local SQL box, and
then over to
>China.
>China starts its day Sunday night at 7pm. For that day,
they will be in
>read-only mode, so that they won't be writing changes to
their old,
>outdated tables. The downside to being read-only is we
also cannot push
>data to them (since they're using the data) until their
end-of-day at
>approximately 4am Monday. This gives us a very small
window to push the
>data over, re-establish replication, make sure
everything works fine,
>etc. By the way, I'm talking about several gig worth of
data, at least
>12gb and as much as 20gb.
>One of the suggestions was to create a scratch-db
containing all the
>data, and disconnecting that db and transferring the mdf
& ldf files
>over the pond during the middle of our night, so at 5am
they would be
>sitting there ready for us. We could then detach our
existing db, and
>attach the new one in its place. The problem with that
suggestion is
>that once we re-establish replication, how would it know
which data
>already exists in China? Our fear is that replication
will drop and re-
>create the tables and start pushing from scratch, which
defeats the
>purpose of pushing the mdf/ldf files.
>Another suggestion was to set up another box over there
with SQL, and
>putting the tables there and establishing replication to
that machine...
>and when China stopped for the day, changing the DNS
mappings to point
>to the new SQL box instead of the old box. However that
suggestion
>makes too much sense, and therefore will probably be
shot down by
>management.
>I guess the nutshell version of my question is this:
What's the fastest
>way to get that large an amount of data over to China,
and ready to
>replicate without error?
>Regards,
>Scott
>.
>
post:
Our company has offices in the US and China, and an AS400 (where the
data originates) in the US, and 3 SQL boxes (which mirror the AS400
data, with the odd extra field thrown in) - 2 in the US, and one in
China.
We're changing our item number scheme from a numeric field to a 25-
character char field. We're doing this over Memorial Day weekend...
we're dropping replication and everything on Friday, and then the AS400
starts its conversion process, and sometime late Saturday or early
Sunday the conversion process will be done on the AS400, at which point
we start copying the data over to our local SQL box, and then over to
China.
China starts its day Sunday night at 7pm. For that day, they will be in
read-only mode, so that they won't be writing changes to their old,
outdated tables. The downside to being read-only is we also cannot push
data to them (since they're using the data) until their end-of-day at
approximately 4am Monday. This gives us a very small window to push the
data over, re-establish replication, make sure everything works fine,
etc. By the way, I'm talking about several gig worth of data, at least
12gb and as much as 20gb.
One of the suggestions was to create a scratch-db containing all the
data, and disconnecting that db and transferring the mdf & ldf files
over the pond during the middle of our night, so at 5am they would be
sitting there ready for us. We could then detach our existing db, and
attach the new one in its place. The problem with that suggestion is
that once we re-establish replication, how would it know which data
already exists in China? Our fear is that replication will drop and re-
create the tables and start pushing from scratch, which defeats the
purpose of pushing the mdf/ldf files.
Another suggestion was to set up another box over there with SQL, and
putting the tables there and establishing replication to that machine...
and when China stopped for the day, changing the DNS mappings to point
to the new SQL box instead of the old box. However that suggestion
makes too much sense, and therefore will probably be shot down by
management.
I guess the nutshell version of my question is this: What's the fastest
way to get that large an amount of data over to China, and ready to
replicate without error?
Regards,
ScottI don't envy your job, but here goes a couple of
sugestions.
1) Don't send over your files. Back up the entire
database up, zip up the file then send that file over to
China. In China unzip it then use the restore from file
option.
Advantage - One file, reasonable safe transfer
Disadvantage - Restores are not the most stable of things.
Large file size being transfered. You will lose the extra
fields.
2) Change your replication to Transactional on the server
which is going to perform the upgrade. Ensure that the
location where the transactional files is accessable to
all servers.
Advantage - This will only log the changes to the data so
file size is not too big, it sould be faster to transfer.
Disadvantage - Its replication, and is sometimes not very
easy to do.
3) You can just send over the datafile, rather than both.
There is a command something on the lines of
db_attach_single_file (check it up on bol), again zip it
up and send it.
As you can see each has its advantages and disadvatages.
Personally I would go for 3 if there is time after the
update to send it to china before china comes on read-
write, otherwise 2, replication.
Anyway keep me posted on what happens. My email is
little_flowery_me@.hotmail.com.
J
>--Original Message--
>There's a lot of explanation involved in this, so bear
with the long
>post:
>Our company has offices in the US and China, and an
AS400 (where the
>data originates) in the US, and 3 SQL boxes (which
mirror the AS400
>data, with the odd extra field thrown in) - 2 in the US,
and one in
>China.
>We're changing our item number scheme from a numeric
field to a 25-
>character char field. We're doing this over Memorial
Day weekend...
>we're dropping replication and everything on Friday, and
then the AS400
>starts its conversion process, and sometime late
Saturday or early
>Sunday the conversion process will be done on the AS400,
at which point
>we start copying the data over to our local SQL box, and
then over to
>China.
>China starts its day Sunday night at 7pm. For that day,
they will be in
>read-only mode, so that they won't be writing changes to
their old,
>outdated tables. The downside to being read-only is we
also cannot push
>data to them (since they're using the data) until their
end-of-day at
>approximately 4am Monday. This gives us a very small
window to push the
>data over, re-establish replication, make sure
everything works fine,
>etc. By the way, I'm talking about several gig worth of
data, at least
>12gb and as much as 20gb.
>One of the suggestions was to create a scratch-db
containing all the
>data, and disconnecting that db and transferring the mdf
& ldf files
>over the pond during the middle of our night, so at 5am
they would be
>sitting there ready for us. We could then detach our
existing db, and
>attach the new one in its place. The problem with that
suggestion is
>that once we re-establish replication, how would it know
which data
>already exists in China? Our fear is that replication
will drop and re-
>create the tables and start pushing from scratch, which
defeats the
>purpose of pushing the mdf/ldf files.
>Another suggestion was to set up another box over there
with SQL, and
>putting the tables there and establishing replication to
that machine...
>and when China stopped for the day, changing the DNS
mappings to point
>to the new SQL box instead of the old box. However that
suggestion
>makes too much sense, and therefore will probably be
shot down by
>management.
>I guess the nutshell version of my question is this:
What's the fastest
>way to get that large an amount of data over to China,
and ready to
>replicate without error?
>Regards,
>Scott
>.
>
Subscribe to:
Posts (Atom)